如何在Google Apps Script中实现多字段联合过滤表格数据

问题说明 #

现有代码将所有搜索字段拼接后进行全局匹配，无法实现Category、Size、Brand、Color、Length多字段同时填充后的联合过滤，需修改后端逻辑实现精准的多条件匹配。

修改后的代码 #

Code.gs #

function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate()
  .setTitle('System Search Label')
  .addMetaTag('viewport', 'width=device-width, initial-scale=1')
  .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function processForm(formObject) {
  // 提取各搜索字段值，转为小写并去除首尾空格
  const filters = {
    category: formObject.category?.trim().toLowerCase() || '',
    size: formObject.size?.trim().toLowerCase() || '',
    brand: formObject.brand?.trim().toLowerCase() || '',
    color: formObject.color?.trim().toLowerCase() || '',
    length: formObject.length?.trim().toLowerCase() || ''
  };
  
  // 若所有字段为空，返回空数组
  if (Object.values(filters).every(val => val === '')) {
    return [];
  }
  
  return searchWithFilters(filters);
}

function searchWithFilters(filters) {
  const spreadsheetId = '1j-r4gD1zV3e0yrNJTnHezXRlv1w-aOf6UqQs_P2jsRY';
  const dataRange = 'Master Barcode!A2:O'; // 扩展范围到所有行，避免固定行数限制
  const data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRange).values || [];
  const results = [];

  data.forEach(row => {
    // 对应表格列索引：Brand(1), Category(6), Size(9), Color(12), Length(10)
    const rowData = {
      brand: row[1]?.toLowerCase() || '',
      category: row[6]?.toLowerCase() || '',
      size: row[9]?.toLowerCase() || '',
      color: row[12]?.toLowerCase() || '',
      length: row[10]?.toLowerCase() || ''
    };

    // 检查所有非空过滤条件是否匹配当前行
    let isMatch = true;
    for (const [key, filterVal] of Object.entries(filters)) {
      if (filterVal && !rowData[key].includes(filterVal)) {
        isMatch = false;
        break;
      }
    }

    if (isMatch) {
      results.push(row);
    }
  });

  return results;
}

Index.html #

<!DOCTYPE html>
<html>
    <head>
        <base target="_top">
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous">
    </head>
    <body>
        <div class="container">
            <br>
            <div class="row">
              <div class="col">
                  <center><form id="search-form" onsubmit="handleFormSubmit(this)">
                    <div class="form-group mb-2">
                      <label for="category">Search a Label</label>
                    </div><p>
                    <div class="col-md-6 mb-2">
                      <input type="text" class="form-control" id="category" name="category" placeholder="Category">
                      <br>
                      <input type="text" class="form-control" id="size" name="size" placeholder="Size">
                      <br>
                      <input type="text" class="form-control" id="brand" name="brand" placeholder="Brand">
                      <br>
                      <input type="text" class="form-control" id="color" name="color" placeholder="Color">
                      <br>
                      <input type="text" class="form-control" id="length" name="length" placeholder="Length">
                      <br>
                    </div><p>
                    <button id="search" type="submit" class="btn btn-success mb-2">Search</button>
                    <span id="spinner" class="spinner-border spinner-border-sm d-none" role="status" aria-hidden="true"></span>
                  </form>
                  </center>
              </div>    
            </div>
            <div class="row">
              <div class="col">
                <div id="search-results" class="table-responsive">
                </div>
              </div>
            </div>
        </div>
 
       <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.5.4/dist/umd/popper.min.js" integrity="sha384-q2kxQ16AaE6UbzuKqyBE9/u/KzioAlnx2maXQHiDX9d4/zp8Ok3f+M7DPm+Ib6IU" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/js/bootstrap.min.js" integrity="sha384-pQQkAEnwaBkjpqZ8RU1fF1AKtTcHJwFl3pblpTlHXybJjHpMYo79HY3hIi4NKxyj" crossorigin="anonymous"></script>

        <script>
          function preventFormSubmit() {
            var forms = document.querySelectorAll('form');
            for (var i = 0; i < forms.length; i++) {
              forms[i].addEventListener('submit', function(event) {
              event.preventDefault();
              });
            }
          }
          window.addEventListener("load", preventFormSubmit, true); 
              
          function handleFormSubmit(formObject) {
            document.getElementById('search').innerHTML = "Loading";
            document.getElementById('spinner').classList.remove("d-none");
            google.script.run.withSuccessHandler(createTable).processForm(formObject);
            // 保留用户输入的搜索条件，提升体验
            // document.getElementById("search-form").reset();
          }
        
          function createTable(dataArray) {
            document.getElementById('search').innerHTML = "Search";
            document.getElementById('spinner').classList.add("d-none");
            if(dataArray && dataArray.length != 0){
              var result = "<table class='table table-sm table-striped' id='dtable' style='font-size:0.8em'>"+
                           "<thead style='white-space: nowrap'>"+
                             "<tr>"+ 
                              "<th scope='col'>Barcode</th>"+
                              "<th scope='col'>Brand</th>"+
                              "<th scope='col'>Standard</th>"+
                              "<th scope='col'>Year</th>"+
                              "<th scope='col'>Table</th>"+
                              "<th scope='col'>Type</th>"+
                              "<th scope='col'>Category</th>"+
                              "<th scope='col'>Coil</th>"+
                              "<th scope='col'>X</th>"+
                              "<th scope='col'>Size</th>"+
                              "<th scope='col'>Length</th>"+
                              "<th scope='col'>Weight</th>"+
                              "<th scope='col'>Color</th>"+
                              "<th scope='col'>Voltage</th>"+
                              "<th scope='col'>Size label</th>"+
                            "</tr>"+
                          "</thead>";
              for(var i=0; i<dataArray.length; i++) {
                  result += "<tr>";
                  for(var j=0; j<dataArray[i].length; j++){
                      result += "<td>"+(dataArray[i][j] || '')+"</td>";
                  }
                  result += "</tr>";
              }
              result += "</table>";
              var div = document.getElementById('search-results');
              div.innerHTML = result;
            }else{
              var div = document.getElementById('search-results');
              div.innerHTML = "Data not found!";
            }
          }
        </script>
    </body>
</html>

核心修改点 #

1. 后端逻辑重构：
   • 不再拼接所有搜索字段，单独处理每个过滤条件
   • 针对每行数据，逐一验证非空搜索字段是否匹配对应列（列索引与表格结构严格对应）
   • 扩展数据范围到整列，避免固定行数限制
2. 前端优化：
   • 将输入框命名改为语义化名称，便于维护
   • 保留用户输入的搜索条件，避免重置后需重新输入
   • 修复拼写错误（Lenght → Length）
3. 匹配规则：采用不区分大小写的包含匹配，用户输入部分关键词也能匹配结果，若需精确匹配可将includes改为===

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