求助：调整Google Apps Script以导入5万行大型谷歌表格数据

问题背景 #

需要从另一张谷歌表格导入25列、约5万行的动态数据，使用过ImportRange和两款脚本均因数据量过大触发超时错误：

首次尝试的脚本及错误 #

function importLargeData() {
  // Replace these with the actual values
  const sourceSheetUrl = "https://docs.google.com/spreadsheets/d/ZNBKBNFCBCAISDYAJTIJYLSUO/edit";
  const sourceRange = "INDEX!A1:Y50000";
  const destinationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("INDEX_COPIED");

  // Get the data from the source sheet
  const values = SpreadsheetApp.openByUrl(sourceSheetUrl).getRange(sourceRange).getValues();

  // Process the data if needed (optional)

  // Write the data to the destination sheet
  destinationSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

执行日志：

1:48:15 PM Notice Execution started
1:54:15 PM Error  Exceeded maximum execution time

第二次尝试的脚本及错误 #

/* Global configuration */
const CONFIG = {
  URL: {
    /* Enter the source sheet url between '' */
    SOUCE_SHEET_URL: 'https://docs.google.com/spreadsheets/d/ZNBKBNFCBCAISDYAJTIJYLSUO/edit',
  },
  SHEET_TO_COPY: {
    /* Enter the source sheet name between '' */
    SHEET_NAME: 'INDEX',
  },
  SPREADSHEET: {
    ACTIVE_SPREADSHEET: SpreadsheetApp.getActiveSpreadsheet(),
  },
  TOAST: {
    T1: 'Sheet found, deleting the current version.',
    T2: 'Sheet not found, copying the new sheet.',
    T3: 'Sheet copied successfully.',
    T4: 'Enter the correct url and sheet name.',
  }
};

const importSheet = () => {
  try {
    const sourceSheet = SpreadsheetApp.openByUrl(CONFIG.URL.SOUCE_SHEET_URL).getSheetByName(CONFIG.SHEET_TO_COPY.SHEET_NAME);
    /* Before copying the sheet, delete the exiting copy (if any) */
    const existingSheet = CONFIG.SPREADSHEET.ACTIVE_SPREADSHEET.getSheetByName(CONFIG.SHEET_TO_COPY.SHEET_NAME);
    if (existingSheet) {
      SpreadsheetApp.getActiveSpreadsheet().toast(CONFIG.TOAST.T1, 'Status', 3);
      Utilities.sleep(2000);
      CONFIG.SPREADSHEET.ACTIVE_SPREADSHEET.deleteSheet(existingSheet);
    } else {
      SpreadsheetApp.getActiveSpreadsheet().toast(CONFIG.TOAST.T2, 'Status', 3);
      Utilities.sleep(2000);
    }
    SpreadsheetApp.flush();
    const destinationSheet = sourceSheet.copyTo(CONFIG.SPREADSHEET.ACTIVE_SPREADSHEET);
    destinationSheet.setName(CONFIG.SHEET_TO_COPY.SHEET_NAME);
    CONFIG.SPREADSHEET.ACTIVE_SPREADSHEET.setActiveSheet(destinationSheet);
    SpreadsheetApp.getActiveSpreadsheet().toast(CONFIG.TOAST.T3, 'Success', 3);
  }
  catch (err) {
    SpreadsheetApp.getActiveSpreadsheet().toast(CONFIG.TOAST.T4, 'Failed', 3);
  }
};

执行日志：

1:58:38 PM Notice Execution started
2:04:38 PM Error  Exceeded maximum execution time

解决方案 #

核心思路是分批处理数据，避免一次性读取/写入超大范围导致超时。以下是优化后的脚本，适合新手直接使用：

分批导入数据脚本 #

function importLargeDataInBatches() {
  // 配置参数，根据实际情况修改
  const config = {
    sourceSheetUrl: "https://docs.google.com/spreadsheets/d/ZNBKBNFCBCAISDYAJTIJYLSUO/edit",
    sourceSheetName: "INDEX",
    destSheetName: "INDEX_COPIED",
    batchSize: 2000 // 每批处理的行数，可根据实际情况调整，建议1000-3000之间
  };

  try {
    // 获取源表格和目标表格对象
    const sourceSpreadsheet = SpreadsheetApp.openByUrl(config.sourceSheetUrl);
    const sourceSheet = sourceSpreadsheet.getSheetByName(config.sourceSheetName);
    const destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(config.destSheetName);
    
    if (!sourceSheet || !destSheet) {
      throw new Error("源表格或目标表格不存在，请检查名称是否正确");
    }

    // 获取源数据的实际行数和列数（避免读取空行）
    const lastRow = sourceSheet.getLastRow();
    const lastCol = sourceSheet.getLastColumn();
    if (lastRow === 0) {
      throw new Error("源表格中无数据");
    }

    // 清空目标表格现有数据
    destSheet.clearContents();

    // 分批读取并写入数据
    for (let startRow = 1; startRow <= lastRow; startRow += config.batchSize) {
      const endRow = Math.min(startRow + config.batchSize - 1, lastRow);
      // 读取当前批次的数据
      const batchValues = sourceSheet.getRange(startRow, 1, endRow - startRow + 1, lastCol).getValues();
      // 写入目标表格
      destSheet.getRange(startRow, 1, batchValues.length, batchValues[0].length).setValues(batchValues);
      // 强制刷新，避免缓存问题
      SpreadsheetApp.flush();
    }

    SpreadsheetApp.getActiveSpreadsheet().toast("数据导入完成！", "成功", 5);
  } catch (error) {
    SpreadsheetApp.getActiveSpreadsheet().toast(`导入失败：${error.message}`, "错误", 10);
    console.error(error);
  }
}

关键优化点说明 #

• 分批处理：将5万行拆分成多个小批次（比如每批2000行），减少单次操作的内存占用和执行时间
• 动态获取数据范围：使用getLastRow()和getLastColumn()获取实际有数据的范围，避免读取大量空行
• 清空目标表格：确保导入前目标表格无旧数据，避免数据重叠
• 错误处理：增加明确的错误提示，方便排查问题

额外建议 #

• 调整批次大小：如果仍超时，可适当减小batchSize（比如改成1000）；如果执行速度快，可增大到3000
• 使用触发器：如果数据需要定期更新，可设置时间驱动触发器（比如每天凌晨执行），避免手动运行超时
• 权限检查：确保当前脚本有权限访问源表格（首次运行会提示授权，需确认）

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