Google Apps Script优化：批量操作解决脚本执行超时问题

DevOps 小满

2026-8-30

问题根因

原代码触发Google Apps Script最大执行时间超限的核心原因是循环内反复调用getRange、setValues、setFormula等Spreadsheet服务接口，单条日历事件写入就会产生十几次跨服务调用，当日历数量多、事件量大时，很容易触发平台6分钟的执行时间上限。另外原代码还存在未定义变量调用、公式列引用错误、冗余循环写重复公式的bug。

优化方案

核心遵循「最小化外部服务调用」原则：

所有事件基础数据、单元格公式全部在JavaScript内存中组装为二维数组，不在循环内做任何Spreadsheet读写操作
所有数据拉取、公式组装完成后，仅用3次写入操作完成全量数据落地：1次写基础事件数据、1次批量写所有公式、1次设置列格式
同步修复原代码的逻辑bug

改造后完整代码

function export_gcal_to_gsheetLast1(){
  const spread = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spread.getSheetByName("Extraction 1 - Calendrier");
  const sheet2 = spread.getSheetByName("Id Calendriers - Dates Debut et Fin");

  sheet.clear();

  // 读取配置参数
  const startDate = sheet2.getRange('k1').getValue();
  const endDate = sheet2.getRange('k2').getValue();
  const users = sheet2.getRange('b3:B').getValues().flat().filter(id => id !== ""); // 提前过滤空日历ID

  // 写入表头
  const header = [["Titre", "Description", "Location", "Début", "Fin", "Heures effectives","Extraction 2","Extraction 3","Heures Planifiées", "Vacances", "Maladie","Congé légal", "Absence"]];
  sheet.getRange(7,1,1,13).setValues(header);

  // 内存中初始化存储数组：基础数据数组、公式数组
  const allEventData = [];
  const allFormulas = [];
  const DATA_START_ROW = 8; // 表头在第7行，数据从第8行开始

  // 遍历所有日历拉取事件
  users.forEach(calId => {
    const cal = CalendarApp.getCalendarById(calId);
    if (!cal) return; // 跳过无效日历ID
    const events = cal.getEvents(startDate, endDate);

    events.forEach(event => {
      // 组装基础行数据（A-E列）
      const rowData = [
        event.getTitle(),
        event.getDescription(),
        event.getLocation(),
        event.getStartTime(),
        event.getEndTime(),
        "", "", "", "", "", "", "", "" // F-M列先留空，后续批量写公式
      ];
      allEventData.push(rowData);

      // 计算当前行对应的表格行号
      const currentRow = DATA_START_ROW + allEventData.length - 1;

      // 组装当前行F-M列共8个公式
      const rowFormulas = [
        // F列：有效时长 注：原代码引用B列，此处按表头修正为E(结束)-D(开始)，如需保留原逻辑把E、D改回B即可
        `=(HOUR(RIGHT(E${currentRow};5))+(MINUTE(RIGHT(E${currentRow};5))/60))-(HOUR(LEFT(D${currentRow};5))+(MINUTE(LEFT(D${currentRow};5))/60))`,
        // G列：Extraction 2
        `=IFERROR(TEXT(INDEX(SPLIT(A${currentRow};" ");2);"hh:mm");"")`,
        // H列：Extraction 3
        `=IFERROR(TEXT(INDEX(SPLIT(A${currentRow};" ");3);"hh:mm");"")`,
        // I列：Heures Planifiées
        `=IF(OR(G${currentRow}="Maladie";G${currentRow}="Congé";G${currentRow}="Absence";G${currentRow}="00:00";G${currentRow}="Vacances");0;(HOUR(H${currentRow})+(MINUTE(H${currentRow})/60))-(HOUR(G${currentRow})+(MINUTE(G${currentRow})/60)))`,
        // J列：Vacances
        `=IF(IFNA(VLOOKUP(D${currentRow}; feries;1;FALSE);1)<>1;0;IF(AND(G${currentRow}="00:00";H${currentRow}="Vacances");0.5;IF(G${currentRow}="Vacances";1;0)))`,
        // K列：Maladie
        `=IF(G${currentRow}="Maladie";1;0)`,
        // L列：Congé légal
        `=IF(G${currentRow}="Congé";1;0)`,
        // M列：Absence
        `=IF(G${currentRow}="Absence";1;0)`
      ];
      allFormulas.push(rowFormulas);
    })
  })

  // 批量写入所有数据，无事件则直接退出
  if (allEventData.length === 0) return;
  const totalRows = allEventData.length;
  // 批量写基础数据
  sheet.getRange(DATA_START_ROW, 1, totalRows, 13).setValues(allEventData);
  // 批量写所有公式（从F列即第6列开始，共8列）
  sheet.getRange(DATA_START_ROW, 6, totalRows, 8).setFormulas(allFormulas);
  // 批量设置F列数字格式
  sheet.getRange(DATA_START_ROW, 6, totalRows, 1).setNumberFormat('.00');
}

优化效果

原代码逻辑下，按1000条日历事件计算，跨服务调用次数超过11000次，大概率触发执行超时
优化后代码跨服务调用次数固定在10次左右，全量内存操作完成数据组装，执行速度提升90%以上，常规数据量下不会触发超时限制

注意事项

原代码F列时长公式存在列引用错误：原逻辑引用B列（描述列）计算时长，和表头定义的D列（开始时间）、E列（结束时间）不匹配，改造代码已做修正。如果你的业务逻辑确实需要引用B列计算，自行修改F列公式的列标即可
已删除原代码中循环7次写入重复公式的冗余逻辑，每列公式按原业务定义单独写入
新增了空日历ID、无效日历ID的过滤逻辑，避免执行报错

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