0

I have a script that consolidates data in the online version of Excel. The script works perfectly when run directly within Excel Online. However, when the same script is executed using Microsoft Flow, I encounter the following error:

We were unable to run the script. Please try again. Runtime error: Line 19: namedRanges.findAsync is not a function clientRequestId: e0d62dd6-eada-4d04-b081-13e7c5400748

Here is the script:

function main(workbook: ExcelScript.Workbook) {
    const names = ["EVMSChart", "AC", "EV", "PV"];
    let outputRow = 0;
    let outputWs = workbook.getWorksheet("Graph Data");

    if (!outputWs) {
        outputWs = workbook.addWorksheet("Graph Data");
    } else {
        outputWs.getRange("A:BZ").clear();
    }

    outputWs.getRange("A1:E1").setValues([["Sheet Name", "EVMSChart", "AC", "EV", "PV"]]);

    workbook.getWorksheets().forEach((ws) => {
        let max = 0;
        let hasData = false;
        for (let i = 0; i < names.length; i++) {
            const namedRanges = ws.getNames();
            const namedRange = namedRanges.find(nr => nr.getName() === names[i]);//Line 19
            if (namedRange) {
                const range = namedRange.getRange();
                const data = range.getValues();
                let outputData: (string[][]) = [];
                for (let j = 0; j < data.length; j++) {
                    const row = data[j];
                    for (let k = 0; k < row.length; k++) {
                        const value = row[k].toString();
                        outputData.push([value]);
                        max = Math.max(max, k);
                    }
                }
                if (outputData.length > 0) {
                    outputWs.getRangeByIndexes(outputRow + 1, i + 1, outputData.length, 1).setValues(outputData);
                    hasData = true;
                }
            }
        }
        if (hasData) {
            outputWs.getRangeByIndexes(outputRow + 1, 0, max + 1, 1).setValues(Array(max + 1).fill([ws.getName()]));
            outputRow += max + 1;
        }
    });

    const tblDataRange = outputWs.getRange("A1").getSurroundingRegion();
    const tbl = outputWs.addTable(tblDataRange, true);
    tbl.setName("data");

    console.log("Done!");
}

The error message points to the line where I use namedRanges.find(...). As far as I can tell, there's no findAsync method in the Office Scripts API. I'm not sure why this error is occurring.

Could someone please help me understand what's going wrong here and how to fix it?

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
Dejan Dozet
  • 948
  • 10
  • 26

1 Answers1

0

After closely examining the error message in Power Automate, I realized that the issue might stem from how Power Automate handles the JavaScript find() function. To eliminate this potential source of confusion, I decided to replace find() with a forEach() loop for array iteration. This approach worked effectively.

Here is the corrected script:

function main(workbook: ExcelScript.Workbook) {
    const names = ["EVMSChart", "AC", "EV", "PV"];
    let outputRow = 0;
    let outputWs = workbook.getWorksheet("Graph Data");

    if (!outputWs) {
        outputWs = workbook.addWorksheet("Graph Data");
    } else {
        outputWs.getRange("A:BZ").clear();
    }

    outputWs.getRange("A1:E1").setValues([["Sheet Name", "EVMSChart", "AC", "EV", "PV"]]);

    workbook.getWorksheets().forEach((ws) => {
        let max = 0;
        let hasData = false;
        for (let i = 0; i < names.length; i++) {
            const namedRanges = ws.getNames();

          namedRanges.forEach(nr => {
            if (nr.getName() === names[i]) {
              const range = nr.getRange();
              const data = range.getValues();
              let outputData: (string[][]) = [];
              for (let j = 0; j < data.length; j++) {
                const row = data[j];
                for (let k = 0; k < row.length; k++) {
                  const value = row[k].toString();
                  // Write only the value to the outputData array
                  outputData.push([value]);
                  max = Math.max(max, k);
                }
              }
               if (outputData.length > 0) {
                outputWs.getRangeByIndexes(outputRow + 1, i + 1, outputData.length, 1).setValues(outputData);
                hasData = true;
              }
            }
          });
        }
        if (hasData) {
            outputWs.getRangeByIndexes(outputRow + 1, 0, max + 1, 1).setValues(Array(max + 1).fill([ws.getName()]));
            outputRow += max + 1;
        }
    });

    const tblDataRange = outputWs.getRange("A1").getSurroundingRegion();
    const tbl = outputWs.addTable(tblDataRange, true);
    tbl.setName("data");

    console.log("Done!");
}
Dejan Dozet
  • 948
  • 10
  • 26