如何加速Office Scripts代码避免其在Power Automate中运行超时失败

性能问题核心根源 #

你当前代码运行极慢的核心原因是每一次循环内对单元格的读取、写入操作都是单独和Excel后端发起的网络请求，你遍历的H7:NP320范围一共有接近15万单元格，相当于发起了数十万次请求，耗时自然达到小时级。

具体优化方案 #

优化点1：只复制实际使用的范围，不要复制整表所有行 #

你原来复制1:1048576整表100多万行，绝大多数都是空行，完全没必要，改用getUsedRange()只复制有内容的区域即可。

优化点2：批量读取所有单元格的值和填充色，不要逐单元格读取 #

Office Script支持一次性读取整个范围的所有值、所有填充色，返回二维数组，仅需2次请求即可拿到全部数据，不需要循环内逐单元格调用接口。

优化点3：本地计算完所有结果后，批量写入单元格，不要循环内逐单元格赋值 #

把所有要修改的值先在本地拼成二维数组，最后一次性写入整个范围，仅需1次请求即可完成所有写入操作。

优化后代码 #

function main(workbook: ExcelScript.Workbook) {
  // 关闭自动计算
  workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.manual);

  const selectedSheet = workbook.getWorksheet("ProjectsColourFill");
  const projectsSheet = workbook.getWorksheet("Projects");
  
  // 优化：只复制Projects表的实际使用范围，不复制整表空行
  const projectsUsedRange = projectsSheet.getUsedRange();
  selectedSheet.getRange(projectsUsedRange.getAddress()).copyFrom(
    projectsUsedRange,
    ExcelScript.RangeCopyType.all,
    false,
    false
  );

  const sheet1 = workbook.getWorksheet("Sheet1");
  const headerRange = sheet1.getRange("E1:NP1");
  selectedSheet.getRange("E3").copyFrom(
    headerRange,
    ExcelScript.RangeCopyType.values,
    false,
    false
  );

  selectedSheet.getRange("3:3").setNumberFormatLocal("dd/mm/yyyy;@");

  const targetRange = selectedSheet.getRange("H7:NP320");
  // 批量读取所有值和填充色，仅2次请求
  const rangeValues = targetRange.getValues();
  const rangeFillColors = targetRange.getFormat().getFill().getColors();
  const rowCount = targetRange.getRowCount();
  const colCount = targetRange.getColumnCount();

  // 本地遍历数组计算结果，无任何网络请求
  for (let row = 0; row < rowCount; row++) {
    for (let col = 0; col < colCount; col++) {
      if (rangeValues[row][col] !== "") {
        const color = rangeFillColors[row][col];
        switch (color) {
          case '#DA9694':
            rangeValues[row][col] = "Planned Dates TBC";
            break;
          case '#92D050':
            rangeValues[row][col] = "Booked";
            break;
          case '#E26B0A':
            rangeValues[row][col] = "Prospective";
            break;
          default:
            // 不匹配的颜色保留原值
            break;
        }
      }
    }
  }

  // 批量写入所有结果，仅1次请求
  targetRange.setValues(rangeValues);

  // 恢复自动计算
  workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.automatic);
}

优化效果 #

修改后整体网络请求次数从数十万次降到不足10次，运行时间会从3小时缩短到10秒以内，完全满足Power Automate的运行超时要求。

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