如何优化Google Apps Script中大数据量的复制操作？

一、当前耗时是否正常？ #

对于**10000行×115列（约115万个单元格）**的数据量，使用原生getValues()+setValues()耗时2分钟属于合理范围。这类操作的开销主要来自：

• 数据从Google Sheets服务器传输到Apps Script运行环境的内存
• 数据从内存写入目标表格的网络与处理开销
• 目标表格可能触发的自动计算（若存在公式）

二、getValues()/setValues()是否为最优原生方案？ #

是的。你已经遵循了Apps Script的核心最佳实践：通过单次getValues()获取所有数据、单次setValues()写入所有数据，避免了多次调用服务接口的巨大开销。原生Apps Script环境下，这已是效率最高的常规数据读写方式。

三、大幅缩短运行时间的优化措施 #

如果想要进一步提速，推荐使用Google Sheets Advanced Service（批量API操作），它能跳过Apps Script内存中转，直接在Google服务器端完成跨表格数据复制，效率提升非常明显。

1. 启用Sheets Advanced Service #

在Apps Script编辑器中：

• 点击「扩展」→「Apps Script」进入编辑器
• 点击「服务」→「添加服务」，找到「Sheets」并启用

2. 优化后的代码示例 #

function importDataWithSheetsAPI() {
  const sourceSheetId = "source-sheet-id";
  const sourceSheetName = "2014/2021";
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Baza danych");
  
  // 获取源表格的范围参数
  const sourceSheet = SpreadsheetApp.openById(sourceSheetId).getSheetByName(sourceSheetName);
  const rowCount = sourceSheet.getDataRange().getNumRows();
  const colCount = sourceSheet.getDataRange().getNumColumns();
  
  // 使用Sheets API批量复制（服务器端直接操作，无需内存中转）
  Sheets.Spreadsheets.batchUpdate({
    requests: [
      {
        copyPaste: {
          source: {
            sheetId: sourceSheet.getSheetId(),
            startRowIndex: 0,
            endRowIndex: rowCount,
            startColumnIndex: 0,
            endColumnIndex: colCount
          },
          destination: {
            sheetId: targetSheet.getSheetId(),
            startRowIndex: 0,
            startColumnIndex: 0
          },
          pasteType: "PASTE_VALUES" // 仅复制值，若需保留格式改用"PASTE_NORMAL"
        }
      }
    ]
  }, sourceSheetId);
  
  console.log("Zakonczono kopiowanie - Baza");
}

3. 额外优化项（进一步降低耗时与源表负载） #

• 关闭目标表格自动计算：如果目标表格包含大量公式，操作前将计算模式设为手动，完成后恢复自动，避免粘贴时触发大量计算：

  // 操作前设置手动计算
  targetSheet.getParent().setCalculationMode(SpreadsheetApp.CalculationMode.MANUAL);
  // ... 执行复制操作 ...
  // 操作后恢复自动计算
  targetSheet.getParent().setCalculationMode(SpreadsheetApp.CalculationMode.AUTOMATIC);
  

• 缓存源数据：如果源数据并非实时更新，可使用CacheService或PropertiesService缓存已获取的数据，减少对源表格的重复请求，降低负载同时加快后续调用速度：

  function importDataWithCache() {
    const cache = CacheService.getScriptCache();
    const cacheKey = "source_data_cache";
    let sourceData = cache.get(cacheKey);
    
    if (!sourceData) {
      // 从源表格拉取数据并缓存（设置过期时间，比如1小时）
      const ss = SpreadsheetApp.openById("source-sheet-id").getSheetByName("2014/2021");
      sourceData = JSON.stringify(ss.getDataRange().getValues());
      cache.put(cacheKey, sourceData, 3600);
    }
    
    const ts = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Baza danych");
    const parsedData = JSON.parse(sourceData);
    ts.getRange(1, 1, parsedData.length, parsedData[0].length).setValues(parsedData);
    console.log("Zakonczono kopiowanie - Baza");
  }
  

四、总结 #

• 原生getValues()/setValues()方案的2分钟耗时符合数据量的正常水平；
• Sheets API批量复制是当前能大幅缩短运行时间的最优方案，可将耗时压缩至30秒以内；
• 结合手动计算、数据缓存，能进一步降低运行时间与源表格负载，适配频繁触发的需求。

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