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

Excel to Sharepoint Scheduled Flow

I've created a flow that goes through an excel file stored in a document library and updates a SharePoint list based on the contents of the spreadsheet. It deletes items from the list if they're not in the spreadsheet, creates now items in the list if they're not in sharepoint and so on.  This worked fine when I was just starting out with a few items in my excel file, but now I have over 1500 rows.  The first issue is that it seems to take forever to run because it's basically looping through all items.  I'm now wondering if it would be quicker to delete the contents of the SP list with the flow at the start then just import the entire spreadsheet into the list with the flow, I'm not sure if that's possible to do?

 

The other issue I was having was that some columns weren't getting populated, I'm not sure why, I checked all the columns, I may just try to recreate the flow to see if that resolves the issue there.

 

The third issue was that it was only retrieving a maximum of 256 items from excel and copying them into SP list.  I found another post that says the limit is 2048 and the 256 limit can be changed to 2048.  Is 2048 really the most rows I can copy into the list with a flow?

 

Sorry for all the questions.

7 REPLIES 7
manuelstgomes
Level 10

Re: Excel to Sharepoint Scheduled Flow

HI @Manc_Gurl 

 

You've got yourself a challenge there. Let's see if we can figure this out.

 

SYNC

Pulling all items is not scalable. Is your flow running once a day or triggering automatically? 

 

Let's say that you run this once a day. Add a "Changed Date" to Excel. SharePoint already does that for you, so you don't need to worry about that. Then, when you run the Flow, you can fetch only the ones that changed during that period. By doing this, you only get items that you need to do something and not all of them.

 

Also, you'll get a lot fewer rows, and the Flow will run a lot faster. If you hit a limitation, increase the period. For example, if you get 1000 items to sync if you sync once a day, do it 2 or 3 times a day, and the number of things to sync will be lower. 

 

Does it make sense? 

 

To fetch only the ones parsed in a specific period you can use the Advanced Options (to access it press the "Advanced Options") and in the filter query do something like:

 

small-Screenshot_2019-10-22_at_10_46_41.jpg

 

ISSUES IN POPULATING

These issues are trickier to debug, but try to solve the first issue first. Then you'll have fewer data do check, and probably you can find a pattern. Check if you're trying to insert data in columns that are not correct, for example, a date formatted incorrectly.

 

MAX ITEMS

You can check the solution above. Run more frequent updates until you have only a few items to sync at a time. It will run faster and will have less information to sync.

 

Does this help you?

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

 

 

Dual Super User
Dual Super User

Re: Excel to Sharepoint Scheduled Flow

Hey @Manc_Gurl 

 

So as you are iterating over each item it definitely will take a lot of time. If you can share a few screenshots of the flow you have built so far, we might be able to suggest some workarounds to make it faster. 

 

About the other question on the row limit, I myself tried for about 36K rows and it worked just fine. In the list rows action, you can mention the number as 50000 and it can get you those many nmber of rows. (50K) is the limit here and you might not get 50K rows due to the data restrictions but the point here is you can get upto 40K easily. 

 

Hope this Helps!

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. If you liked my response, please consider giving it a thumbs up. THANKS!

Manc_Gurl
Level: Powered On

Re: Excel to Sharepoint Scheduled Flow

temp1.JPG1temp2.JPG2temp3.JPG3temp4.JPG4temp5.JPG5temp6.JPG6temp7.JPG7

 

Here is what the flow looks like now. It's probably not right and there are probably much better or more efficient ways of doing what I'm doing, I'm still fairly new to all this Flow stuff 🙂

 

 

Dual Super User
Dual Super User

Re: Excel to Sharepoint Scheduled Flow

Hey @Manc_Gurl 

 

This looks good. Here is one more thing you can do to keep deleting items on the go: Use an array for the unique values or values that you want to keep and then check with those, delete the ones that do not match. 

 

You can refer this blog for some hints and tips: https://powerusers.microsoft.com/t5/Microsoft-Flow-Community-Blog/Delete-duplicate-records-items-fro... 

 

This might make your flow a bit faster. 

 

Hope this Helps!

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. If you liked my response, please consider giving it a thumbs up. THANKS!

Manc_Gurl
Level: Powered On

Re: Excel to Sharepoint Scheduled Flow

@yashag2255 Thanks for the information that will help a lot 🙂

 

This might seem like a crazy idea, but I am thinking now it might be better to just empty/delete the SP list items every week (not sure if this is possible with a flow)?  Then run a flow to import the whole spreadsheet into the list.  That cuts out the need for iterating through the list and checking for updates, if I could use flow to delete the entire list contents and then copy the excel data into via flow to replace it all that would work too.  Would that be more efficient? And if so how the heck do I do that 😄

Dual Super User
Dual Super User

Re: Excel to Sharepoint Scheduled Flow

Hey @Manc_Gurl 

 

Yes you can do that as well. But does the excel get new data every week? Because if not, it would not be that efficient to reload all the data you just deleted from the list. 

 

However, if you plan to do it that way, you can set up a recurrecne flow and then get items from SP and delete item action to delete the items. Ensure that you appropriately coordinate the two flows so that one does not interfere with the outcomes of the other and produce any undesired results/scenario. 

 

Hope this Helps!

 

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. If you liked my response, please consider giving it a thumbs up. THANKS!

Manc_Gurl
Level: Powered On

Re: Excel to Sharepoint Scheduled Flow

@yashag2255 I took a look at that article and I don't really understand it or rather I don't really understand how to apply that to my situation.  I've never done an array before so I'm not really sure how this would help me in my situation or how to apply that to my flow.  Would it speed it up? I think you're right about deleting all items not being efficient.  I really just want the flow to run faster, it is taking over half an hour just to do around 50 items so if I have over 1500 items in my spreadsheet you can imagine how long that is going to take to run.

 

The SharePoint list is never going to manually be changed, I want it to be updated only from the Spreadsheet contents.  The Spreadsheet it a query from a database that will change on a regular basis so I want any deletions, updates or new items in the spreadshee to be reflected in the Sharepoint list.

 

Could you give me some advice about how to use an array in my scenario to speed it up please?

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,884)