如何避免向大型表格写入公式时脚本超时？

你的问题核心在于批量写入VLOOKUP公式后，Spreadsheet需要瞬间计算8万行的跨表查找，触发大量计算操作导致脚本超时。即使改用数组一次性写入公式，本质还是让表格承担了巨大计算量，拖慢了脚本执行。下面是两种高效解决方案：

方案1：使用ARRAYFORMULA替代批量公式写入 #

数组公式仅需在列的起始单元格写入一次，就能自动填充整列，完全避免循环构建公式数组和多次setFormulas操作，大幅减少脚本执行时间和表格计算负载。

修改后的代码：

function insertNewColumns(sheet) {
  Logger.log("Inserting new columns: Commited Projects, Skillsets and Country PPM!");

  var startRow = (SheetType.timeUnit === "YEAR") ? 20 : 21;
  var formulaStartRow = startRow + 1; // 公式起始行

  // 插入列并设置表头
  sheet.insertColumnAfter(2);
  var columnC = 3;
  sheet.getRange(startRow, columnC).setValue("Committed Projects");

  sheet.insertColumnAfter(7);
  var columnH = 8;
  sheet.getRange(startRow, columnH).setValue("Skillset");

  sheet.insertColumnAfter(13);
  var columnN = 14;
  sheet.getRange(startRow, columnN).setValue("Country PPM");

  // 写入数组公式，替代批量VLOOKUP
  sheet.getRange(formulaStartRow, columnC).setFormula(`=ARRAYFORMULA(IFERROR(VLOOKUP(VALUE(A${formulaStartRow}:A), 'Committed Projects'!A:B, 2, 0), "XXXXX"))`);
  sheet.getRange(formulaStartRow, columnH).setFormula(`=ARRAYFORMULA(IFERROR(VLOOKUP(G${formulaStartRow}:G, 'Skillset'!A:B, 2, 0), "XXXXX"))`);
  sheet.getRange(formulaStartRow, columnN).setFormula(`=ARRAYFORMULA(IFERROR(VLOOKUP(M${formulaStartRow}:M, 'Country PPM'!A:B, 2, 0), "XXXXX"))`);
  
  SpreadsheetApp.flush();
}

优势：代码简洁无循环，仅3次单单元格写入操作，表格计算效率远高于批量VLOOKUP。

方案2：脚本预计算结果，直接写入值而非公式 #

如果数据无需实时更新，可让脚本先读取所有lookup表数据，计算出结果后一次性写入值，完全绕过表格公式计算环节，这是性能最优的方案。

修改后的代码：

function insertNewColumns(sheet) {
  Logger.log("Inserting new columns: Commited Projects, Skillsets and Country PPM!");

  var startRow = (SheetType.timeUnit === "YEAR") ? 20 : 21;
  var formulaStartRow = startRow + 1;
  var lastRow = sheet.getRange("A:A").getLastRow();
  var numRows = lastRow - startRow;

  // 插入列并设置表头
  sheet.insertColumnAfter(2);
  var columnC = 3;
  sheet.getRange(startRow, columnC).setValue("Committed Projects");

  sheet.insertColumnAfter(7);
  var columnH = 8;
  sheet.getRange(startRow, columnH).setValue("Skillset");

  sheet.insertColumnAfter(13);
  var columnN = 14;
  sheet.getRange(startRow, columnN).setValue("Country PPM");

  // 读取三个lookup表数据，转换成键值对对象加速查找
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var committedProjectsData = ss.getSheetByName('Committed Projects').getDataRange().getValues();
  var committedMap = new Map();
  committedProjectsData.forEach(row => {
    if (row[0] !== '') committedMap.set(String(row[0]), row[1]);
  });

  var skillsetData = ss.getSheetByName('Skillset').getDataRange().getValues();
  var skillsetMap = new Map();
  skillsetData.forEach(row => {
    if (row[0] !== '') skillsetMap.set(String(row[0]), row[1]);
  });

  var countryPPMData = ss.getSheetByName('Country PPM').getDataRange().getValues();
  var countryPPMMMap = new Map();
  countryPPMData.forEach(row => {
    if (row[0] !== '') countryPPMMMap.set(String(row[0]), row[1]);
  });

  // 读取主表需匹配的列数据（A、G、M列）
  var mainData = sheet.getRange(formulaStartRow, 1, numRows, 13).getValues();

  // 构建结果数组
  var resultsC = [];
  var resultsH = [];
  var resultsN = [];

  mainData.forEach(row => {
    var aValue = String(row[0]);
    resultsC.push([committedMap.get(aValue) || "XXXXX"]);

    var gValue = String(row[6]);
    resultsH.push([skillsetMap.get(gValue) || "XXXXX"]);

    var mValue = String(row[12]);
    resultsN.push([countryPPMMMap.get(mValue) || "XXXXX"]);
  });

  // 一次性写入所有结果
  sheet.getRange(formulaStartRow, columnC, numRows, 1).setValues(resultsC);
  sheet.getRange(formulaStartRow, columnH, numRows, 1).setValues(resultsH);
  sheet.getRange(formulaStartRow, columnN, numRows, 1).setValues(resultsN);

  SpreadsheetApp.flush();
}

优势：脚本完成所有计算，仅写入纯文本/数值，表格无需执行任何公式计算，执行速度最快，完全避免超时问题，适合一次性数据处理场景。

额外优化建议 #

• 减少Range操作：尽量用getDataRange()或一次性读取多列数据，避免多次单独读取单元格。
• 缩小lookup范围：如果lookup表有固定行数，用'Committed Projects'!A1:B1000而非A:B，减少计算范围。
• 分批次处理：若仍超时，可使用PropertiesService分割任务，每1万行处理一次（上述方案基本覆盖8万行需求）。

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