cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zdliang
Level: Powered On

Excel connector to update a row and save as another file failed

 

Recently, I am working on using flow to handle some excel in email attachment.

I found there is an issue. I create a very simple flow to reproduce the issue.

  • In the 1st step, I update a row in an excel on OneDrive, update the content of row(seq=1) to Test1
  • In the 2nd step, I copy the excel to other location
  • In the 3rd step, I did the update again, update the content of row(seq=2) to Test2
  • In the 4th step, I copy the excel to another location
  •  

I suppose to see step 1 and step 3 updates in 2/Test.xlsx, but I can only see the step 1 updates. It seems due to excel file lock. But there is no error while running the flow. 

 

Did anyone encounter similar issue also?

flow.jpg

 

4 REPLIES 4
Super User
Super User

Re: Excel connector to update a row and save as another file failed

The Excel connectors have some serious limitations, which is why Excel is not recommended by most Microsoft Flow Experts. The documentation for the connectors of Excel, Excel Online (Business), and Excel Online (OneDrive) each outline the limitations of the connectors, including file lockouts. 

All of the Excel connectors have a limit of 100 API calls her 60 seconds, and the Business and OneDrive Excel Online Connectors have the following limitations:

  • The maximum size of an Excel file that is supported by the Excel Online (Business) connector is 25 MB.
  • The data may not be up to date (there may be some delay) when filtering or sorting is used in the List rows present in a table action.
  • The List rows present in a table action supports basic filtering and sorting:
    • Supports the following filter functions: eq, ne, contains, startswith, endswith.
    • Only 1 filter function can be applied on a column.
    • Only 1 column can be used for sorting.
  • An Excel file may be locked for an update or delete up to 6 minutes (Excel Online Business) or 12 minutes (Excel Online OneDrive) since the last use of the connector.
  • A single Excel file should be used by a single connection to prevent writing data from multiple connections and possible data inconsistency.

Again, due to these limitations, most Flow experts do not recommend using the Excel connector if at all possible with your Flows, and if you must use the Excel connectors within your Flow insure that your Flow can work within these strict constraints.

The SharePoint Online connector and SharePoint Lists are much better suited for frequently run Flows and jobs, and as such, would be recommended for most Flows. With my Flows, everything I'd use Excel for, I simply just create a SharePoint list, although Azure Cosmos DB can be handy too. 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. THANKS!

Super User
Super User

Re: Excel connector to update a row and save as another file failed

Please disregard this reply, thanks.

zdliang
Level: Powered On

Re: Excel connector to update a row and save as another file failed

@Brad_Groux thanks for reply. I know those limitations, and all actions in my example flow are within these limitations. File is less than 10 MB, no filter at all, single connection as the flow is manually triggered. The only unsure one is the file lock. It seems the lock time is not as short as 6 mins, it is an magic number, it may last for hours. I will add a delay step after the update step to wait for the file to be unlocked.

 

For me, these issues are defects in Excel connector, not limitation. 

Super User
Super User

Re: Excel connector to update a row and save as another file failed


@zdliang wrote:

@Brad_Groux thanks for reply. I know those limitations, and all actions in my example flow are within these limitations. File is less than 10 MB, no filter at all, single connection as the flow is manually triggered. The only unsure one is the file lock. It seems the lock time is not as short as 6 mins, it is an magic number, it may last for hours. I will add a delay step after the update step to wait for the file to be unlocked.

 

For me, these issues are defects in Excel connector, not limitation. 


The file lock can occur anytime the file is accessed, by anything, not just Flow. The includes by a security/virus software scan, someone viewing the metadata, someone loading it in-browser, literally anything. That's why experts don't recommend using the Excel connector for write jobs. It is nearly impossible, especially in large environments, to insure that a file won't be locked out - and each time the file is accessed during the lockout period, it extends the lockout. 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. THANKS!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,821)