When I run a script to consolidate data in Excel Online, it works perfectly within Excel Online itself. However, when I try to execute the same script using Microsoft Flow, I encounter an error message:
The script cannot be executed. Please attempt it again. Runtime error: Line 19: namedRanges.findAsync is not a function clientRequestId: e0d62dd6-eada-4d04-b081-13e7c5400748
Below is the script being used:
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!");
}
An error occurs on the line where 'namedRanges.find(...)' is used. There seems to be no 'findAsync' method in the Office Scripts API, causing this issue. I'm unsure of the reason behind this error.
I would appreciate any assistance in understanding what's causing the problem and how to resolve it.