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?
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
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:
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
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
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:
shopRanges.forEach
, the code checks if item[0] != el
and then deletes a row. This can cause a potential issue:
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--;
.item[0] != el
is correct, and you're deleting the intended rows.Row Indexing:
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:
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