Google Apps Script复制超2万行时报错：超出最大执行时间

原脚本执行超时的核心原因是大数据量下SpreadsheetApp的getValues/setValues效率不足，加上脚本存在冗余操作，导致5分钟（Google Apps Script普通账户最大执行时间）内无法完成。以下是两种解决方案：

方案一：优化现有脚本逻辑（无需启用API） #

对原脚本的冗余操作和批量处理逻辑进行调整，提升执行效率：

function copySheetData2LTP() {  
  // 源文件配置
  const sourceFolderId = 'Id_from';  
  const sourceFileName = getCurrentYearAndMonth();
  const sourceSheetName = 'from';  

  // 目标文件配置
  const targetFolderId = 'Id_to';
  const targetFileName = getCurrentYearAndMonth();  
  const targetSheetName = 'to';  

  // 获取源文件ID（优化查找逻辑）
  const sourceFile = DriveApp.getFolderById(sourceFolderId).getFilesByName(sourceFileName).next();
  const sourceSpreadsheet = SpreadsheetApp.openById(sourceFile.getId());
  const sourceSheet = sourceSpreadsheet.getSheetByName(sourceSheetName);
  const numRows = sourceSheet.getLastRow();
  const numColumns = 10; // 仅复制前10列

  // 获取目标文件ID
  const targetFile = DriveApp.getFolderById(targetFolderId).getFilesByName(targetFileName).next();
  const targetSpreadsheet = SpreadsheetApp.openById(targetFile.getId());
  const targetSheet = targetSpreadsheet.getSheetByName(targetSheetName);

  // 高效清空目标表（替代clearSheet）
  targetSheet.clearContents();
  // 如果需要清除格式，用targetSheet.clear()，但clearContents更快

  // 调整批量大小：单次处理1500行（平衡效率与次数）
  const batchSize = 1500;

  for (let startBatchRow = 1; startBatchRow <= numRows; startBatchRow += batchSize) {
    const endBatchRow = Math.min(startBatchRow + batchSize - 1, numRows);
    const rowCount = endBatchRow - startBatchRow + 1;

    // 一次性获取源数据
    const sourceValues = sourceSheet.getRange(startBatchRow, 1, rowCount, numColumns).getValues();
    // 一次性写入目标表
    targetSheet.getRange(startBatchRow, 1, rowCount, numColumns).setValues(sourceValues);

    // 强制刷新，避免内存堆积
    SpreadsheetApp.flush();
  }
}

优化关键点： #

• 移除冗余操作：原脚本重复打开源表格和工作表，直接一次性获取后复用，减少API调用次数。
• 调整批量大小：将batchSize从50000改为1500，单次处理行数过多会导致单次操作超时，小批量分多次处理更稳定。
• 高效清空表格：用clearContents替代自定义clearSheet（如果原clearSheet是逐行删除，效率极低），仅清除内容保留格式，速度更快。
• 强制刷新：每次批量写入后调用SpreadsheetApp.flush()，确保数据及时写入，避免内存占用过高。

方案二：使用Sheets API（超大数据量首选） #

对于2万行以上的大数据量，Sheets API的batchUpdate效率远高于原生SpreadsheetApp方法，能大幅缩短执行时间。

步骤： #

1. 在脚本编辑器中，点击「资源」→「高级Google服务」，启用Google Sheets API。
2. 使用以下脚本：

function copySheetData2LTPWithSheetsAPI() {  
  const sourceFolderId = 'Id_from';  
  const sourceFileName = getCurrentYearAndMonth();
  const sourceSheetName = 'from';  

  const targetFolderId = 'Id_to';
  const targetFileName = getCurrentYearAndMonth();  
  const targetSheetName = 'to';  

  // 获取源文件ID
  const sourceFile = DriveApp.getFolderById(sourceFolderId).getFilesByName(sourceFileName).next();
  const sourceSpreadsheetId = sourceFile.getId();
  // 获取目标文件ID
  const targetFile = DriveApp.getFolderById(targetFolderId).getFilesByName(targetFileName).next();
  const targetSpreadsheetId = targetFile.getId();

  // 获取源表数据范围（前10列）
  const sourceSheet = SpreadsheetApp.openById(sourceSpreadsheetId).getSheetByName(sourceSheetName);
  const sourceRange = `${sourceSheetName}!A1:J${sourceSheet.getLastRow()}`;
  
  // 构建复制请求
  const requests = [
    // 先清空目标表内容
    {
      updateCells: {
        range: {
          sheetId: targetSpreadsheetId ? SpreadsheetApp.openById(targetSpreadsheetId).getSheetByName(targetSheetName).getSheetId() : '',
          startRowIndex: 0,
        },
        fields: 'userEnteredValue'
      }
    },
    // 复制源数据到目标表
    {
      copyPaste: {
        source: {
          spreadsheetId: sourceSpreadsheetId,
          range: sourceRange
        },
        destination: {
          spreadsheetId: targetSpreadsheetId,
          range: `${targetSheetName}!A1`
        },
        pasteType: 'PASTE_VALUES'
      }
    }
  ];

  // 执行批量更新
  Sheets.Spreadsheets.batchUpdate({requests}, targetSpreadsheetId);
}

优势： #

• 直接通过API批量操作，避免多次get/setValues的开销，处理2万行数据仅需几秒到几十秒。
• 一次请求完成清空和复制操作，大幅减少API调用次数。

内容的提问来源于stack exchange，提问作者v.yushkin-NSK