Running Script Error

[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.`); 

}

[Nived_Nambiar]

Hi @NeilNPH 

share screenshot of your flow 

[NeilNPH]

Hi Nived there you go. Thanks

Above that is the get respond from forms that's all. 

[darren-h-gill-m]

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!)

[NeilNPH]

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

[VincentGonza]

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