优化Google Apps Script：多Spreadsheet数据合并至主表的性能提升

问题背景 #

我需要将约300个谷歌表格的所有数据合并到一个主表格中，已有的脚本可以实现功能，但运行速度极慢。试过把所有文档ID手动存入变量，速度没有改善，求更优的处理方式。

原脚本代码 #

function combineData() {
  const masterID = "ID";
  const masterSheet = SpreadsheetApp.openById(masterID).getSheets()[0];
  let targetSheets = docIds();
  for (let i = 0, len = targetSheets.length; i < len; i++) {
    let sSheet = SpreadsheetApp.openById(targetSheets[i]).getActiveSheet();
    let sData = sSheet.getDataRange().getValues();
    sData.shift() //Remove header row
     if (sData.length > 0) { //Needed to add to remove errors on Spreadsheets with no data
      let fRow = masterSheet.getRange("A" + (masterSheet.getLastRow())).getRow() + 1;
      let filter = sData.filter(function (row) { 
         return row.some(function (cell) {
           return cell !== "";  //If sheets have blank rows in between doesnt grab
         })
       })

      masterSheet.getRange(fRow, 1, filter.length, filter[0].length).setValues(filter)
     }
  }
}

function docIds() {
    let listOfId = SpreadsheetApp.openById('ID').getSheets()[0]; //list of 300 Spreadsheet IDs
  let values = listOfID.getDataRange().getValues()
  let arrayId = []
  for (let i = 1, len = values.length; i < len; i++) {
    let data = values[i];
    let ssID = data[1];
    arrayId.push(ssID)
  }
  return arrayId
}

性能瓶颈分析 #

1. 频繁网络IO操作：循环中每次调用SpreadsheetApp.openById()、主表getLastRow()和setValues()都是耗时的网络请求，300次循环会累积大量延迟
2. 多次写入主表：每处理一个子表就执行一次写操作，累计300次写请求大幅拖慢速度
3. 冗余计算：通过getRange("A" + masterSheet.getLastRow()).getRow()获取行号属于多余操作，可直接简化

优化后的脚本代码 #

function combineDataOptimized() {
  const masterID = "主表格ID";
  const idListSheetID = "存储ID的表格ID";
  const masterSheet = SpreadsheetApp.openById(masterID).getSheets()[0];
  
  // 1. 一次性获取所有子表ID并过滤空值
  const idListSheet = SpreadsheetApp.openById(idListSheetID).getSheets()[0];
  const idValues = idListSheet.getDataRange().getValues();
  const targetSheetIds = idValues.slice(1).map(row => row[1]).filter(id => id);

  // 2. 批量读取所有子表数据到内存
  let allCombinedData = [];
  targetSheetIds.forEach(sheetId => {
    try {
      const sSheet = SpreadsheetApp.openById(sheetId).getActiveSheet();
      let sData = sSheet.getDataRange().getValues();
      if (sData.length <= 1) return; // 跳过只有表头或空表的情况
      
      sData.shift(); // 移除表头
      // 过滤全空行
      const filteredData = sData.filter(row => row.some(cell => cell !== "" && cell !== null));
      if (filteredData.length > 0) {
        allCombinedData = allCombinedData.concat(filteredData);
      }
    } catch (e) {
      console.error(`处理表格ID ${sheetId} 出错: ${e.message}`);
    }
  });

  // 3. 一次性写入主表
  if (allCombinedData.length > 0) {
    const startRow = masterSheet.getLastRow() + 1;
    masterSheet.getRange(startRow, 1, allCombinedData.length, allCombinedData[0].length).setValues(allCombinedData);
  }
}

核心优化点 #

• 减少主表写操作：将所有子表数据先收集到内存数组，最后仅执行一次setValues()写入，避免300次单独写请求
• 简化ID获取逻辑：用数组方法替代传统循环，代码更简洁高效
• 添加异常处理：捕获单个表格处理的错误，避免一处出错导致整个脚本中断
• 提前过滤无效数据：在读取子表后直接判断是否为空表，减少后续无效计算
• 简化行号计算：直接通过masterSheet.getLastRow() + 1获取起始行，去掉冗余的Range操作

进阶优化建议 #

• 启用高级表格服务：如果速度仍未达标，可以开启谷歌高级表格服务（脚本编辑器→资源→高级谷歌服务→开启Sheets API），它的批量操作效率原生API更高
• 分批次处理：若总数据量极大，可将300个表格分成10-20个批次，每批收集数据后写入一次主表，避免内存溢出
• 缓存ID列表：如果ID列表不常变动，可将ID缓存到脚本属性中，避免每次运行都读取ID表格

内容的提问来源于stack exchange，提问作者Mr. Anderson