How can I speed up my Office Scripts code so that it doesn't fail in Power Automate?

Question

I have been asked to pull data from an Excel online spreadsheet on Sharepoint into Power BI to create a dashboard - no problem, right? Well, one of the 'data points' is actually fill colour of the cell indicating status. I did some googling and managed to write an Office Script that translates the fill colour to the status and got everything to work but I was still running the Office Script manually (and it was taking 3 hours to run).

So I turned to Power Automate and scheduled it to run the script, but it fails every time and I think it's to do with how long the Office Script takes to run because when I reduce the range that it's running over (significantly) it works. Any help to solve this will be greatly appreciated. Here's my code:

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

Here are a few things you can consider:

• Avoid Excel API calls as much as possible inside loops.
• Try to capture API results into variables if they can be reused.
• Instead of iterating through cells and calling cell.getValue(), try retrieving all the values of the whole range into a two-dimensional array (i.e, let values = range.getValues()) and iterating through that array.
• Same thing when setting cell values - use range.setValues(values) instead of calling cell.setValue() repetitively inside a loop.
• Instead of tackling with all the cells in the whole worksheet, try things like worksheet.getUsedRange() to work on only the cells that have values.
• This articles talks about some good performance tips: https://learn.microsoft.com/en-us/office/dev/scripts/develop/web-client-performance.

Below is my attempt to optimize some of your code. I'm sure there are still room for improvement (e.g., switch to getUsedRange(), etc.), but hopefully you can get the idea of it:

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);
}