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

[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?

[Heartholme]

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. 

1. 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.

2. 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.

3. 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

[ggbsms]

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

[Heartholme · Accepted solution]

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: 

1. 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. 
2. 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. 
3. 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

[ggbsms · Accepted solution]

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

[Heartholme]

@ggbsms 

Perfect, I'm very glad to hear. 

Br

Joachim