Google Sheet Web App：移除行首编号及产品筛选异常求助

问题描述 #

• 筛选产品时，每行顶部出现的编号无法移除
• 选择分类（Category）和子分类（Sub-Category）后，部分产品未显示

相关代码 #

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 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();
 }

HTML 代码 #

<!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>

运行效果与数据截图 #

运行效果 #

Google Sheet 数据截图 #

问题解决方案 #

问题1：移除每行顶部的编号 #

问题根源：HTML代码中错误使用Object.keys(filteredData[0])获取表头——filteredData是二维数组，数组的键是索引值（0、1、2...），所以才会显示编号。同时原filterProducts函数返回的数据包含表头行，导致表头被当作数据行渲染。

修复步骤：

1. 修改 Google Apps Script 的 filterProducts 函数：
   跳过表头行过滤数据，返回时带上表头：

   function filterProducts(category, subCategory) {
     var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Product");
     var data = sheet.getDataRange().getValues();
     // 跳过第0行（表头），从第1行开始过滤
     var filteredData = data.slice(1).filter(function(row) {
       return row[0] == category && row[1] == subCategory;
     });
     // 返回时带上表头，方便前端渲染
     return [data[0]].concat(filteredData);
   }
   

2. 修改 HTML 的 filterProducts 函数：
   直接取返回数据的第一行作为表头，后续行作为数据行：

   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 = filteredData[0];
       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);
       // 从第1行开始渲染数据
       for (var i = 1; i < filteredData.length; i++) {
         var row = filteredData[i];
         var tableRow = document.createElement('tr');
         for (var j = 0; j < headers.length; j++) {
           var tableCell = document.createElement('td');
           tableCell.textContent = row[j];
           tableRow.appendChild(tableCell);
         }
         productTable.appendChild(tableRow);
       }
     }).filterProducts(category, subCategory);
   }
   

问题2：部分产品未显示 #

问题根源：原过滤逻辑直接使用==匹配，未处理空格、大小写差异，且未跳过空行，导致部分数据匹配失败。

修复方式：修改filterProducts函数，统一格式并跳过空行：

function filterProducts(category, subCategory) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Product");
  var data = sheet.getDataRange().getValues();
  var filteredData = data.slice(1).filter(function(row) {
    // 跳过空行
    if (!row[0] || !row[1]) return false;
    // 统一转为小写并去除前后空格，避免匹配误差
    var rowCat = row[0].trim().toLowerCase();
    var rowSubCat = row[1].trim().toLowerCase();
    var targetCat = category.trim().toLowerCase();
    var targetSubCat = subCategory.trim().toLowerCase();
    return rowCat === targetCat && rowSubCat === targetSubCat;
  });
  return [data[0]].concat(filteredData);
}

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