How do I remove the numbering on the top of each row when I filter the products? The code is also not displaying some of the products when I select the category and sub-category. Can you help? Here is the code
function getCategories() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Product");
var categories = [];
var data = sheet.getDataRange().getValues();
for (var i = 1; i < data.length; i++) {
var category = data[i][0];
if (categories.indexOf(category) == -1) {
categories.push(category);
}
}
return categories;
}
function getSubCategories(category) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Product");
var subCategories = [];
var data = sheet.getDataRange().getValues();
for (var i = 1; i < data.length; i++) {
var rowCategory = data[i][0];
var subCategory = data[i][1];
if (rowCategory == category && subCategories.indexOf(subCategory) == -1) {
subCategories.push(subCategory);
}
}
return subCategories;
}
function filterProducts(category, subCategory) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Product");
var data = sheet.getDataRange().getValues();
var filteredData = data.filter(function(row) {
return row[0] == category && row[1] == subCategory;
});
return filteredData;
}
function doGet() {
var template = HtmlService.createTemplateFromFile('index');
template.categories = getCategories();
return template.evaluate().setTitle('Product Filter');
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
Here is the HTML code. When the web app runs,
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function updateSubCategories() {
var category = document.getElementById('category').value;
google.script.run.withSuccessHandler(function(subCategories) {
var subCategoryDropdown = document.getElementById('sub-category');
subCategoryDropdown.innerHTML = '';
for (var i = 0; i < subCategories.length; i++) {
var option = document.createElement('option');
option.value = subCategories[i];
option.text = subCategories[i];
subCategoryDropdown.appendChild(option);
}
}).getSubCategories(category);
}
function filterProducts() {
var category = document.getElementById('category').value;
var subCategory = document.getElementById('sub-category').value;
google.script.run.withSuccessHandler(function(filteredData) {
var productTable = document.getElementById('product-table');
productTable.innerHTML = '';
var headers = Object.keys(filteredData[0]); // get the keys of the first row as
column headers
var headerRow = document.createElement('tr');
for (var i = 0; i < headers.length; i++) {
var headerCell = document.createElement('th');
headerCell.textContent = headers[i];
headerRow.appendChild(headerCell);
}
productTable.appendChild(headerRow);
for (var i = 0; i < filteredData.length; i++) { // changed starting index to 0
var row = filteredData[i];
var tableRow = document.createElement('tr');
for (var j = 0; j < headers.length; j++) { // loop through headers to get the
values for the row
var tableCell = document.createElement('td');
tableCell.textContent = row[headers[j]];
tableRow.appendChild(tableCell);
}
productTable.appendChild(tableRow);
}
}).filterProducts(category, subCategory);
}
</script>
</head>
<body>
<h1>Product Filter</h1>
<label for="category">Category:</label>
<select id="category" onchange="updateSubCategories()">
<? for (var i = 0; i < categories.length; i++) { ?>
<option value="<?= categories[i] ?>"><?= categories[i] ?></option>
<? } ?>
</select>
<label for="sub-category">Sub-Category:</label>
<select id="sub-category" onchange="filterProducts()">
</select>
<table id="product-table">
</table>
</body>
</html>
The pictures below will show you what is happening.
This is the Google sheets.