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

[Bill51106]

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)

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?

[Yutao]

@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]

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

[Yutao]

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

[Bill51106]

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

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]

@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]

@Yutao 

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

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

[Yutao]

@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).

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!

[Bill51106]

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

[Gowmadrid82]

Did this ever get resolved? if so, what was the outcome? Thanks

[Bill51106]

My script was poorly written and timed out during run, causing it to run again.