Google Sheets批量迁移12.5万行A:Z数据遇超时/响应过大问题求助

原方案问题分析 #

• Sheets API方案：一次性读取12.5万行完整数据，超出HTTP请求响应的大小限制，触发413 Response too large错误。
• App Script原生方案：batchSize=10000过大，且每次循环都发起一次数据源读取请求，多次服务调用累积导致执行超时。

优化方案1：Sheets API分批读写方案 #

通过分批读取+分批写入拆分数据请求，避免单次请求数据量超限。建议每批次处理5000行（可根据实际情况调整）：

function copyDataInBatchesWithSheetsAPI() {
  const sourceSpreadsheetId = 'Source Sheet ID';
  const sourceSheetName = 'Sheet1';
  const targetSheetName = 'Data';
  const startRowSource = 2; // 源数据从第2行开始
  const startRowTarget = 4; // 目标表从第4行开始
  const batchSize = 5000; // 每批次处理行数，可调整

  // 获取目标表并清除原有数据
  const targetSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = targetSpreadsheet.getSheetByName(targetSheetName);
  targetSheet.getRange(startRowTarget, 1, targetSheet.getLastRow() - startRowTarget + 1, 26).clearContent();

  // 获取源表总数据行数
  const sourceMeta = Sheets.Spreadsheets.get(sourceSpreadsheetId, { ranges: [`${sourceSheetName}!A:A`], fields: 'sheets/data/rowMetadata' });
  const totalRows = sourceMeta.sheets[0].data[0].rowMetadata.length - startRowSource + 1;

  let currentRow = startRowSource;
  let targetCurrentRow = startRowTarget;

  while (currentRow <= startRowSource + totalRows - 1) {
    const endRow = Math.min(currentRow + batchSize - 1, startRowSource + totalRows - 1);
    const range = `${sourceSheetName}!A${currentRow}:Z${endRow}`;
    
    // 分批读取源数据
    const response = Sheets.Spreadsheets.Values.get(sourceSpreadsheetId, range);
    const data = response.values || [];
    if (data.length === 0) break;

    // 分批写入目标表
    const targetRange = `${targetSheetName}!A${targetCurrentRow}:Z${targetCurrentRow + data.length - 1}`;
    Sheets.Spreadsheets.Values.update(
      { values: data },
      targetSpreadsheet.getId(),
      targetRange,
      { valueInputOption: 'RAW' }
    );

    console.log(`已完成批次：行 ${currentRow}-${endRow}，写入目标行 ${targetCurrentRow}-${targetCurrentRow + data.length - 1}`);
    currentRow += batchSize;
    targetCurrentRow += data.length;
  }

  console.log('数据复制完成');
}

优化方案2：App Script原生高效分批方案 #

通过一次性读取全部源数据减少服务调用次数，同时缩小批次大小（建议2000-3000行），降低单次写入的资源开销：

function batchCopyDataOptimized() {
  const sourceSpreadsheetId = 'Source Sheet ID';
  const targetSheetName = 'Data';
  const startRowSource = 2; // 源数据起始行
  const startRowTarget = 4; // 目标表起始行
  const batchSize = 2000; // 每批次写入行数，可调整

  // 获取源表数据（一次性读取全部）
  const sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
  const sourceSheet = sourceSpreadsheet.getSheetByName('Sheet1');
  const lastRowSource = sourceSheet.getLastRow();
  const allSourceData = sourceSheet.getRange(startRowSource, 1, lastRowSource - startRowSource + 1, 26).getValues();
  if (allSourceData.length === 0) {
    console.log('源表无数据');
    return;
  }

  // 获取目标表并清除原有数据
  const targetSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = targetSpreadsheet.getSheetByName(targetSheetName);
  targetSheet.getRange(startRowTarget, 1, targetSheet.getLastRow() - startRowTarget + 1, 26).clearContent();

  // 分批写入目标表
  let currentIndex = 0;
  let targetCurrentRow = startRowTarget;
  const totalDataRows = allSourceData.length;

  while (currentIndex < totalDataRows) {
    const endIndex = Math.min(currentIndex + batchSize - 1, totalDataRows - 1);
    const batchData = allSourceData.slice(currentIndex, endIndex + 1);
    
    targetSheet.getRange(targetCurrentRow, 1, batchData.length, 26).setValues(batchData);
    
    console.log(`已完成批次：行 ${currentIndex+1}-${endIndex+1}，写入目标行 ${targetCurrentRow}-${targetCurrentRow + batchData.length - 1}`);
    currentIndex += batchSize;
    targetCurrentRow += batchData.length;
    
    // 强制刷新，避免内存堆积
    SpreadsheetApp.flush();
  }

  console.log('数据复制完成');
}

注意事项 #

• 执行前需确保已启用Sheets API（在App Script编辑器的「服务」中添加Google Sheets API）。
• 若仍然遇到超时，可进一步缩小batchSize（比如1000行）。
• 避免在数据复制期间操作目标表，防止冲突。

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