Power Automate - Building Flows

Unanswered

Running Script Error

Like (0) Share

Report

Posted on 20 May 2023 08:51:21 by NeilNPH

Good day,

So as the final action in a flow I want Power Automate to run an excel script. When I run this script manually in excel it works no problem.

When I ask PA to run it I get an error message

We were unable to run the script. Please try again.
Runtime error: Line 5: _a.sent(...).findAsync is not a function
clientRequestId: 0e46d584-7d5a-4ad4-a222-1a051bdb220b

Script below, any help would be apprciated.

function main(workbook: ExcelScript.Workbook) {

let worksheet = workbook.getWorksheet("Change Order Form");

let table = worksheet.getTables().find(t => t.getName() === "table5");

let tableDataRange = table.getRangeBetweenHeaderAndTotal();

let dataValues = tableDataRange.getValues();

let removed = 0;

for (let i = dataValues.length - 1; i >= 1; i--) {

let rowData = dataValues[i];

let isEmptyRow = rowData.every(value => value === '');

if (!isEmptyRow) {

let rowToDelete = tableDataRange.getRow(i);

rowToDelete.delete(ExcelScript.DeleteShiftDirection.up);

removed++;

}

console.log(`Removed ${removed} rows.`);

}

I have the same question (0)

All responses (5)

Answers (0)

VincentGonza 3 on 08 May 2024 at 15:47:36

Like (0)

Report

Re: Running Script Error

What was your work around? I've been stuck on this for a while now.

Was this reply helpful? Yes No
NeilNPH 29 on 15 Aug 2023 at 05:07:20

Like (1)

Report

Re: Running Script Error

I ended up telling chat gpt there was an error and running through multiple versions of the script and eventually we got one that works. It does not use the same asyn main function as your version but I shall keep this in mind for the next time that this inevitably comes up as an error.

Thanks

Was this reply helpful? Yes No
darren-h-gill-m 15 on 14 Aug 2023 at 10:35:58

Like (0)

Report
Re: Running Script Error

I have the exact same error - it only manifests itself when running under PowerAutomate.

I tried to write a manual function to use a traditional for(;;) loop version to see if I could avoid the issue:
function getTableInSheetWithName(sht: ExcelScript.Worksheet, name: string😞ExcelScript.Table|undefined {
  let aTables = sht.getTables()
  for (let i = 0; i < aTables.length; i++) {
    let tbl = aTables[i]
    if (tbl.getName().toLowerCase() === name.toLowerCase()) {
      return tbl
    }
    return undefined
  }
}

But this just returns undefined!

My solution that works is this:
Mark the main function as async
async function main(workbook: ExcelScript.Workbook) {
then get a reference to your desired table like this:
    let tbl: ExcelScript.Table
    let aTables = await shtDirectory.getTables()
    for (let i = 0; i < aTables.length; i++) {
      tbl = aTables[i]
      if (tbl.getName().toLowerCase() === TABLE_DIRCTORY_APPPOINTMENTS.toLowerCase()) break;
    }
This works for me.

Clearly, this is a bug in ExcelScript. I hope it will be resolved at some point so that your original code runs fine. However, as of Augist 2023 this workaround resolves the problem when running from PowerAutomate (MS Flow)

Part of the problem here is that the ExcelScript object model as documented is not actually the object model we are in fact working with.

For example, the documentation suggests that the result of a call to method "getTables" of a Worksheet results in an array or ExcelScript Table objects. It does not! What we actually get is a Promise of an Array of ExcelScript Table objects.

Behing the scenes the ExcelScript is doing somethig clever to the code in the OSTS files to effectiveley perform the work of async/await.

However, for some reason that work is not performed when running from PowerAutomate, maybe it will soon when someone in MS catches on to this bug.

(For the record, I don't know what it is they are doing behind the scenes to take away all the asynchronous wiring, but it's great in allowing us to develop synchronous style code in an asynchrounous world, I just wish they'd tell us the truth in the documentation so we can be aware of it!)

Was this reply helpful? Yes No
NeilNPH 29 on 20 May 2023 at 14:18:59

Like (0)

Report

Re: Running Script Error

Hi Nived there you go. Thanks
Above that is the get respond from forms that's all.

Was this reply helpful? Yes No
Nived_Nambiar 18,014 Super User 2025 Season 2 on 20 May 2023 at 11:29:59

Like (0)

Report

Re: Running Script Error

Hi @NeilNPH

share screenshot of your flow

Was this reply helpful? Yes No

Community site session details

Running Script Error

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

MS.Ragavendar – Community Spotlight

Congratulations to the August Top 10 Community Leaders!

Leaderboard > Power Automate

Featured topics

Product updates

Community site session details

Running Script Error

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

MS.Ragavendar – Community Spotlight

Congratulations to the August Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Power Automate

Featured topics

Product updates