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?
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?
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.
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.
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.
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:
Please let me know how it goes!
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.
Learn how to create your own user groups today!
Check out the new Power Platform Community Connections gallery!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.