我的专用过滤器下拉列表没有过滤我的产品列表中的某些产品。例如，如果我在类别中选择手机并选择三星，则会显示这两个选项。但如果我选择Apple Watch，则不会显示任何内容。我如何确保在类别和子类别中选择它们时，只要它们在Google表单中，就会显示某些内容？我添加了图像。
下面是代码
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 [headers, ...data] = sheet.getDataRange().getValues();
var filteredData = data.filter(function (row) {
return row[0] == category && row[1] == subCategory;
});
return [headers, ...filteredData];
}
function doGet() {
var template = HtmlService.createTemplateFromFile('index');
template.categories = getCategories();
template.category = '';
template.subCategory='';
return template.evaluate().setTitle('Product Filter');
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
function getSheetData() {
var ss= SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Product');
var dataRange = sheet.getDataRange();
var dataValues = dataRange.getValues();
return dataValues;
}
function getUrl() {
var url = ScriptApp.getService().getUrl();
return url;
}这是脚本和HTML代码。
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function getSubCategories(category)
{
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
subCategory.length = 0;
let option = document.createElement("option");
option.value = "";
option.text = "";
subCategory.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item;
option.text = item;
subCategory.appendChild(option);
});
}).getSubCategories(category);
};
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 [headers, ...filteredData] = filteredData;
var productTable = document.getElementById('product-table');
productTable.innerHTML = '';
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++) {
var row = filteredData[i];
var tableRow = document.createElement('tr');
for (var j = 0; j < row.length; j++) {
var tableCell = document.createElement('td');
tableCell.textContent = row[j];
tableRow.appendChild(tableCell);
}
productTable.appendChild(tableRow);
}
}).filterProducts(category, subCategory);
}
</script>
</head>
<body>
<h1>Product Filter</h1>
<?var tableData = getSheetData();?>
<?var url = getUrl();?>
<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><br><br>
<? if(category != '' || subCategory != '')
{
var output_array = [];
tableData.forEach(function(value, index) {
if(index == 0)
{
output_array.push(value);
}
else if(index > 0 && category != '' && subCategory != '')
{
if(value[0] == category && value[2] == subCategory)
{
output_array.push(value);
}
}
else if(index > 0 && category != '')
{
if(value[0] == category)
{
output_array.push(value);
}
}
else if(index > 0 && subCategory != '')
{
if(value[2] == subCategory)
{
output_array.push(value);
}
}
});
}
else
{
var output_array = tableData;
} ?>
<!-- <table id="product-table">
</table> -->
<table id="product-table" border="1" cellpadding="5px" >
<?for(var i = 0; i < tableData.length; i++) { ?>
<?if(i == 0) { ?>
<tr>
<?for(var j = 0; j < tableData[i].length; j++) { ?>
<th><?= tableData[i][j] ?></th>
<? } ?>
</tr>
<? } else { ?>
<tr>
<? for(var j = 0; j < tableData[i].length; j++) { ?>
<td><?= tableData[i][j] ?></td>
<? } ?>
</tr>
<? } ?>
<? } ?>
</table>
</body>
</html>这里是所有产品的列表的图像
通过电话筛选产品并显示2个结果
这是按watch筛选的产品，不显示任何内容
1条答案
按热度按时间vddsk6oq1#
虽然我不确定我是否能正确理解你的预期结果，但将初始值添加到第二个下拉列表中怎么样？当这反映在你的脚本中时，它变成如下所示。
请修改Javascript的
updateSubCategories()如下。修改脚本：
注意：
*修改Web Apps的Google Apps脚本时，请将部署修改为新版本，这样修改后的脚本会反映到Web Apps中，请注意。