优化DTR文件合并脚本执行时长，解决超时问题

原代码性能瓶颈分析 #

你的脚本超时核心原因是频繁调用Spreadsheet远程API，以及低效的数据查找逻辑：

• 逐行调用setValues/appendRow，每次都是跨网络请求，累积耗时极高
• 用Set存储主键后，每次更新都要通过findIndex遍历整个现有数据数组，时间复杂度O(n)
• 获取源数据最后一行的方式冗余，多次调用getRange和数组过滤

优化后的代码 #

function mergeDataFromSheets() {
  const folderId = "16jQGy_NZJWazugBTfkv48s-egT48RsRI";
  const masterSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const combinedSheet = masterSpreadsheet.getSheetByName("Combined_Data");
  const targetSheetName = masterSpreadsheet.getSheetByName("Month").getRange("Q3").getValue();
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFilesByType("application/vnd.google-apps.spreadsheet");

  // 一次性读取现有数据，并用对象映射主键到行索引（内存操作，极快）
  const existingData = combinedSheet.getDataRange().getValues();
  const keyToRowIndex = {};
  existingData.forEach((row, index) => {
    if (row[0]) keyToRowIndex[row[0]] = index; // 主键非空才存储
  });

  // 批量收集需要更新和新增的数据
  const updatedRows = [...existingData]; // 复制现有数据，后续直接修改数组
  const newRows = [];

  while (files.hasNext()) {
    const file = files.next();
    const sourceSpreadsheet = SpreadsheetApp.openById(file.getId());
    const sourceSheet = sourceSpreadsheet.getSheetByName(targetSheetName);

    if (!sourceSheet) continue;

    // 高效获取源数据范围：直接用getLastRow()替代冗余的列遍历
    const lastRow = sourceSheet.getLastRow();
    if (lastRow < 3) continue; // 没有有效数据，跳过
    const dataValues = sourceSheet.getRange("A3:J" + lastRow).getValues();

    // 遍历源数据，仅在内存中处理，不调用API
    dataValues.forEach(row => {
      const key = row[0];
      if (!key) return; // 空主键跳过

      if (keyToRowIndex.hasOwnProperty(key)) {
        // 更新内存中的行数据
        updatedRows[keyToRowIndex[key]] = row;
      } else {
        // 收集新增行
        newRows.push(row);
        // 记录新增行的索引（后续如果有重复源数据可以处理）
        keyToRowIndex[key] = updatedRows.length;
      }
    });
  }

  // 一次性写入所有更新和新增数据，仅2次API调用
  if (updatedRows.length > 0) {
    combinedSheet.getRange(1, 1, updatedRows.length, updatedRows[0].length).setValues(updatedRows);
  }
  if (newRows.length > 0) {
    combinedSheet.getRange(updatedRows.length + 1, 1, newRows.length, newRows[0].length).setValues(newRows);
  }
}

核心优化点说明 #

• 批量内存处理：所有数据修改先在内存数组中完成，最后仅用2次setValues写入，彻底减少远程API调用次数（这是性能提升的关键）
• 主键索引映射：用对象keyToRowIndex存储主键到行索引的映射，查找时间复杂度降为O(1)，避免每次更新都遍历整个数组
• 高效获取源数据：用getLastRow()替代原有的列遍历方式，减少不必要的数组操作和API调用
• 跳过无效数据：增加了空主键、无数据表格的判断，避免无效处理

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