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']}')/Items?$select=@{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
Jared F says
This is a great solution! I have it working great with three iterations but I need somewhere around 20. I have added them to the Threads step successfully and it still runs great, but I get an error when trying to add more iterations to the unions step in the Response step. All it says is that the expression is invalid and it happens anytime I change the expression at all. Any suggestions? I even tried only four and it throws the same error:
union(
outputs(‘querySharepoint’)[0][‘body’][‘value’],
outputs(‘querySharepoint’)[1][‘body’][‘value’],
outputs(‘querySharepoint’)[2][‘body’][‘value’],
outputs(‘querySharepoint’)[3][‘body’][‘value’],
outputs(‘querySharepoint’)[4][‘body’][‘value’]
)
The expression is invalid.
Paulie says
Hi Jared,
Your expression is invalid. On the final line you have your quotation marks incorrect (if you have copied and pasted). You have backticks instead of single quotes right after the word querySharepoint and surrounding the word body. A correct version of your expression would be:
union(outputs('querySharepoint')[0]['body']['value'],
outputs('querySharepoint')[1]['body']['value'],
outputs('querySharepoint')[2]['body']['value'],
outputs('querySharepoint')[3]['body']['value'],
outputs('querySharepoint')[4]['body']['value']
)
Jared F says
Thanks Paulie! This must be because I copied and pasted from Chrome to text editor on a Mac. I did the same copy/paste into a notepad on Windows and it worked great.
Awesome flow, thanks for sharing!
Paulie says
Stuart,
Just did a post on how to create items using a batch method. Patching will be next up.
Paul
Stuart says
Thanks, and appreciate you following up on this ?
Dorinda Reyes says
I am having an issue with this as when I look in my sample it is showing the correct information but when I put the table to see the output I am only getting true and not the output of my 4800 items from SharePoint.
I need the VendorName from the SharePoint list. Here is what I have in the code
ResponseBodyJSON Schema
{
“type”: “array”,
“items”: {
“type”: “object”,
“properties”: {
“Id”: {
“type”: “integer”
},
“VendorName”: {
“type”: “string”
}
}}}
and your regular Union Code
union(
outputs(‘querySharepoint’)[0][‘body’][‘value’],
outputs(‘querySharepoint’)[1][‘body’][‘value’],
outputs(‘querySharepoint’)[2][‘body’][‘value’],
outputs(‘querySharepoint’)[3][‘body’][‘value’]
)
How do i get the
Paulie says
Did you update the json schema in the response action?
Rheda says
Awesome PowerAutomate flow. 🙂
Wondering how I could reference a peoplefinder colunm on the “querySettings” box in the beginning?
The problem is that when I include the peoplefinder field the flow doesn’t run.
Paulie says
Hi Rheda,
Assuming your person column was called Person you would modify the query settings to something like this:
Person/EMail,Person/FirstName,Person/LastName,Person/EMail&$expand=Person
Give it a try and let me know how it goes.
Suci says
Hi Paul I have an error on the “Each Thread – querySharepoint” step. Can you help me?
{“odata.error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”en-US”,”value”:”The expression \”ID,Equipment No,PIC,Functional Location,Parking Location\” is not valid.”}}}
i dont know whats wrong with my flow i just following your intruction please help me if any idea,
{ “listName”: “Stock”,
“fields”: “ID,Equipment No,PIC,Functional Location,Parking Location” }
look forward to hearing from you soon..Thankyou..
Paulie says
The problem is your Functional Location field. You cannot have spaces in a field name, you must use the internal field name.
Stuart says
Hi Paul, did you ever create a post about patching this data back to the original SP list? Have checked your blog and cant see anything. Thanks in advance, Stu
Paulie says
I haven’t done yet, will try to do so soon, but got a lot on at the moment – Sorry 🙁
Stuart says
That’s cool. I appreciate the time you put into these posts.
Patricia Sparkman says
Paul – thank you so much for posting this! We just came upon the need to implement a solution for large SharePoint lists and your solution hit the mark!
I am struggling with one last thing and was hoping you may have come across this and can offer some guidance.
One of my SharePoint columns is a multi-select field and when I enter the field name in the querySettings and run the flow, it fails during the querySharePoint step and gives the following error:
{“odata.error”:{“code”:”-1, Microsoft.SharePoint.SPException”,”message”:{“lang”:”en-US”,”value”:”The query to field ‘AssignedTo’ is not valid. The $select query string must specify the target fields and the $expand query string must contains AssignedTo.”}}}
Any help or insight is greatly appreciated!
Paul says
Paulie,
I am hoping that this flow can allow me to use the “in” function on a collection that is non-delegable in a SharePoint list.
I the flow got hung up on the querySharepoint action.
I get the following error.
“odata.error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”en-US”,”value”:”The expression \”ID ge add(body(‘getLowestID’)?[‘value’][0][‘ID’], mul(outputs(‘itemsPerThread’), item())) and ID le add(body(‘getLowestID’)?[‘value’][0][‘ID’], mul(outputs(‘itemsPerThread’), add(item(), 1))\” is not valid.”}}}
Any ideas?
Thanks,
Paul
Alberto Pinto says
Hi Paul,
Any luck on putting something together on updating this data back into the SP List?
Looking forward to hearing from you.
Thanks,
Albert0
Devan says
Hi Paul, I am stuck on the over5000v7 flow. I am getting results in the “each thread” step but now I want to write the two datasets returned from the “querySharePoint” action into a single csv. I keep on getting only the first result set in my CSV and have tried doing an apply to each outside of the “each thread” to select the datasets and then appending to an array but end up with an error or again only getting the first data set. I am new to flow and this has me stumped. Are you able to give some guidelines or provide a link to an article that could point me in the right direction?
Daniel says
Hi Paul,
How do you go about setting up the number of threads when you don’t know how many you are going to need? After applying filters (so that you are getting the data you need) it not easy to tell if today you are going to be getting 50K itens or just 10K items.
Any suggestions?
Cheers
Manuel says
Any suggestions here: {“odata.error”:{“code”:”-1, Microsoft.SharePoint.SPException”,”message”:{“lang”:”en-US”,”value”:”The query to field ‘SUPERVISOR’ is not valid. The $select query string must specify the target fields and the $expand query string must contains SUPERVISOR.”}}}
clientRequestId: 8de04cb8-0a6a-435f-b0d3-07b8774f28a2
serviceRequestId: ca8aff9f-e0a3-1000-1a33-c45df57f5a01
the data I want to retrieve is a person field and the error appear on “each thread – querySharepoint”
Devan says
Hi Manuel,
I had the same problem. I managed to get it working for me by adding an expand option in the querySettings. i.e. “expand”: “PeopleField,PeopleField2”. In the querySharepoint action I changed the Uri to the below
_api/Web/Lists/GetByTitle(‘@{outputs(‘querySettings’)[‘listName’]}’)/Items?$select=@{outputs(‘querySettings’)[‘fields’]}&$expand=@{outputs(‘querySettings’)[‘expand’]}&$filter=ID ge @{outputs(‘lowID’)} and ID le @{outputs(‘highID’)}&$top=5000
Jairo Sánchez says
Hi Paul,
You’re great, honestly!
I read the comments and as you mentioned I tried to use the Functional Location field, but still encounters the below on the “each thread” “query SharePoint”, any chance you can help me out :
Here is my list:
“listName”: “AE Data”,
“fields”: “First_x0020_Name,Last_x0020_Name,Email_x0020_Address,Identification_x0020_of_x0020_Tier_x0020_of_x0020_support”
the error I’m getting:
{“odata.error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”en-US”,”value”:”The expression \”id ge 1 and id le3458\” is not valid.”}}}
Narmer says
Hello Paul ,
im getting this error in the Response step
ActionResponseSkipped. The execution of template action ‘Response’ is skipped: the client application is not waiting for a response from service.
any help !!
Achal Desai says
Hi @Paul,
This is brilliant!!!
I have been trying to find a solution for the for months now!
The flow works perfectly. Although I am having a problem at the last stage.
After running the flow from powerapps, I am getting columns from your original query (“Id”, “Title”, “EmpNo”, “Last”, “Furloughed”, “Telephone”, “Email”, “Brand”, “ID”). The response in the flow output is correct. It’s just when getting it in powerapps something goes wrong.
I have changed the schema in the response step and my schema is as below.
{
“type”: “array”,
“items”: {
“type”: “object”,
“properties”: {
“Id”: {
“type”: “integer”
},
“EshopPackageReference”: {
“type”: “string”
},
“CarrierReference”: {
“type”: “string”
},
“BrandPackageReference”: {
“type”: “string”
},
“BrandOrderReference”: {
“type”: “string”
},
“CarrierName”: {
“type”: “string”
},
“OperationStatus”: {
“type”: “string”
},
“Reason”: {
“type”: “string”
},
“CarrierStatus”: {
“type”: “string”
},
“ID”: {
“type”: “integer”
},
“Retailer”: {
“type”: “string”
},
“FinanceStatus”: {
“type”: “string”
}
},
“required”: [
“Id”,
“EshopPackageReference”,
“CarrierReference”,
“BrandPackageReference”,
“BrandOrderReference”,
“CarrierName”,
“OperationStatus”,
“Reason”,
“CarrierStatus”,
“ID”,
“Retailer”,
“FinanceStatus”
]
}
}
Iain Hamilton says
Hi Paul,
Thanks for this, it looks exactly what I need.
I’m having an issue with the ‘querySharepoint’ block though.
When I run it, I get an ‘HTTP Error 400. The request is badly formed.’.
I’ve had to recreate your flow as I am unable to import third party flows into my environment.
Any ideas?
Thanks,
Iain.
Jeremy says
Thanks so much for this! For the question on whether this works with document libraries, it DOES. There are a couple issues. Get high item and low item ID doesn’t work properly (which I haven’t attempted to fix yet and just manually put in ID numbers after exporting the library to excel). Also, when returning results, you have to replace [‘body’][‘value’] with [‘body’][‘d’][‘results’].
Jeremy says
Oops, I had a typo “notmetadata” vs “nometadata” in the query, [‘value’] works when you don’t have typo’s!
Jan says
Hi, it looks like it won’t work if the record ID doesn’t go through continuously.
I have a list of about 25,000 records. The first ID = 57890, the last ID = 228486. I should now loop at least 228486/5000 = 46 times.
Dave says
Hello Paul. Excellent blog thank you.
I am not able to query more than 40,000 items in a list as I receive the error “Request to the backend service timed out”, have you experienced this before or know of a solution?
Thanks
DAVID GHAVBAN says
Thanks Paul for this video and the information provided, I tried testing my flow but was asked to SendanHTTPrequestSharePoint_uri as it stated this is required, how do I get this ?
Suraj says
If highest ID is not equal to no of items in that list (deleted records) need to tweak formulas.
Jay says
Hi Paul,
Please help me with this error:
{“odata.error”:{“code”:”-1, Microsoft.SharePoint.SPException”,”message”:{“lang”:”en-US”,”value”:”The query to field ‘Logged_x0020_By’ is not valid. The $select query string must specify the target fields and the $expand query string must contains Logged_x0020_By.”}}}
clientRequestId: 2053059b-f66b-427d-b4da-3117e74261a7
serviceRequestId: 7893baa0-c050-3000-984f-447021bfb853
I used my list’s internal field names and still get that error.
Also I only use the “0” in the threads since I only have 4000 rows of data.
Angie Yang says
Hi Paulie – Can you create a similar solution but for reading Excel Online files instead of SP Lists?
Barbara Rosorani says
Thanks so much, it worked perfectly for me and I was looking for this for a long long time. Thank you