Optimizing Officescript for Power Automate

Question

First post here.

I've been tasked with snapshotting some data in an Excel file and storing it for use in a Power BI report. I'm relatively new to Officescript and the Power Platform, with some background in code. I was learning Officescript as I was making this, so I would expect there's a lot of optimization to do in my code.

I have to collect data on two levels: CAMs (employees) and Customers, then multiple counts for each. I do this by running a for loop for Customers nested in the CAM for loop. The script enters the current search into designated search boxes in helper ranges that calculate the necessary counts. The script then copies the helpers into a larger list, removes zero data rows, and then copies the lists to the historical data list.

To run the snapshot script automatically, I use a Power Automate flow to run a desktop flow that refreshes my data connections in the Excel file, then (back in the cloud flow) I run the Officescript. The problem is that the script keeps timing out. I've tried chunking out the code, but it still seems to be timing out.

Hoping someone can help me identify ways to make this run faster so it doesn't time out.

Code is here:

function main(workbook: ExcelScript.Workbook) {
    // refresh workbook data
    workbook.refreshAllDataConnections;

    // set worksheet variable names 
    let data = workbook.getWorksheet("Detail"); // set Detail worksheet
    let ss = workbook.getWorksheet("Status Snapshot"); // set Status Snapshot worksheet
    let ssh = workbook.getWorksheet("Status Snapshot Helper"); // set Status Snapshot Helper worksheet
    let vs = workbook.getWorksheet("Violation Snapshot");
    let vsh = workbook.getWorksheet("Violation Snapshot Helper");

    // Safeguard against duplicate data
    // clear Status Snapshot
    let todayDate = ssh.getRange("C1").getValue(); // get today's date
    let ssRange = ss.getRange("A:A").getUsedRange().getRowCount(); // set used range in column A
    for (let i = 1; i <= ssRange; i++) { // initiate for loop
        if (ss.getRange("A" + i).getValue() === todayDate) { // check if date is today
            ss.getRangeByIndexes(i - 1, 0, i - 1, 6).clear(ExcelScript.ClearApplyTo.contents); // clear row data
        }
    }
    // clear Violation Snapshot
    let vsRange = vs.getRange("A:A").getUsedRange().getRowCount(); // set used range in column A
    for (let i = 1; i <= vsRange; i++) { // initiate for loop
        if (vs.getRange("A" + i).getValue() === todayDate) { // check if date is today
            vs.getRangeByIndexes(i - 1, 0, i - 1, 6).clear(ExcelScript.ClearApplyTo.contents); // clear row data
        }
    }

    // Clear Snapshot Helper Data
    // clear Status Snapshot Helper
    ssh.getRange("C2:G2").getExtendedRange(ExcelScript.KeyboardDirection.down).clear(ExcelScript.ClearApplyTo.contents);
    // clear Violation Snapshot Helper
    vsh.getRange("C2:H2").getExtendedRange(ExcelScript.KeyboardDirection.down).clear(ExcelScript.ClearApplyTo.contents);

    // Get last row of Unique CAM column
    let rangeCAM = ssh.getRange("A:A").getUsedRange();
    let uniqueCAMRows = rangeCAM.getRowCount();

    // Get last row of Unique Customer column
    let rangeCust = ssh.getRange("B:B").getUsedRange();
    let uniqueCustRows = rangeCust.getRowCount();

    // snapshot loop
    // Status Snapshot loop
    for (var i = 2; i <= uniqueCAMRows; i++) { // initiate for loop
        let searchCAM = ssh.getRange("A" + i); // set the CAM to search for
        let searchCAMdest = ssh.getRange("H2"); // set the CAM search destination
        searchCAMdest.copyFrom(searchCAM, ExcelScript.RangeCopyType.values, false, false); // paste CAM into search destination
        for (var j = 2; j <= uniqueCustRows; j++) {
            let searchCust = ssh.getRange("B" + j);
            let searchCustdest = ssh.getRange("I2");
            searchCustdest.copyFrom(searchCust, ExcelScript.RangeCopyType.values, false, false);
            let dataRange = ssh.getRange("H2:K4"); // set snapshot data range for current CAM
            let sshRange = ssh.getRange("D:D").getUsedRange(); // get used range in snapshot data
            let sshNextEmpty: number = sshRange.getRowCount() + 1; // get row count in snapshot data
            let dataDest = ssh.getRange("D" + sshNextEmpty); // find data paste destination
            dataDest.copyFrom(dataRange, ExcelScript.RangeCopyType.values, false, false); // paste snapshot data into destination
        }
    }
    // Violation Snapshot loop
    for (var i = 2; i <= uniqueCAMRows; i++) { // initiate for loop
        let searchCAM = vsh.getRange("A" + i); // set the CAM to search for
        let searchCAMdest = vsh.getRange("I2"); // set the CAM search destination
        searchCAMdest.copyFrom(searchCAM, ExcelScript.RangeCopyType.values, false, false); // paste CAM into search destination
        for (var j = 2; j <= uniqueCustRows; j++) {
            let searchCust = vsh.getRange("B" + j);
            let searchCustdest = vsh.getRange("J2");
            searchCustdest.copyFrom(searchCust, ExcelScript.RangeCopyType.values, false, false);
            let dataRange = vsh.getRange("I2:M5"); // set snapshot data range for current CAM
            let vshRange = vsh.getRange("D:D").getUsedRange(); // get used range in snapshot data
            let vshNextEmpty: number = vshRange.getRowCount() + 1; // get row count in snapshot data
            let dataDest = vsh.getRange("D" + vshNextEmpty); // find data paste destination
            dataDest.copyFrom(dataRange, ExcelScript.RangeCopyType.values, false, false); // paste snapshot data into destination
        }
    }

    // Delete all 0 rows for Snapshot Helper data
    // Status Snapshot
    let sshRange: number = ssh.getRange("D:D").getUsedRange().getRowCount(); // set used range in column D
    for (let i = 2; i <= sshRange; i++) { // initiate for loop
        let g = ssh.getRange("G" + i).getValue(); // get column G number
        if (g === 0) { // check if all 3 counts = 0
            ssh.getRangeByIndexes(i - 1, 2, 1, 6).delete(ExcelScript.DeleteShiftDirection.up); // delete row if 0
            i--; // subtract 1 from i to compensate for deleted
        }
    }
    // Violation Snapshot
    let vshRange: number = vsh.getRange("D:D").getUsedRange().getRowCount(); // set used range in column D
    for (let i = 2; i <= vshRange; i++) { // initiate for loop
        let g = vsh.getRange("G" + i).getValue(); // get column G number
        let h = vsh.getRange("H" + i).getValue(); // get column H number
        let sum: number = g + h;
        if (sum === 0) { // check if sum counts = 0
            vsh.getRangeByIndexes(i - 1, 2, 1, 6).delete(ExcelScript.DeleteShiftDirection.up); // delete row if 0
            i--; // subtract 1 from i to compensate for deleted
        }
    }
    // paste dates into the snapshot helper data
    // Status Snapshot
    let dateCells = ssh.getRange("C1"); // set date cell
    let sshUsedRows: number = ssh.getRange("D:D").getUsedRange().getRowCount(); // get used rows number in Snapshot Helper data
    let sshUsedRange = ssh.getRange("C2:C" + sshUsedRows); // set the range to fill dates
    sshUsedRange.copyFrom(dateCells, ExcelScript.RangeCopyType.values, false, false); // copy date cell into range needing dates
    // Violation Snapshot
    let dateCellv = vsh.getRange("C1"); // set date cell
    let vshUsedRows: number = vsh.getRange("D:D").getUsedRange().getRowCount(); // get used rows number in Snapshot Helper data
    let vshUsedRange = vsh.getRange("C2:C" + vshUsedRows); // set the range to fill dates
    vshUsedRange.copyFrom(dateCellv, ExcelScript.RangeCopyType.values, false, false); // copy date cell into range needing dates


    // Copy and paste Snapshot Helper Data
    // Status Snapshot
    let sshDataRange = ssh.getRange("C2:G2").getExtendedRange(ExcelScript.KeyboardDirection.down); // get Snapshot Helper data range
    let ssNextRow: number = ss.getRange("A:A").getUsedRange().getRowCount() + 1; // get Snapshot used range
    let ssDataDest = ss.getRange("A" + ssNextRow); // set data paste destination
    ssDataDest.copyFrom(sshDataRange, ExcelScript.RangeCopyType.all, false, false); // copy and paste data into Snapshot
    // Violation Snapshot
    let vshDataRange = vsh.getRange("C2:H2").getExtendedRange(ExcelScript.KeyboardDirection.down); // get Snapshot Helper data range
    let vsNextRow: number = vs.getRange("A:A").getUsedRange().getRowCount() + 1; // get Snapshot used range
    let vsDataDest = vs.getRange("A" + vsNextRow); // set data paste destination
    vsDataDest.copyFrom(vshDataRange, ExcelScript.RangeCopyType.all, false, false); // copy and paste data into Snapshot
}

