Super simple Power Automate flow to get more than 5000 items from a SharePoint list into PowerApps

[@christosv690]

I was about to bin a project until I found this solution. Very helpful. Thanks

[@cloudcomputer4437]

Amazing solution. You have just solved so many of my issues, at a stroke. Thank you.

[@PaulieM]

Thank you. I’m really happy with the solution and early reports from others seem to suggest it works really well for them.

[@cloudcomputer4437]

@@PaulieM I've been trying to hack this solution (i.e. concurrent threads of 5,000 items) to perform a bulk delete on a large SP list, but to no avail. Do you know if it is possible, or am I wasting my time? The little I've looked into it suggests that a Batch solution may be the way to go. Thanks in advance.

[@PaulieM]

@@cloudcomputer4437 I think you could use a method similar to this, but a delete operation is specific to an item, not a range. But you could certainly use the concurrency element, how many items are you trying to delete?

[@cloudcomputer4437]

@@PaulieM I appreciate the quick reply, thanks. It would be somewhere in the region of 50k to 100k - potentially reaching the 250k region, but no greater.

[@PaulieM]

@@cloudcomputer4437 I don't think I would use this approach in that case, simply because it is going to use all of your flow API allocation. I would probably do it with PowerShell or something like that. Are you deleting every item in the list? If so you could automatically delete the entire list and recreate.

[@dgjanes917]

OMG This video helped me so much! I did change a couple of things though. I made the lowestID, highighestID, list name and fields inputs from PowerApps so that I could use it in different apps for multiple lists. Also, now that the lowestID and highestID are determined using a delegable filter in the App, it doesn't need to retrieve any unnecessary records. Thank you so much!!

[@thegame4988]

Amazing solution. Undoubtedly a simplified solution to your previous method, which still worked wonders for me. Thank you

[@adedaporh]

Hi Paul. Thanks for this great video. this has inspired me to create a flow similar to yours but with a twist: the flow can dynamically generate the optimum threads based on the number of items to download. So no more manually adjusting the flow to add the threads and related expressions all over the flow. Just feed it the site url and list name (as long as the connection has access to the list) and the flow will spin-up the apprioprate threads (max.of 50!) and page size to download it! So the flow can (in theory) download upto 250k items.

[@jjfraga22]

Fantastic work @Paulie M !!! The duration of my previous flow was 1d 11:34. My new one made with your solution takes 40m. It´s a game changer ! Thank you very much for sharing. I really appreciated.

[@PaulieM]

Wow - how many rows and columns is that working through?

[@Sensorgrafie]

Gorgeous tutorial. I tried to use the solution to delete list items, but in order to do so I need to iterate thru all items means at each thread from lowID to highID, but a regular index variable can't be used as it would require an individual index per thread. As I am "just" a Citizen Developer I didn't figured out how to achieve this.

[@deepanshsaxena5677]

Just amazing, superbly done!!

[@MrHhhscott]

Just found this video, love this solution. My question is how would I use the output data in the flow. My list I am querying is a reference list that feeds other lists in different solutions. So after I get the data I want to use the data to evaluate if the source data for the other solutions needs to be updated. Thanks.

[@JonatasBeque]

Thanks so much, Paulie!

[@Chipcob66]

Hi Paul. You mentioned a Blog post where the output from the Response action can be used by an http action. Do you have the link to that please?

[@365g]

Great solution. Many thanks for sharing. Is there a slight adjustment I can make to the fields in the query to include a person type say called 'Reviewers'?

[@PaulieM]

I don't see why not. This would result in an array within an array, but there is not a problem with doing that. Have you tried it?

[@365g]

@@PaulieM thanks for the response. Yeah, had a try yesterday and in the response body it threw a null against that field with no nested array. Didn't really persevere tbh, so will give it another crack. Tried also with get items to troubleshoot and got the same result. Have prob missed something, but will give it another go.

[@PaulieM]

@@365g I will have a look and tell you what syntax you should use.

[@365g]

@@PaulieM that would be much appreciated many thanks

[@PaulieM]

@@365g You can modify the fields parameter of the QuerySettings compose like this: ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand,jpiz/ID,jpiz/FirstName,jpiz/EMail&$expand=jpiz Where jpiz is the name of my person picker feild.

[@DeShonClark]

Unheard of speed using Power Automate! This is so awesome. Any issues with throttling by Microsoft?

[@PaulieM]

Not that I’m aware of. Lots of people have now used this method. But flow API limits could be an issue if you have a PowerApp used by many users.

[@PaulieM]

DeShon - your videos are great, will definitely be checking you out more. Love the style.

[@hebercastillorojas573]

Thank you very much for share this solution. one question, is it work whit attachments like .jpg?

[@PaulieM]

Hmmm, probably not. Can you explain what you are trying to achieve a bit more?

[@hebercastillorojas573]

