How to speed up Excel data upload to SharePoint List using Power Automate

[Anonymous]

I have setup a flow in Power Automate which basically retrieves data from an excel file that's in a SharePoint document library. The excel file contains a significant volume of data. The workflow flow, on triggering, retrieves the data from excel file using the 'List rows present in a table' step and by checking the ID fields, it creates or updates data in SharePoint. The flow runs successfully and as a result updating the Sharepoint list on a periodic basis. However, one thing I noticed that, it is taking alot of time to execute - a day or two to complete execution. For this reason, I'm looking for some suggestions as to better design my workflow in order to minimize the processing time for it to update the Sharepoint list.

The workflow design is given below:

Would appreciate any advice or suggestion. Thanks

[eric-cheng · Accepted solution]

Hi @Anonymous ,

How many items are in the Excel file?  You may have exceeded the daily limits for an action or for your flow.  

I would firstly examine whether all the looping is required and whether there is an opportunity to optimize the flow?

If not, you can investigate enabling concurrency and tweaking the parallelism of the loops.  Be careful if you are setting variables as it can muck things up with many branches running in parallel.

Another option is to use the SharePoint APIs.  This article is for a totally different thing to what you are trying to do but it outlines some performance gains using the SharePoint API

Depending on the issue, PA might not be the best tool and you may need to look at other things e.g. Logic Apps (Azure version of PA) or Azure Data Factory.

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

[Anonymous]

Hi @eric-cheng ,

Thank you for your response. The excel file has 4000 items. I have implemented a loop so that it can check and update for each sharepoint row of data and if the excel file has new rows, it can add the same in Sharepoint list. And I'm also researching and working through options to optimize this workflow as its taking a lot of time. Appreciate your advice on using the SharePoint API but, I want to stick with using Power Automate for now and to see whether the flow can be executed a bit faster.

In terms of the flow execution timing, I agree with you, as its reading data from excel and updating the Sharepoint list, it would take a significant amount of time for it to complete. However, one theory that I have in mind, as a work around, is to modify the workflow such that, it gets all the excel data in one go and that data be stored in a collection / array of some sort. That way, it would avoid the periodic data read from the excel file time to time and the workflow can use that collection instead to update the SharePoint list. I'm currently exploring this option and to check whether this could be a possible approach to minimize the execution time.

For now, I'm giving it a go with enabling the concurrency control like you mentioned and to check the test results. If you have any further suggestions or advice for workflow improvement/ optimization , it would be really appreciated.

[takolota]

You can also set up batch actions…

Batch Create to SharePoint: https://youtu.be/2dV7fI4GUYU

Batch Update SharePoint: https://youtu.be/l0NuYtXdcrQ

For your use-case, you’ll need to use some Filter array actions to get arrays of the records that need to be created and the records that need to be updated.

I used this set-up to register & check-in almost 100,000 people for a series of holiday events.