修改Google Apps Script解决多Sheet数据合并执行超时问题

问题背景 #

需要将Drive文件夹（ID：16jQGy_NZJWazugBTfkv48s-egT48RsRI）中多个Google Sheets的数据合并至主表格的「DTR_Merge」工作表，现有脚本功能正常但因数据量过大触发执行超时，无法完成全部数据合并。

原脚本代码 #

function mergeDataFromSheets() {
  // Get the folder ID where the source spreadsheets are located
  const folderId = "16jQGy_NZJWazugBTfkv48s-egT48RsRI";

  // Get the active spreadsheet (the master spreadsheet)
  const masterSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Set the new spreadsheet ID where the "DTR_Merge" sheet is located
  const newSpreadsheet = masterSpreadsheet.getSheetByName("DTR_Merge");
  newSpreadsheet.clearContents();

  // Get the target cell value from the "Teacher Rate" sheet
  const targetCell = masterSpreadsheet.getSheetByName("Teacher Rate").getRange("Q3").getValue();

  // Get the folder containing the source spreadsheets
  const folder = DriveApp.getFolderById(folderId);

  // Get all files in the folder with the specified MIME type (Google Sheets)
  const files = folder.getFilesByType("application/vnd.google-apps.spreadsheet");

  // Iterate through each file
  while (files.hasNext()) {
    const file = files.next();
    const spreadsheet = SpreadsheetApp.openById(file.getId());

    // Get the sheet with the specified name from the source spreadsheet
    const sourceSheet = spreadsheet.getSheetByName(targetCell);

    if (sourceSheet) {
      // Calculate the last row with data in the source sheet
      const lastRow = sourceSheet.getRange("C3:C").getValues().filter(String).length + 2;

      // Get the data range from the source sheet
      const dataRange = sourceSheet.getRange("A3:J" + lastRow);
      const dataValues = dataRange.getValues();

      // Append the data to the "DTR_Merge" sheet in the new spreadsheet
      newSpreadsheet.getRange(newSpreadsheet.getLastRow() + 1, 1, dataValues.length, dataValues[0].length).setValues(dataValues);
    }
  }
}

优化方向与具体修改 #

超时核心原因是频繁调用Spreadsheet API（循环中反复打开文件、读写数据），以及低效的数据范围获取方式，以下是针对性修改：

1. 批量收集数据，一次性写入主表 #

原脚本每次循环都向主表写入数据，会产生大量API调用。改为先将所有源数据收集到内存数组中，最后一次性写入，大幅减少API交互次数。

2. 优化获取最后一行的逻辑 #

原代码通过加载整列数据计算最后一行，效率极低。改用getLastRow()直接获取，同时判断数据起始行（C3）的有效性。

3. 增加异常捕获，避免单个文件出错中断整个流程 #

添加try-catch块，处理单个文件读取失败的情况，确保其他文件能继续处理。

优化后的脚本 #

function mergeDataFromSheets() {
  const folderId = "16jQGy_NZJWazugBTfkv48s-egT48RsRI";
  const masterSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const mergeSheet = masterSpreadsheet.getSheetByName("DTR_Merge");
  const targetSheetName = masterSpreadsheet.getSheetByName("Teacher Rate").getRange("Q3").getValue();
  
  // 清空目标表内容
  mergeSheet.clearContents();
  
  // 初始化批量数据数组，统一存储所有源数据
  const allData = [];
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFilesByType("application/vnd.google-apps.spreadsheet");

  while (files.hasNext()) {
    const file = files.next();
    try {
      const spreadsheet = SpreadsheetApp.openById(file.getId());
      const sourceSheet = spreadsheet.getSheetByName(targetSheetName);
      
      if (!sourceSheet) continue;
      
      // 高效获取最后一行，避免加载整列数据
      const lastRow = sourceSheet.getLastRow();
      if (lastRow < 3) continue; // 无有效数据，跳过当前文件
      
      // 一次性获取A3到J列的所有数据
      const dataValues = sourceSheet.getRange(3, 1, lastRow - 2, 10).getValues();
      
      // 过滤空行，避免写入无效数据
      const filteredData = dataValues.filter(row => row.some(cell => cell !== ""));
      allData.push(...filteredData);
    } catch (e) {
      console.log(`处理文件 ${file.getName()} 时出错: ${e.message}`);
      continue;
    }
  }

  // 批量写入所有数据到主表
  if (allData.length > 0) {
    mergeSheet.getRange(1, 1, allData.length, allData[0].length).setValues(allData);
  }
}

额外优化建议 #

• 如果文件数量极多，可结合PropertiesService记录已处理的文件ID，实现断点续传，避免单次执行时间过长。
• 确保脚本启用V8运行时（脚本编辑器 > 运行 > 启用新Apps Script运行时），提升代码执行速度。
• 移除循环内的冗余日志输出，减少性能消耗。

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