power-automate - 如何加速我的 Office 脚本代码，使其不会在 Power Automate 中失败？

Question

有人要求我将 Sharepoint 上的 Excel 在线电子表格中的数据提取到 Power BI 中以创建仪表板 - 没问题，对吧？好吧，“数据点”之一实际上是指示状态的单元格的填充颜色。我进行了一些谷歌搜索并设法编写了一个 Office 脚本，将填充颜色转换为状态并使一切正常工作，但我仍在手动运行 Office 脚本(运行需要 3 个小时)。

所以我转向 Power Automate 并安排它运行脚本，但每次都失败，我认为这与 Office 脚本运行的时间有关，因为当我减少它运行的范围时(显着)有用。解决此问题的任何帮助将不胜感激。这是我的代码:

function main(workbook: ExcelScript.Workbook)
{
  
  //disable auto calculation mode

  workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.manual);

  let selectedSheet = workbook.getWorksheet("ProjectsColourFill");
  let projects = workbook.getWorksheet("Projects");
  // Paste range at 1:1048576 on selectedSheet from 1:1048576 on projects
  selectedSheet.getRange("1:1048576").copyFrom(projects.getRange("1:1048576"), ExcelScript.RangeCopyType.all, false, false);

  let sheet1 = workbook.getWorksheet("Sheet1");
  // Paste range at E3 on selectedSheet from E1:NP1 on sheet1
  selectedSheet.getRange("E3").copyFrom(sheet1.getRange("E1:NP1"), ExcelScript.RangeCopyType.values, false, false);

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

  let cell = selectedSheet.getRange("H7");
  let format = cell.getFormat().getFill().getColor();

  let range = selectedSheet.getRange("H7:NP320");

  // Get the size boundaries of the range.
  let rows = range.getRowCount();
  let cols = range.getColumnCount();


  // Iterate over the range.
  for (let row = 0; row < rows; row++) {
    for (let col = 0; col < cols; col++) {


      if (range.getCell(row, col).getValue() != "") {
         // Get colour of current cell

           // If colour is one of the three status, fill with status
        if (range.getCell(row, col).getFormat().getFill().getColor() == '#DA9694') {
                range.getCell(row,col).setValue("Planned Dates TBC");
            }
        if (range.getCell(row, col).getFormat().getFill().getColor() == '#92D050') {
              range.getCell(row, col).setValue("Booked");
            }
        if (range.getCell(row, col).getFormat().getFill().getColor() == '#E26B0A') {
              range.getCell(row, col).setValue("Prospective");
            }
  
      }
     // console.log(range.getCell(row,col).getAddress());

    }
  }

  //turn it back on
  workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.automatic);


}

Answer 1

您可以考虑以下几点:

• 尽可能避免在循环内调用 Excel API。
• 如果可以重复使用，请尝试将 API 结果捕获到变量中。
• 不要遍历单元格并调用 cell.getValue()，而是尝试将整个范围的所有值检索到一个二维数组中(即，let values = range.getValues ()) 并遍历该数组。
• 设置单元格值时同样如此 - 使用 range.setValues(values) 而不是在循环内重复调用 cell.setValue()。
• 与其处理整个工作表中的所有单元格，不如尝试使用 worksheet.getUsedRange() 之类的方法来仅处理具有值的单元格。
• 本文讨论了一些良好的性能技巧:https://learn.microsoft.com/en-us/office/dev/scripts/develop/web-client-performance .

下面是我尝试优化您的一些代码。我确信仍有改进的余地(例如，切换到 getUsedRange() 等)，但希望您能理解它:

function main(workbook: ExcelScript.Workbook) {

  //disable auto calculation mode
  workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.manual);

  let selectedSheet = workbook.getWorksheet("ProjectsColourFill");
  let projects = workbook.getWorksheet("Projects");
  // Paste range at 1:1048576 on selectedSheet from 1:1048576 on projects
  selectedSheet.getRange("1:1048576").copyFrom(projects.getRange("1:1048576"), ExcelScript.RangeCopyType.all, false, false);

  let sheet1 = workbook.getWorksheet("Sheet1");
  // Paste range at E3 on selectedSheet from E1:NP1 on sheet1
  selectedSheet.getRange("E3").copyFrom(sheet1.getRange("E1:NP1"), ExcelScript.RangeCopyType.values, false, false);

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

  let cell = selectedSheet.getRange("H7");
  let format = cell.getFormat().getFill().getColor();

  let range = selectedSheet.getRange("H7:NP320");
  let values = range.getValues();

  // Get the size boundaries of the range.
  let rows = range.getRowCount();
  let cols = range.getColumnCount();

  // Iterate over the range.
  for (let row = 0; row < rows; row++) {
    for (let col = 0; col < cols; col++) {

      if (values[row][col] != "") {
        // Get colour of current cell
        let color = range.getCell(row, col).getFormat().getFill().getColor();

        // If colour is one of the three status, fill with status
        if (color == '#DA9694') {
          values[row][col] = "Planned Dates TBC";
        }
        if (color == '#92D050') {
          values[row][col] = "Booked";
        }
        if (color == '#E26B0A') {
          values[row][col] = "Prospective";
        }
      }
    }
  }

  range.setValues(values);

  //turn it back on
  workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.automatic);
}