Google Apps Script大数据集传输超时，求高效优化方案

针对30万行×20列的数据传输超时问题，除了固定批次拆分，以下是更高效的优化手段：

1. 动态调整批次大小，自动重试失败批次 #

固定5万行的批次可能在数据结构变化时失效，改用动态批次逻辑：失败时自动缩小批次，成功时可尝试增大，确保在不同场景下稳定运行。

function transferWithDynamicBatches() {
  const sourceSS = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = sourceSS.getSheetByName('Sheet1');
  const targetSS = SpreadsheetApp.openById("mRhWcIKLiBchNX3tYvn062tUMkRtrWgNQWaXaA8");
  const targetSheet = targetSS.getSheetByName('Copy of VTVimport Here');
  
  const startRow = 2;
  const startCol = 2;
  const totalRows = sourceSheet.getLastRow() - startRow + 1;
  const totalCols = sourceSheet.getLastColumn() - startCol + 1;
  
  let batchSize = 50000; // 初始批次
  let currentRow = startRow;
  
  while (currentRow <= sourceSheet.getLastRow()) {
    try {
      const endRow = Math.min(currentRow + batchSize - 1, sourceSheet.getLastRow());
      const data = sourceSheet.getRange(currentRow, startCol, endRow - currentRow + 1, totalCols).getValues();
      
      targetSheet.getRange(currentRow - startRow + 2, startCol, data.length, data[0].length).setValues(data);
      
      currentRow = endRow + 1;
      batchSize = Math.min(batchSize + 10000, 100000); // 成功则增大批次
    } catch (e) {
      batchSize = Math.max(Math.floor(batchSize / 2), 1000); // 失败则减半
      if (batchSize < 1000) throw new Error(`处理行 ${currentRow} 失败：${e.message}`);
    }
  }
}

2. 改用Google Sheets API替代SpreadsheetApp服务 #

SpreadsheetApp是封装层，直接调用Sheets API的批量写入接口速度提升明显，尤其适合超大数据量。

前置步骤： #

• 在脚本编辑器中开启高级Google服务（依次点击「资源」→「高级Google服务」→ 启用「Google Sheets API」）
• 在Google Cloud Console中确保Sheets API已启用（脚本编辑器会自动跳转，按提示操作即可）

示例代码： #

function transferWithSheetsAPI() {
  const sourceSSId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  const targetSSId = "mRhWcIKLiBchNX3tYvn062tUMkRtrWgNQWaXaA8";
  const targetSheetName = 'Copy of VTVimport Here';
  
  const startRow = 2;
  const startCol = 2;
  const totalRows = sourceSheet.getLastRow() - startRow + 1;
  const totalCols = sourceSheet.getLastColumn() - startCol + 1;
  
  // 获取源数据（批量读取）
  const data = sourceSheet.getRange(startRow, startCol, totalRows, totalCols).getValues();
  
  // 构建API批量写入请求
  const request = {
    valueInputOption: 'RAW',
    data: [{
      range: `${targetSheetName}!${SpreadsheetApp.getRange(2, startCol).getA1Notation()}:${SpreadsheetApp.getRange(2 + totalRows -1, startCol + totalCols -1).getA1Notation()}`,
      majorDimension: 'ROWS',
      values: data
    }]
  };
  
  // 调用API写入
  Sheets.Spreadsheets.Values.batchUpdate(request, targetSSId);
}

注：若数据量超过API单请求限制（约10MB），需拆分数据为多个批次调用batchUpdate。

3. 优化旧数据清理逻辑 #

若需覆盖目标表旧数据，避免使用低效的deleteRows或clear()，改用API批量清除或批量覆盖：

用API快速清除目标区域： #

function clearTargetSheet() {
  const targetSS = SpreadsheetApp.openById("mRhWcIKLiBchNX3tYvn062tUMkRtrWgNQWaXaA8");
  const targetSheet = targetSS.getSheetByName('Copy of VTVimport Here');
  const startCol = 2;
  
  const clearRequest = {
    requests: [{
      updateCells: {
        range: {
          sheetId: targetSheet.getSheetId(),
          startRowIndex: 1, // 第二行对应索引1
          startColumnIndex: startCol - 1 // 列索引从0开始
        },
        fields: 'userEnteredValue'
      }
    }]
  };
  
  Sheets.Spreadsheets.batchUpdate(clearRequest, targetSS.getId());
}

4. 时间驱动触发器拆分长任务 #

当数据量超过6分钟执行限制时，将任务拆分为多个小任务，用时间驱动触发器分批执行：

示例代码： #

function transferBatch() {
  const props = PropertiesService.getScriptProperties();
  const currentRow = parseInt(props.getProperty('currentRow')) || 2;
  
  const sourceSS = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = sourceSS.getSheetByName('Sheet1');
  const targetSS = SpreadsheetApp.openById("mRhWcIKLiBchNX3tYvn062tUMkRtrWgNQWaXaA8");
  const targetSheet = targetSS.getSheetByName('Copy of VTVimport Here');
  
  const startCol = 2;
  const totalCols = sourceSheet.getLastColumn() - startCol + 1;
  const batchSize = 50000;
  const endRow = Math.min(currentRow + batchSize - 1, sourceSheet.getLastRow());
  
  // 任务完成判断
  if (currentRow > sourceSheet.getLastRow()) {
    props.deleteProperty('currentRow');
    SpreadsheetApp.getUi().alert('数据传输完成');
    return;
  }
  
  // 读取并写入当前批次
  const data = sourceSheet.getRange(currentRow, startCol, endRow - currentRow + 1, totalCols).getValues();
  targetSheet.getRange(currentRow - 2 + 2, startCol, data.length, data[0].length).setValues(data);
  
  // 更新当前进度
  props.setProperty('currentRow', endRow + 1);
  
  // 触发下一次执行（避免并发，延迟1秒）
  ScriptApp.newTrigger('transferBatch')
    .timeBased()
    .after(1000)
    .create();
}

使用方式： #

1. 手动运行一次transferBatch初始化任务
2. 或设置时间驱动触发器（每5分钟运行一次），自动处理剩余批次

5. 基础性能优化细节 #

• 启用V8运行时：脚本编辑器默认已启用，若未开启，依次点击「运行」→「启用新的Apps Script运行时」
• 减少重复调用：将getLastRow()、getLastColumn()结果存入变量，避免重复执行
• 关闭屏幕更新：在脚本开头添加SpreadsheetApp.enableScreenUpdates(false)，结尾添加SpreadsheetApp.enableScreenUpdates(true)，减少UI渲染耗时

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