Google Sheets产品Dropdown filter异常：部分分类组合无结果显示

我基于Google Sheets开发的产品Dropdown Filter存在筛选异常：当选择分类为Phones、子分类为Samsung时，能正常显示对应产品；但选择分类为Watch、子分类为Apple Watch时，无任何产品显示。需要确保只要Google Sheets中存在对应分类与子分类的产品，筛选后就能正常展示结果。

Google Apps Script代码 #

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> 

截图 #

• 全产品列表：
• Dell电脑筛选结果：
• Watch筛选无结果：

内容的提问来源于stack exchange，提问作者Lamin