cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bill51106
Helper III
Helper III

Flow fails with Office Script, it try's to run it twice. sometimes.

At the top of the hour I have a Flow that drops an Excel file into SharePoint, runs an Excel office script, and mails it out. On occasion it fails with the "A table can't overlap another table." and references line 10 (The first part of my scrip is to turn the data into a table)

 

Capture.PNG

 

The problem is the script has already ran, when I go into the file it has been completed. Viewing the history in share point shows 2 versions, before and after. Why does it try to run the file a second time?

8 REPLIES 8
Yutao
Microsoft
Microsoft

@Bill51106 - this does sound strange. Can you share a bit more about your flow (via screenshot or describing the trigger and actions) here? Thanks!

Bill51106
Helper III
Helper III

@Yutao 

I sure can. One thing I forgot to mention, maybe not even relevant. I have 2 identical versions of the flow set up, one to run at 6am daily and the same one to run hourly. Different flows, same script but different folders but same file, slightly updated etc. The ones that run at 6am I don't recall ever failing like this, maybe they have?

Capture.PNGCapture1.PNG

Yutao
Microsoft
Microsoft

@Bill51106 -

 

Thanks for sharing the details!

 

Just wanted to be clear about the two flows - so they both trigger the same script, but against different Excel workbooks, right? Because if they happen to run the script against the same workbook, the situation you mentioned above indeed might happen (e.g., the script in the first flow adds a table to the workbook and the script in the second flow tries to add the table again at the same location of that workbook).

 

Regarding troubleshooting, another thing you can try is to look into the run history of the two flows. See if there is a failing pattern or anything suspicious.

@Yutao 

 

Correct, against 2 different flows / workbooks. The only thing in common is the same script. We have some data that updates hourly and dumps out into a workbook. I did not want to wait a full day to verify changes so I made a copy and set a bunch of runs for the top of every hour. It is unique values and this has been tried on a different account. The first, maybe 2nd, sometime 3rd works but it will fail by the 4th.

 

My flow grabs that export at 6AM daily and run the script and off it goes. The flow works every single time.

 

I make a copy of that same flow and trigger it at the top of every hour, it will fail the table error. First time always works. One thing I did on the 3PM run on oct 26 was up my delay from 5 to 10 minutes as I was getting "locked" errors prior on a delete.

 

Capture.PNG

 

Another weird thing, the time. The more I think about this, it almost seems my flow actually runs, it has a 5 min timer delay. My entire Flow takes less than 2 minutes to run without a delay. Does it try to rerun the office script after completing the flow and then erroring out like it never ran? I am so confused. It seems once every 24 hours is ok, or I just might be going crazy, 50/50 at this point.

Yutao
Microsoft
Microsoft

@Bill51106 ,

 

Sorry for the late response. Have you tried looking into an individual failed run and see if there was any rerunning or retrying?

 

If you're still seeing this issue, do you mind sharing the "clientRequestId" (you can get it from a failure message) so we can further investigate it on our side? Please note this ID is totally random and won't reveal any personal or sensitive information. Feel free to send via private message if you want.

Bill51106
Helper III
Helper III

@Yutao 

 

02647b18-cfae-43b5-a2af-329e8053a110

 

Please let me know what I am doing wrong, thank you!

Yutao
Microsoft
Microsoft

@Bill51106 ,

 

According to our service logs, it seems there was always a timeout failure (HTTP status code 504 Gateway Timeout) right before the "table overlapping" error (which is normally a 400 Bad Request).

 

Yutao_1-1636133657673.png

 

Do you know how long it normally takes for your script to complete? Could it be that the first run took too long to finish and timed out the flow, so the succeeding actions in this flow (e.g., delete file, etc.) were all skipped? Then the second run kicked in and tried to execute the script against the same workbook that didn't get deleted in the previous run and still had the table. Thus caused the table overlapping error.

 

However without close inspecting the flow and script, the above was just my guess.

 

A few things you can try for further troubleshooting:

 

  • To prevent the timeout issue, see if you can optimize your script to make it run faster. Here are some general performance tips:
    • Avoid Excel API calls as much as possible inside loops.
    • Try to capture API results into variables if they can be reused.
    • Instead of iterating through cells and calling cell.getValue(), try retrieving all the values of the whole range into a two-dimensional array (i.e., let values = range.getValues()) and iterating through that array.
    • Same thing when setting cell values - use range.setValues(values) instead of calling cell.setValue() repetitively inside a loop.
    • Instead of tackling with all the cells in the whole worksheet, try things like worksheet.getUsedRange() to work on only the cells that have values.
    • This articles talks about some good performance tips: https://docs.microsoft.com/en-us/office/dev/scripts/develop/web-client-performance.
  • Double-check a failed run and see if it did a retry for the "Run script" action.
  • Try to give your workbook a different name every time. Doing this might also help you to see if the workbook actually gets deleted after each run.

 

Please let me know how it goes!

 

@Yutao

 

Thank you, that does make sense. I am learning, baby steps 🙂

 

I PM'd you my script I built. Under // ***Code*** there is a lot of the same replace all. It was never intended to get this big but I think this is the cause of the slowness. I deleted them out but the example was left in. I would love ANY feedback.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,604)