优化 Power Automate 的 Officescript

问：

第一篇文章在这里。

我的任务是为 Excel 文件中的一些数据创建快照，并将其存储起来以在 Power BI 报表中使用。我对 Officescript 和 Power Platform 比较陌生，有一些代码背景。我在制作这个时正在学习 Officescript，所以我希望我的代码中有很多优化工作要做。

我必须在两个级别上收集数据：CAM（员工）和客户，然后对每个级别进行多次计数。为此，我通过为嵌套在 CAM for 循环中的 Customers 运行一个 for 循环来做到这一点。该脚本将当前搜索输入到帮助程序范围中的指定搜索框中，用于计算必要的计数。然后，该脚本将帮助程序复制到更大的列表中，删除零个数据行，然后将列表复制到历史数据列表。

若要自动运行快照脚本，我使用 Power Automate 流运行桌面流，该桌面流刷新 Excel 文件中的数据连接，然后（返回云端流）运行 Officescript。问题是脚本不断超时。我尝试过分块代码，但它似乎仍然超时。

希望有人能帮助我确定使它运行得更快的方法，这样它就不会超时。

代码在这里：

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
}