Hi @@PaulieM, thank you for replay my commnt, I have a sharepoint list with more than 30 000 rows and every row has 2 or 3 images stored in the attachment column, with this information, I want to visualize through powerapps. I was thinking that your flow could help and may be I need to develop another flow to get the shareppint attachement for example when I select an item on one gallery that should be conected with the powerapps collect from your flow. Sorry for my bad English :( I'm looking forward to read your comment Thanks!

[@diegoendo6003]

Amazing improve from last video . One question, since the "response action" is PREMIUM how can I replace this actions and get my data to powerapps

[@PaulieM]

Hi Diego, although it is premium it’s free if you are using it to respond to PowerApps and you can use it no problems. Enjoy!

[@vladimir4851]

Hi Paulie, hopefully you are still tracking questions in this thread. I tried to add a 'people' type field in my "fields" section. The flow fails during the SharePoint query step. Any tips on the proper syntax (or if it would be at all possible) to add a people field to the flow? Thanks you!

[@ajambo2010]

Hi Paul, I have applied your method to get more than 5000 items from a spreadsheet and your method to Send a HTTP request to update sharepoint. However I am getting the error message "Number of read requests for identity exceeded the limit of '900' over time window of '00:01:00'. The sharepoint list gets updated but it is missing some items, which I then need to reconcile back. Any ideas of how to overcome the error?

[@ajambo2010]

Thanks for the sharepoint solution video. First I need to get the data into sharepoint. Any suggestions on how to upload 44,000 rows in excel spreadsheet into a sharepoint list?

[@PaulieM]

Everyone seems to be asking that lately! I have a solution, I just need the time to write it up! Will be posting it very soon.

[@videosthatilike3924]

Hi, thank you for sharing. I followed your guide but i got an error says my field is not valid, my filed is a lookup column in sharepoint, other fields is fine. So do you have any suggest to get value from lookup fields?

[@SamRustagi]

Nice work man!!👍

[@PaulieM]

Thanks! Did you implement it?

[@juansepowerplatform]

Hello this is a game changer solution. It works great. I ran into a weird problem and maybe you can help. All works fine until I load the data into the collection, for some reason it loads the column names from your schema. When I download the data from the response action, it has my data schema. For some reason PowerApps still loads yours with the only column we have in common is ID. It is like somehow the response action cached your schema? I tried deleting from the flow and re-adding but it still does that weird. Thank you

[@juansepowerplatform]

Never mind I found the reason why. I removed the connection from powerapps to the flow and recreated it. Then it worked fine. Just in case someone runs into the same problem.

[@PaulieM]

Glad it works well for you. 👍🏻 Well done.

[@aakashkarmakar4265]

Hi Paul, You are just awesome. I wanted to ask if you can please share something for bulk create/delete of sharepoint list items efficiently using microsoft power automate. It will be really helpful. Thank you!

[@PaulieM]

Can do. I’ve been working on that this week actually. Will post it soon.

[@aakashkarmakar4265]

Thank you so much Paul! I will eagerly wait for it.

[@lioneldallapace8833]

Nice! How use delegation with Photo? a gallery of item with 1 or 2, 3 photos per Item? 2 flows and how get the collection Photos?

[@lioneldallapace8833]

is it possible to have other fields than the default field of the collection ??

[@csponge1]

Paulie, is it possible to add multiple lists for "listName" in querySettings?

[@PaulieM]

No, you can only query one list at a time with this flow.

[@ajambo2010]

Hi Paulie, It is all working for me, however I don't have access to the Response action as it is a Premium connector. Is there any other way to send a very large array from Power Automate back to Powerapps? By way of background to the problem I am trying to solve as you may have a better idea. I have a list in sharepoint which lists inventory items. There are more than 5000 inventory items. A 'DRI' (Directory Responsible Individual) is assigned, via a People sharepoint column, to look after each inventory item. An individual may look after more than one inventory item. In Powerapps I have create a drop down to show the DisplayName of the DRI. On selecting the DRI a gallery would be updated to show only the inventory items the selected DRI is responsible for. First, I tried using a Distinct in Powerapps but in only works on the first 2000 items in the sharepoint list. So I thought I could use the Flow you described here to send a array back to Powerpps. Within the Flow I have used Union it create a Distinct list of DRIs. I tried using 'Send an HTTP' to create the consolidated list, however that returned the DRI 'ID' and not their display name. Therefore then used Get Items, which brought back the DisplayName, and went through the same process. However I am now stuck, as I do not have access to the action Response. I can pass the result back to Powerapps all in one text sting, which would then need to be Parsed in Powerapps, which I do not think is possible. Appreciate any thoughts.

[@PaulieM]

Hello, the response action is not premium when returning data to PowerApps. Just try it - you will see that it works fine.

[@ImranKhan-cd1kv]

How to update records back to SharePoint from the collection.

[@pratik903]

Hi Paul I have to import data which has more than 5k records but from csv to list. How do I implement using above steps?

[@PaulieM]

Check this video, it’s the easiest and fastest way: kzbin.info/www/bejne/aZ7MiolpjcqFrNE

[@pratik903]

@@PaulieM You are amazing. This can solve so many problems. There would be no performance issue and no row count limitations. Unfortunately my organization won't approve to get the paid version. Any suggestion to do it without paid connector please?

[@PaulieM]

@@pratik903 I just took a look at the video, there is nothing in there which requires payment?

[@pratik903]

@@PaulieM I am referring to the link you have shared which requires parse csv to json connector. (kzbin.info/www/bejne/aZ7MiolpjcqFrNE) Connector is not available to download and it seems it is a paid version. Please correct me if I am wrong.

[@PaulieM]

@@pratik903 ah ok. Well, you can use any of my Parse CSV videos to convert the CSV to JSON. Then either use an apply to each to insert the records or use my batch method. All are free.

[@jksureshkumar]

Excellent

[@rhydonplay9190]

Can we get rid of the Response final action? It is Premium and unfortunately I cannot use it, I'll appreciate a response

[@PaulieM]

Response isn’t premium when responding to a Power App.

[@csponge1]

Great video! Question, in querySettings why is it that some of my fields fail the flow test? Example, one field name is Case_ID and another is Current Buyer. Those two specifically cause the flow to fail the test. I imagine it is due to the _ and the space (blank) in the other name. How do I get those fields added in querySettings. I appreciate any input :)

