Unanswered

Office script fails in 20% of Power Automate runs but never when run in Excel

(0) Share

Report

Posted on by ggbsms

Hi, I am having trouble with an unstable Power Automate flow because, 20% of the time, Power Automate cannot run an office script in the flow successfully. Even though the Power Automate data output to the Excel online workbook has not changed between runs. When I run the script in Excel, it runs successfully 100% of the time without waiting to see if it runs.

When the run fails in Power Automate, I always get this error message: "We were unable to run the script. Please try again.
Office JS error: Line 214: {"message":"The argument is invalid or missing or has an incorrect format.","code":"InvalidArgument","type":"Worksheet","method":"getRange","line":248}"

clientRequestId: 59aeac90-65a9-4836-a979-8e243cd478e4

I have been unable to solve this. Does anyone have an answer or a clue for me?

Categories:

Using connectors

script_snippet.PNG

I have the same question (1)

All responses (5)

Answers (2)

Heartholme 1,278 Super User 2024 Season 1 on at

Like (0)

Report
Hi @ggbsms,

The error occurs at the getRange method on line 248, which means there might be an issue with the argument passed to this method.

Row Deletion Logic:

Inside the shopRanges.forEach, the code checks if item[0] != el and then deletes a row. This can cause a potential issue:

When you delete a row, all the rows below it shift up. So, by incrementing the row variable, you might skip checking some rows. To handle row deletion while iterating, consider decrementing the row count after deleting, which you are already doing with row--;.

Ensure the logic behind item[0] != el is correct, and you're deleting the intended rows.

Row Indexing:

The range is built with let str = row.toString().concat(":").concat(row.toString());, which would result in a range like "2:2". Ensure that this format is what you intend. If you're trying to reference a complete row, this format is correct. However, if you're aiming to reference a cell or a range of cells within a row, you'll need a different format.

Initialization of Variables:

The variables shopRanges and el are used within the shopRanges.forEach loop. Ensure that these variables have been correctly initialized and populated with the necessary data before this loop.

My recommendation: If the issue is indeed with the getRange method, closely inspect the values of row during each iteration, especially when a row is deleted. Ensure that row doesn't go out of bounds or reference an invalid range.

Finally, to further assist, it would be helpful to have the complete context of the script, including the initialization of the variables and the data structure of the Excel sheet.

✔ Marking my post as a solution not only confirms that your question has been answered, but also helps others find solutions to similar problems.
By doing so, you'll be making a valuable contribution to the community.

Best Regards
Heartholme

Was this reply helpful? Yes No
ggbsms 15 on at

Like (0)

Report

Thanks for replying Heartholme! I will take a closer look at the deletion logic. But what I don´t understand is mainly this. Why does the code run in Excel without a problem no matter what dataset is thrown at it while the exact same code returns an error only 20% of the time even though the dataset always stays the same? Why doesn´t it always throw an error for that particular unchanged dataset? Best, ggbsms

Was this reply helpful? Yes No
Verified answer

Heartholme 1,278 Super User 2024 Season 1 on at

Like (0)

Report
I agree with you, that is indeed puzzling. It's a bit hard for me to comment on, but it might be one of the following issues:

Concurrency Issues

If you have multiple flows or processes that interact with the same Excel file simultaneously, this can can create issues. As far as I've understood from your post, this probably is not the issue.

Caching

It might be that Excel Online are caching certain operations and data. So when you run your script locally, these caches could be different compared to when you use Power Automate to execute the scripts.

Latency in Power Automate:

Power Automate interacts with Excel Online over the internet, and there might be minor latency or delays. These delays can sometimes cause race conditions or unexpected behaviors, especially if certain operations in the script rely on timing.

Again, this is just me brainstorming ideas. To really see what's happening in PowerAutomate vs Excel, I suggest that you implement some logging in your script. This should uncover any inconsistencies.

BR

Heartholme

Was this reply helpful? Yes No
Verified answer

ggbsms 15 on at

Like (1)

Report

Hi @Heartholme
I think I have resolved the issue. In line 214, I grab the newly made table from when I copy a sheet (with a table) over to another new sheet one line above. Successful table grabbing can sometimes take more time (in milliseconds) than usual when the script is run from Power Automate. So, 20% of the time, the table was not successfully grabbed before I immediately deleted it when I converted it to a range of two lines below. So the solution was to move the deletion of the table to the end of the loop, some 20-30 lines down, to give the table grabbing more time to succeed. I have now run the Power Automate flow ten times on the same dataset as when it used to fail and five times on other datasets bigger and smaller. It succeeded every time. So, I think I have solved the issue. I appreciate your sharing the brainstorming. Best, Gunnar

Was this reply helpful? Yes No
Heartholme 1,278 Super User 2024 Season 1 on at

Like (0)

Report

@ggbsms

Perfect, I'm very glad to hear.

Br

Joachim

Was this reply helpful? Yes No