cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TesDA
Continued Contributor
Continued Contributor

Excel Flow Fails and then runs fine

Basically I have an excel flow that recreates a table from SQL every night. Each night it goes into the excel file, deletes the old rows (usually ~400), and then imports the new rows from sql. Starting about a month ago, it started failing but when I reran it, it was fine. So now, I have it set that if it fails, to just go and rerun the process and that works perfectly... however it would be alot better to know what is causing it to fail in the first place.

 

I get the error: No row was found with Id: ...

 

ExcelFlowFail.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Brad_Groux
Community Champion
Community Champion

I'd wager that this is because the Excel file is locked out for 6-12 minutes in time. This is the main reason most Flow experts would never recommend using Excel if at all possible. File lockouts are one of the major known issues and limitations for the Excel connectors: 

You should use a SharePoint list for this sort of operation, or ensure that no other users, Flows or operations are accessing the Excel file at or around the recurrence time set within the Flow.

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION ☑️. If you find this reply helpful, please consider giving it a LIKE.

 

 

 

View solution in original post

5 REPLIES 5
Brad_Groux
Community Champion
Community Champion

I'd wager that this is because the Excel file is locked out for 6-12 minutes in time. This is the main reason most Flow experts would never recommend using Excel if at all possible. File lockouts are one of the major known issues and limitations for the Excel connectors: 

You should use a SharePoint list for this sort of operation, or ensure that no other users, Flows or operations are accessing the Excel file at or around the recurrence time set within the Flow.

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION ☑️. If you find this reply helpful, please consider giving it a LIKE.

 

 

 

TesDA
Continued Contributor
Continued Contributor

@Brad_GrouxI was afraid that would be the answer. Unfortunately this is one of the few options for what we need to do. Some people just really love excel. Appreciate your insight.

@TesDA it is a common problem and @Brad_Groux is probably correct that the file is locked. But you can work around with with a do until loop like this:

 

Excel-Online-Locked.png

This loop will continue to run the Excel action until it is successful. It checks the return code from the Excel operation. The locked status is "423" and the message is "The resource you are attempting to access is locked". 

 

Once that status is no longer returned from the Excel operation then the flow continues.

The problem is attempting an Excel operation, while the file is in lockout, can extend the file lockout period. In my extensive testing, there are very different outcomes at different times when dealing with Excel lockout issues, they are nearly impossible to predict. Even querying it with a GET API call can cause a lockout. The file doesn't have to be updated; it can be simply accessed to trigger a lockout - and a lockout can be more than just a code 423. For this reason, I have and never will recommend using Excel if at all possible - especially for business-critical Flows.

 

Edwardsm1
Frequent Visitor

How do you set a the flow that if it fails to rerun the process. Because Unfortunately I have many people using the excel workbook when the flow is running and I can't transfer to SharePoint because there are people who are using the excel file that are not in my organization and they wont have access in the SharePoint.  If someone can show me I would really appreciate it. 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (2,140)