Answer 1

• The nested loop is overly used.
• All operation with Excel cells consume some resource, eg. getRange, CopyFrom
• Instead of CopyFrom, consider using setValue.
• The better apporach is to manipulate data in memory with array(list).

For example:

• Step 1 updates Col A, Step 2 updates Col B
• Inner for loop is not related to either i (outer loop variable) or Col A, but it has been executed multiple times. (from i=2 to uniqueCAMRows)

    // =======================
    // snapshot loop
    // Status Snapshot loop
    for (var i = 2; i <= uniqueCAMRows; i++) { // initiate for loop
        // Step 1
        let searchCAM = ssh.getRange("A" + i); // set the CAM to search for
        let searchCAMdest = ssh.getRange("H2"); // set the CAM search destination
        searchCAMdest.copyFrom(searchCAM, ExcelScript.RangeCopyType.values, false, false); // paste CAM into search destination
        for (var j = 2; j <= uniqueCustRows; j++) {
            // Step 2
            let searchCust = ssh.getRange("B" + j);
            let searchCustdest = ssh.getRange("I2");
            searchCustdest.copyFrom(searchCust, ExcelScript.RangeCopyType.values, false, false);
            // Step 3
            let dataRange = ssh.getRange("H2:K4"); // set snapshot data range for current CAM
            let sshRange = ssh.getRange("D:D").getUsedRange(); // get used range in snapshot data
            let sshNextEmpty: number = sshRange.getRowCount() + 1; // get row count in snapshot data
            let dataDest = ssh.getRange("D" + sshNextEmpty); // find data paste destination
            dataDest.copyFrom(dataRange, ExcelScript.RangeCopyType.values, false, false); // paste snapshot data into destination
        }
    }

