如何优化Google Sheets自定义函数getTable的执行性能？

原代码核心性能问题 #

• 重复服务调用：每次调用getTable都会重新读取INPUT和TABLE_CONFIG全表数据，50-100次调用会产生大量耗时的SpreadsheetApp服务请求（这类请求的开销远高于纯JS逻辑）。
• 低效嵌套循环：多处使用双层/三层循环进行匹配查找，时间复杂度达O(n²)甚至更高，数据量增大时性能急剧下降。
• 冗余数据结构：用二维数组存储键值对（如pairsTable），导致后续查找效率低下；通过filter+indexOf获取唯一值的方式效率极低。
• 全局变量污染：values、table等变量未限定作用域，可能引发意外行为，也不利于JS引擎优化。

优化后的完整代码 #

// 缓存工作表数据，避免重复读取
let cachedInputData = null;
let cachedConfigData = null;

// 初始化缓存数据
function initCache() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  cachedInputData = ss.getSheetByName("INPUT").getDataRange().getValues();
  cachedConfigData = ss.getSheetByName("TABLE_CONFIG").getDataRange().getValues();
}

// 过滤INPUT数据，优先使用缓存
function filterInput(group, role) {
  if (!cachedInputData) initCache();
  return cachedInputData.filter(row => row[2] === group && row[8] === role);
}

// 简化矩阵生成逻辑
const generateMatrix = (m, n, value) => Array.from({length: m}, () => Array(n).fill(value));

// 主函数
function getTable(groupUUID, configSheetName, role) {
  const filteredRows = filterInput(groupUUID, role);
  
  if (filteredRows.length === 0) {
    Logger.log("没有匹配的INPUT行");
    return "";
  }
  
  Logger.log(`找到${filteredRows.length}条匹配的行`);
  
  if (!cachedConfigData) initCache();
  
  // --------------------------
  // 构建高效映射，消除嵌套查找
  // --------------------------
  // 1. 唯一Instance ID及其索引映射
  const instanceIds = [...new Set(filteredRows.map(row => row[0]))];
  const instanceIndexMap = new Map(instanceIds.map((id, idx) => [id, idx]));
  const uniqueInstanceCount = instanceIds.length;
  Logger.log(`唯一Instance ID数量：${uniqueInstanceCount}`);
  
  // 2. 配置字段映射：Field ID -> 列号
  const configFieldMap = new Map();
  let configFieldCount = 0;
  cachedConfigData.forEach(configRow => {
    if (configRow[3] === groupUUID && configRow[2] !== "") {
      configFieldMap.set(configRow[0], configRow[2]);
      configFieldCount++;
    }
  });
  Logger.log(`配置字段数量：${configFieldCount}`);
  
  // 3. Instance ID -> File ID映射
  const instanceFileMap = new Map();
  filteredRows.forEach(row => {
    const instanceId = row[0];
    if (!instanceFileMap.has(instanceId)) {
      instanceFileMap.set(instanceId, row[7]);
    }
  });
  
  // --------------------------
  // 生成并填充结果表格
  // --------------------------
  const table = generateMatrix(uniqueInstanceCount, configFieldCount + 1, "");
  
  // 填充Instance ID和File ID列
  instanceIds.forEach((id, idx) => {
    table[idx][0] = id;
    table[idx][configFieldCount] = instanceFileMap.get(id) || "";
  });
  
  // 填充配置字段值
  filteredRows.forEach(row => {
    const rowIdx = instanceIndexMap.get(row[0]);
    const colIdx = configFieldMap.get(row[4]);
    if (rowIdx !== undefined && colIdx !== undefined) {
      table[rowIdx][colIdx] = row[6];
    }
  });
  
  return table;
}

关键优化点说明 #

1. 数据缓存机制：通过全局变量缓存INPUT和TABLE_CONFIG数据，整个脚本执行周期内仅读取两次工作表，彻底消除重复服务调用的开销。
2. Set/Map优化查找：
   • 用new Set获取唯一Instance ID，时间复杂度O(n)，比原代码的filter+indexOf（O(n²)）效率提升数倍。
   • 用Map存储各类映射关系，后续查找操作均为O(1)，完全替代原代码的多层嵌套循环。
3. 简化循环逻辑：原代码中三层嵌套的表格填充逻辑，简化为两次单层遍历，大幅降低时间复杂度。
4. 规范变量作用域：所有变量使用const/let声明，避免全局变量污染，提升代码稳定性和JS引擎优化空间。

额外性能建议 #

• 若调用频率极高（50-100次），建议用自定义菜单触发批量更新：写一个批量计算函数，一次性生成所有RESULTS表的结果并写入单元格，避免每个单元格单独执行getTable。
• 可启用CacheService跨脚本执行周期缓存数据（如设置10分钟缓存），进一步减少工作表读取次数。
• 原代码中configSheetName参数未实际使用，若需动态指定配置表，可调整缓存逻辑为按表名缓存数据。

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