I’ve done a series of blog posts on how to get more than 5000 items from a SharePoint list using Power Automate. While helping someone else out I figured out a way to make it much easier.
If you’re not interested in knowing how it works, just download the flow and reconfigure it for your environment.
Overview
In this example the flow is triggered by PowerApps, but the same method could be used for any trigger. Let’s take a look at the complete flow and then I will explain each step in detail:
Step by Step Explanation
Here is a explanation of every step of the flow in the order they appear in the screenshot above
- Trigger Action
Is the flow trigger action. This could be PowerApps, the HTTP connector or anything that suits your situation. - Compose Action ‘querySettings’
This is a compose action which specifies the query settings for the flow.
It has two properties:- listName – Specifies the name of the SharePoint list that you want to retrieve items from.
- fields – Is a comma separated list of fields that will be retrieved from the SharePoint list.
- Send an HTTP Request to SharePoint ‘getLowestID’
This step gets the ID of the first record in the SharePoint List. - Send an HTTP Request to SharePoint ‘getHighestID’
Gets the ID of the last record in the SharePoint List. - Compose Action ‘threads’
This flow is going to execute multiple simultaneous queries to SharePoint. Each of which will return a maximum of 5,000 records. Specify the number of threads you will need to cover the number of records that you have in total.
For example, if you have 9,000 records you will need 2 threads. If you have 23,000 you will need 5 threads. For example:[ 0, 1, 2, 3, 4 ] - Compose Action ‘itemsPerThread’
This action calculates the number of items to fetch in each thread. If you have 9,000 items it will get 4,500 items per thread. The expression is:add(div(sub(body('getHighestID')?['value'][0]['ID'],body('getLowestID')?['value'][0]['ID']), length(variables('threads'))),1 ) - Apply to each
The apply to each action is where the SharePoint queries take place. It is important to configure the concurrency options on this step to ensure it is set to at least the number of threads you have configured:
Within the apply to each the following actions take place:- Compose action ‘lowID’
Calculates the lowest ID to find in the current iteration:add(body('getLowestID')?['value'][0]['ID'], mul(outputs('itemsPerThread'), item())) - Compose action ‘highID’
Calculates the highest ID to find in the current iteration:add(body('getLowestID')?['value'][0]['ID'], mul(outputs('itemsPerThread'), add(item(), 1)) - Send an HTTP Request to SharePoint
This is where the actual SharePoint query takes place. Using the inputs from the previous steps. The expression in the Uri field is:_api/Web/Lists/GetByTitle('@{outputs('querySettings')['listName']}')/[email protected]{outputs('querySettings')['fields']}&$filter=ID ge @{outputs('lowID')} and ID le @{outputs('highID')}&$top=5000
- Compose action ‘lowID’
- Compose Action ‘Sample Output’
This action isn’t required for the flow to run. It is used to output two records from the first iteration of the SharePoint API query. This can be used to generate the required JSON Schema in the final step.take(outputs('querySharepoint')[0]['body']['value'],2) - Response Action
This is used to send the data back to PowerApps. You need to modify the Response Body JSON Schema. If you run the flow once you can use the output of the “Sample Data” step and use the “Generate from Sample” function.
The body content unions all of the data that is returned from SharePoint into a single array. This will need to be adjusted according to the number of threads that you have configured:
union(
outputs('querySharepoint')[0]['body']['value'],
outputs('querySharepoint')[1]['body']['value'],
outputs('querySharepoint')[2]['body']['value'],
outputs('querySharepoint')[3]['body']['value']
)Conclusion
This is an incredibly easy way to get more than 5000 items from a SharePoint list. It is also very fast. I was able to get 17,000 rows in 5 seconds and someone I am working with was able to get 5,000 rows in two seconds!
Download the Flow
This flow is simple enough that you don’t really need to understand how it works, you can just import it into your environment and reconfigure to your needs.
Merrian Holland says
Great explanation! Very comprehensive – thank you
Damir says
Hi Paul, thank you for a great job! Would you please provide a link to some (better official) confirmation of is Power Automate Response premium action it’s free if we are using it to respond to PowerApps?
Tom says
Great Solution and many thanks for sharing.
Very curious if you are going to work on this again like you previously did (I worked but you improved it =))
Paulie says
Thank you. I will happily work on it again if you can find any further improvements that can be made! But I’ve slimmed it right down as much as I can think of. I have used the same technique in this flow for many things in Power Automate though. Does your execution still take a long time?
Kamilo Rudeš says
Hi Paul,
Thank you so much for sharing the knowledge in the community to non-IT guys like me.
This is awesome! 🙂
I applied it for my case, but I got stuck on one thing. I’m fetching string from SharePoint and it has “-” character in the text. When checking the data in Power App part of the string after “-” is not there.
Appreciate if you can help me on this one.
Thank you in advance for the time!
Best
Kamilo
Hao, Bui says
Hello Paul,
Please help me. I download your flow. When I change Share point list, but Schema not change:
This is my “Body”
[
{
“Id”: 1,
“Title”: “DONGNAI”,
“STT”: 1,
“Item”: “GRL_B?ng keo ?en”,
“Department”: “NCF”,
“Unit”: “Cu?n”,
“Price”: 18500,
“Supplier”: “AB”,
“idStatus”: “USE”,
“Kind_Sundry”: “FIX”,
“idStatus_Use”: “LOCAL”,
“Item_Code”: “NCF00000001”,
“ID”: 1
},
{
“Id”: 2,
“Title”: “DONGNAI”,
“STT”: 2,
“Item”: “GRL_M?c in pallet lo?i nh?”,
“Department”: “NCF”,
“Unit”: “Cu?n”,
“Price”: 254500,
“Supplier”: “AB”,
“idStatus”: “USE”,
“Kind_Sundry”: “FIX”,
“idStatus_Use”: “LOCAL”,
“Item_Code”: “NCF00000002”,
“ID”: 2
},…
This is Schema:
{
“type”: “array”,
“items”: {
“type”: “object”,
“properties”: {
“Id”: {
“type”: “integer”
},
“Title”: {
“type”: “string”
},
“EmpNo”: {
“type”: “integer”
},
“Last”: {
“type”: “string”
},
“Furloughed”: {
“type”: “integer”
},
“Telephone”: {
“type”: “string”
},
“Email”: {
“type”: “string”
},
“Brand”: {
“type”: “string”
},
“ID”: {
“type”: “integer”
}
},
“required”: [
“Id”,
“Title”,
“EmpNo”,
“Last”,
“Furloughed”,
“Telephone”,
“Email”,
“Brand”,
“ID”
]
}
}
Stuart Smith says
Hi Paul, the above is/would be a god send in dealing with the inadequacies of Power Apps and large sharepoint list, but im getting an error on the “Each Thread\querySharepoint” flow step. The error says…
{“odata.error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”en-US”,”value”:”The expression \”ID, Site, Floor, RoomIDLocation, SwitchName, SwitchUnitNo., SwitchType, SwitchSerial, SwitchAssetNo., InstallDate, SwitchPortNo., FloorPortNo., EquipmentType, Comments\” is not valid.”}}}
I guess flow is getting the above from the “QuerySettings” input section…
{
“listName”: “LON01 Patch Panel Inventory”,
“fields”: “ID, Site, Floor, RoomIDLocation, SwitchName, SwitchUnitNo., SwitchType, SwitchSerial, SwitchAssetNo., InstallDate, SwitchPortNo., FloorPortNo., EquipmentType, Comments”
}
but cant figure out why its failing as still new to flow. Any help would be great.
Stuart Smith says
I figured out the issue, it was the column names, I was using the friendly names and not the actual column names and not the flow runs pass that section, but now get an error on the “Response” section…
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 https://aka.ms/logicexpressions#union for usage details.’.
Any ideas? Thanks in advance.
tachytelic says
Yes, it sounds like you have invoked Union with a single parameter, but it needs to be as per the code above and union all the threads (arrays) together. Like this:
union(
outputs(‘querySharepoint’)[0][‘body’][‘value’],
outputs(‘querySharepoint’)[1][‘body’][‘value’],
outputs(‘querySharepoint’)[2][‘body’][‘value’],
outputs(‘querySharepoint’)[3][‘body’][‘value’]
)
Stuart Smith says
Hi Paul, thanks for getting back to me and that worked. I have edited the above union code and removed outputs 1,2 & 3 as my SP List has less than 5000 rows, so was trying to speed up the import, but guess I done something wrong. Anyway, I will leave them in there to future proof the flow. Any once again, thanks and this is going to be so helpful. Think I will have to have a celebratory pint tomorrow, now that the pubs are back open.
Sue says
I’m getting the following error and no data is being pulled into my PowerApp: “over2000v7.Run failed: The data returned in the response is invalid. Response data should be a valid JSON object.” The flow runs successfully in Power Automate. What could be the problem?
Paulie says
My guess is that you haven’t setup the response body JSON schema properly. Did you copy the output from the “Sample Output” step into the “Generate from Sample” in the repsonse?
Sue says
Yes, I did copy from the Sample Output step. The resulting Schema is:
{
“type”: “array”,
“items”: {
“type”: “object”,
“properties”: {
“Title”: {
“type”: “string”
},
“FirstName”: {
“type”: “string”
},
“Alias”: {
“type”: “string”
},
“GIN”: {
“type”: “string”
}
},
“required”: [
“Title”,
“FirstName”,
“Alias”,
“GIN”
]
}
}
Russ Bell says
This looks like an impressive solution. Thanks so much for working on it and posting it!
Would this work for a SharePoint document library, as opposed to a list? I’m just trying to create a file picker in PowerApps to the user to locate and select a load file, but it chokes due to delegation problems on Filter() function. Otherwise I would not need this. I only need to allow the user to work through a couple levels of folders/subfolders for years and months and list the .TXT files in the selected subfolder for the user to pick from but there are too many files in the library as a whole, so the Filter() does not locate all the files beyond the delegation limit.
Paulie says
No idea actually, I think so, happy to help you build it and find out!
Akash says
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!
Paulie says
I actually worked on that this week, out of interest – how many items are you creating?
Akash says
Hi Paul,
I am currently seeing a volume of around 10000 items. Maybe the volume will scale up in coming weeks. So can you please help with some efficient solution like the one in this post.
Thank you!
Akash says
Hi Paul,
I am currently seeing a volume of 10000 items. Maybe the volume will scale up in coming weeks. So can you please help with some efficient solution like the one in this post.
Thank you!
James Warren says
Hi @Paulie,
As @Akash mentioned tying a rest delete into this solution would be incredible! Iterating through the list of ~50k items was very quick and efficient using the above workflow. I have been trying to delete around ~15-30k items in one shot but so far this has proven to be extremely slow and/or error prone. Even deleting 4500k items takes 30-40 minutes. This could be a fantastic youtube and blog post!
Akash says
Hi @James,
I was able to build a “batch” api call which is much faster than single api calls. For delete part its working fine. But for for create items , if I increase the batch volume to 1000 items at a time, it’s throwing time-out error for around 80 items.
I will await @Paul ‘s update as he mentioned that he worked on this type of flow few weeks back.
Stuart Smith says
Hi Paul, this works great pulling large amounts of records from an SP List to a collection to allow you to work on the records, but any advise on then patching the records back to the SP List? ideally (i guess), only this records that have been updated.
Thanks in advance,
Paulie says
Yes, I’ve got a video and post coming on it very soon.
Stuart Smith says
Cool, and thanks for a super quick response. Do we have a rough timeline? 😉
Paulie says
Should be by the end of the week. If you look at my latest post you can also figure it out from there.
Orlando Soto Batista says
Hi Paul, terrific; this is a time saver for sure.
Now, I’m trying to optimize it a little bit since I have one field that I could use for filtering purposes ; is that possible with this method?
Thanks, Orlando
Paulie says
For sure just extend the query after $filter in the URI