如何优化Google Apps Script嵌套for循环实现双表匹配避免运行超时

核心优化逻辑 #

你当前使用的嵌套循环时间复杂度为O(花名册行数 × 报名表行数)，数据量稍大就容易触发Google Apps Script的执行超时限制。最优方案是改用「哈希表预索引」方案，时间复杂度直接降到O(花名册行数 + 报名表行数)，完全可以规避超时问题。
你提到的map函数仅为遍历的语法糖，如果还是嵌套使用不会带来本质效率提升，不需要优先考虑。

优化后完整代码 #

function matchRosterAndRegistration() {
  const SS = SpreadsheetApp.getActiveSpreadsheet();
  const rosterTab = SS.getSheetByName('Roster');
  const registrationTab = SS.getSheetByName('Responses 1');
  const referenceTab = SS.getSheetByName('Reference');
  const resultTab = SS.getSheetByName('Result');
  
  // 读取所有表的有效数据，避免硬编码行数读取空值
  const codeStart = referenceTab.getRange('A1').getValue();
  // 过滤三个关键字段全空的无效行，减少不必要计算
  const rosterData = rosterTab.getRange(1, 1, rosterTab.getLastRow(), 3).getValues().filter(row => row[0] && row[1] && row[2]);
  const registerData = registrationTab.getRange(1, 1, registrationTab.getLastRow(), 3).getValues().filter(row => row[0] && row[1] && row[2]);

  // 第一步：预处理报名表，生成唯一键的哈希索引
  const registerMap = new Map();
  registerData.forEach(row => {
    // 三个字段拼接为唯一键，加特殊分隔符避免不同字段内容撞键
    const uniqueKey = `${row[0]}|${row[1]}|${row[2]}`;
    registerMap.set(uniqueKey, true);
  });

  // 第二步：遍历花名册，直接查索引匹配，无需嵌套循环
  const resultData = [];
  rosterData.forEach(row => {
    const uniqueKey = `${row[0]}|${row[1]}|${row[2]}`;
    if (registerMap.has(uniqueKey)) {
      // 匹配成功直接推入结果数组
      resultData.push([row[0], row[1], row[2], codeStart]);
    }
  });

  // 批量写入结果
  if (resultData.length > 0) {
    resultTab.getRange(resultTab.getLastRow() + 1, 1, resultData.length, resultData[0].length).setValues(resultData);
  }
}

优化效果说明 #

• 索引查找为O(1)常数时间，500行量级的匹配执行时间不会超过1秒，完全不会触发超时
• 用对应表的getLastRow()代替硬编码的300行，避免读取大量无效空行占用内存
• 新增空行过滤逻辑，跳过无效数据的匹配计算，进一步提升执行效率

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