[@PaulieM]

I think Current Buyer would be Current_x0020_Buyer perhaps. Case_ID should be ok though. Have you checked the internal names on the list?

[@csponge1]

@@PaulieM Yes. The exact field names are Case_ID and Current Buyer. They just won’t pass the flow. What does pass is if I remove them such as: { "listName": "tblStatus", "fields": "ID,Description,PCO,Section,CAGE,Contractor" }

[@csponge1]

@@PaulieM Current_x0020_Buyer worked... but Case_ID continues to fail. Doesn't make sense to me Lol. It's a single line of text field.

[@PaulieM]

@@csponge1 have you double checked the internal field name in the list settings? I bet it is that.

[@PaulieM]

You have just inspired me to write a new blog post :D

[@garciakamunga7878]

I am new to power automate and I have a project that I am having trouble writing the logic. I used HTTP connector to pull the data from a website using their API and saved the data in a file. they have over 20,000 reviews, but my flow is only pulling the 5000 recent reviews. How do I write my flow where it grabs pass and future reviews?

[@PaulieM]

I expect they are only giving you 5,000 in the JSON they are sending to you and you need to paginate to the next batch of 5,000. Can you share the URL?

[@BorisDK1]

@@PaulieM Hi Paul, I have the same issue as Garcia. Now with your solution I have the option to parse all the 11K records in JSON file and save it as backup of the SharePoint list into the folder in the SP. I created Excel Power query parser for the JSON files so they can be in tabular form in case someone needs to read the backup files. THANK YOU!!! I was wondering if it is possible to have the export in CSV format as it is easier to read (with Excel) and also smaller file size. Could your code be modified to export CSV instead of JSON? Thank you! :)

[@PaulieM]

@@BorisDK1 yes, you can do this easily. Once you have compiled the JSON you can use a select action to create a CSV with a concat expression. But even easier would be to export it directly to an excel file. If you look at this video I did you will be able to do it very easily: kzbin.info/www/bejne/d3KQdWeinM-FadE

[@BorisDK1]

@@PaulieM That was FAST reply! You are great! I will look at it. Hope I will understand it as I am not that good, but really appreciate your quick response! You are amazing! I am staring right away and will reply back with results. Thanks again!

[@PaulieM]

@@BorisDK1 I don’t think you will have any problems. It’s quite an easy (but new method)

[@hiro1943]

Very nice, but the html of the linked blog page has collapsed.

[@PaulieM]

It has - I should have it fixed today

[@PaulieM]

Fixed now.😀

[@stuartsmith801]

Hi Paul, i get the below error on the "Response" item... InvalidTemplate. Unable to process template language expressions in action 'Response' inputs at line '1' and column '2528': 'The template language function 'union' expects either a comma separated list of arrays or a comma separated list of objects as its parameters. The function was invoked with '1' parameter(s). Please see aka.ms/logicexpressions#union for usage details.'. Any ideas and thanks in advance.

[@PaulieM]

Yes, you cannot invoke Union with only a single parameter. You need to union all the threads together, something like this: union( outputs('querySharepoint')[0]['body']['value'], outputs('querySharepoint')[1]['body']['value'], outputs('querySharepoint')[2]['body']['value'], outputs('querySharepoint')[3]['body']['value'] ) Depending on how many threads you have.

[@niravbamrotiya6981]

how to get 10K items into excel from sharepoint list

[@onevision6706]

how to get more than 5000 items from excel with powerautomate

[@VinhLangCo]

how to get information of column with type Person or Group on sharepoint list???? please help me... I tried to get information of column "Requester" , I cannot...how to get it? @paulie M