如何修改Google Sheets Web表单代码以支持多工作表搜索

Great to hear the initial setup worked so well for you! Let's adjust your code to support searching across multiple worksheets instead of just one. Here's the modified version with clear explanations of the key changes:

Modified Code #

function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate() 
  .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

/* 处理表单 */
function processForm(formObject){
  var result = "";
  if(formObject.searchtext){// 当表单传入搜索文本时执行
    result = search(formObject.searchtext);
  }
  return result;
}

// 搜索匹配内容（支持多工作表）
function search(searchtext){
  var spreadsheetId = 'spreadsheet id'; //** 请修改此处！！！
  // 定义需要搜索的工作表名称列表，可根据需求添加/删除
  var sheetsToSearch = ['Data', 'Sales', 'Inventory']; // 替换成你的实际工作表名
  var ar = [];

  // 遍历每个要搜索的工作表
  sheetsToSearch.forEach(function(sheetName) {
    var dataRange = sheetName + '!A2:G'; // 保持每个表的搜索范围一致，可按需单独调整
    var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRange);
    
    // 检查工作表是否有数据，避免空表导致代码报错
    if (data.values) {
      data.values.forEach(function(row) {
        if (~row.indexOf(searchtext)) {
          // 可选：如果需要区分数据来自哪个工作表，可取消下面注释，将表名添加到结果行开头
          // row.unshift(sheetName);
          ar.push(row);
        }
      });
    }
  });

  return ar;
}

Key Changes Explained #

• Worksheet List: Added a sheetsToSearch array where you can list all the worksheet names you want to include in the search. This makes it super easy to add or remove sheets later without digging into the core logic.
• Loop Through Sheets: We now iterate over each sheet in the list, fetching data from each one individually instead of just a single sheet.
• Empty Sheet Handling: Added a check for data.values to avoid errors if a worksheet is completely empty (the Sheets API won't return a values property for empty sheets, which would break the original code).
• Optional Sheet Label: There's a commented-out line that lets you prepend the worksheet name to each matching row—uncomment it if you need to tell which sheet a result originated from.

Just update the sheetsToSearch array with your actual worksheet names, and you're ready to search across multiple sheets!

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