---

• Some optimization suggestion for each step.

    // snapshot loop
    // Status Snapshot loop
    for (var i = 2; i <= uniqueCAMRows; i++) { // initiate for loop
        // Step 1
        let searchCAM = ssh.getRange("A" + i); // set the CAM to search for
        let searchCAMdest = ssh.getRange("H2"); // set the CAM search destination
        searchCAMdest.copyFrom(searchCAM, ExcelScript.RangeCopyType.values, false, false); // paste CAM into search destination
    }
     
    // Step 1 update, no loop, set values for destination range
    let desRangeA = ssh.getRangeByIndexes(1, 0, uniqueCAMRows-1, 0);
    let srcValH = ssh.getRange("H2").getValue();
    desRangeA.setValue(srcValH);

    // =======================
        for (var j = 2; j <= uniqueCustRows; j++) {
            // Step 2
            let searchCust = ssh.getRange("B" + j);
            let searchCustdest = ssh.getRange("I2");
            searchCustdest.copyFrom(searchCust, ExcelScript.RangeCopyType.values, false, false);
            // Step 3
        }

    // Step 2 update, no loop, no loop, set values for destination range
        let desRangeB = ssh.getRangeByIndexes(1, 1, uniqueCAMRows-1, 1);
        let srcValI = ssh.getRange("I2").getValue();
        desRangeB.setValue(srcValI);

    // =======================
        for (var j = 2; j <= uniqueCustRows; j++) {
            // Step 3
            let dataRange = ssh.getRange("H2:K4"); // set snapshot data range for current CAM
            let sshRange = ssh.getRange("D:D").getUsedRange(); // get used range in snapshot data
            let sshNextEmpty: number = sshRange.getRowCount() + 1; // get row count in snapshot data
            let dataDest = ssh.getRange("D" + sshNextEmpty); // find data paste destination
            dataDest.copyFrom(dataRange, ExcelScript.RangeCopyType.values, false, false); // paste snapshot data into destination
        }    

    // Step 3 update, run four lines before `for` loop, set values for destination range
        let dataRange = ssh.getRange("H2:K4"); // set snapshot data range for current CAM
        let dataValue = dataRange.getValues();
        let sshRange = ssh.getRange("D:D").getUsedRange(); // get used range in snapshot data
        let sshNextEmpty: number = sshRange.getRowCount() + 1; // get row count in snapshot data
        for (var j = 2; j <= uniqueCustRows; j++) {
            let dataDest = ssh.getRange("D" + sshNextEmpty).getAbsoluteResizedRange(2,4) ; // find data paste destination
            dataDest.setValues(dataValues);
            sshNextEmpty = sshNextEmpty + 2;
        }