Run Script (excel) in Power automate

Question

Run Script (excel) in Power automate

AT_M 11

Hi,

I created the sort script in excel online and connected to Power automate. It's set up with 'Recurrence' to run daily.
When I tested it as solo, it worked but it's failed when it's run by power automate. The error message was;
--- Quote ---
We were unable to run the script. Please try again.
Workbook not found.
clientRequestId: 0a43a19b-0b2c-4cfc-8ef9-3954a5767e0e
--- Unquote ---

I used workboog.getactiveworksheet method which is default behaviour in powerautomate.
Please advise 1) what is wrong about it and 2) is there any workaround this? Also, is there any method in script to clear table? Because, the table will be overlapped if I don't clear the table.

Look forward to hearing any idea on this.

Martikainen, Olli 0 Reputation points

2023-07-07T09:17:29.4466667+00:00
I am having similar issues. Running a script in Excel works fine, but trying to run that same script through power automate, I get an error.:

Cannot read properties of undefined (reading 'getRange')\r\nclientRequestId: 072594e6-58ca-44ba-b2b0-0b296ff4fb17",

6 answers

Your answer

Martikainen, Olli 0 Reputation points

2023-07-07T09:17:29.4466667+00:00

I am having similar issues. Running a script in Excel works fine, but trying to run that same script through power automate, I get an error.:

Cannot read properties of undefined (reading 'getRange')\r\nclientRequestId: 072594e6-58ca-44ba-b2b0-0b296ff4fb17",

Answer 1

Yutao Huang - MSFT 701 Microsoft Employee

Hi @AT_M ,

Apologize for the late response and sorry to hear your flow didn't work. Were you able to make your script work with Power Automate later?

Regarding clearing a table, did you mean to remove the table all together? You can probably try something like workbook.getTable("Table1").delete().

Hope this helps. Please let us know if you have any further questions.

Thanks!
Yutao

Answer 2

Nauzad Tantra 1

Hi,
Im having the same error. The script runs correctly in Excel, but does not like being called from Power Automate.

Answer 3

Nauzad Tantra 1

@Yutao Huang - MSFT , any luck finding a solution to this issue?

Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2021-04-30T02:41:06.96+00:00

@Nauzad Tantra - sorry to hear you're having the same error. Can you please share the error message you got from the Run script action in Power Automate? Especially for the clientRequestId.

Normally we'd suggest not to use getActiveWorksheet in the script that needs to run in Power Automate. If possible, please use the getWorksheet method instead.

Thank you!

Answer 4

Clenny, Natasha 1

I'm having the same issue where I'm trying to run my script through Power Automate but says

{
"message": "We were unable to run the script. Please try again.\nWorkbook not found.\r\nclientRequestId: 8e991480-2de9-4264-83d5-3e543acf2a0c",
"logs": []
}

I've tried using getWorksheet instead of getActiveWorksheet.

Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2022-02-11T22:14:02.243+00:00

@Clenny, Natasha - Can you check if the workbook this script is configured to run against does exist? Based on the error message, it appears that the workbook cannot be found. The logs on our side based on the clientRequestId also indicates that the workbook was not found. Maybe somehow it's got deleted?

Answer 5

Bebop 1

Hi,

I have the exact same issue as @AT_M - my script runs perfectly fine on a test flow using a manual trigger but gives the following error when ran with a recurrence trigger:

{  
  "message": "We were unable to run the script. Please try again.\nRuntime error: Line 10: Cannot read property 'match' of undefined\r\nclientRequestId: ac406dc6-b786-41b8-ae16-2ce6493b5c32",  
  "logs": []  
}

I do not understand the error message as the match property works fine when testing with a manual flow. It clearly is something that has to do with the trigger @Yutao Huang - MSFT

Thanks

Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2022-03-09T05:56:18.467+00:00

@Bebop - is it possible to share your script snippet? Or could you double check if there is anything like getActiveWorksheet, getActiveCell, etc.?

BTW, for "manual flow", were you referring to the "button-triggered flow" created from Power Automate, or were you talking about directly running the script inside Excel for the web?
Bebop 1 Reputation point

2022-03-14T13:39:04.397+00:00
Thanks for following up @Yutao Huang - MSFT - This is my code snippet, used for regex expressions:

function main ( workbook: ExcelScript.Workbook, searchString: string, regexPattern: string, regexFlags: string ): Array<string> { let matches: Array<string> = [] let re = new RegExp(regexPattern, regexFlags); let matchArray: Array<string> = searchString.match(re); if (matchArray) { for (var i = 0; i < matchArray.length; i++) { matches.push(matchArray[i]); } } return matches; }

And yes, I meant a button-triggered flow for my test flow. After that, I did try with another separate flow with a recurrence trigger set to run every minute and it was successful. Somehow a daily recurrence changes things and the flow fails.

I found a workaround in the meantime but would be happy to be able to implement this as it is more robust for my use case
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2022-03-15T02:52:45.603+00:00

I see - so the script doesn't have any interactions with the workbook and what it does is only the regex matching against searchString passed as the function argument.

May I ask how this searchString is passed into the Run script action? Could you explain a bit more about your flow in Power Automate? Is it possible that some preceding steps in the flow somehow hasn't returned the expected result to feed into the searchString argument?

Share via

Run Script (excel) in Power automate

6 answers

Your answer