The 5000 item view limit of SharePoint lists has been a stumbling block for a long time. Fortunately we can overcome the limit using Microsoft Power Automate.
This is a part of a series of blog posts which cover retrieving data from SharePoint using Power Automate to read all about the methods I have used, and the best method for you to use, read this post first:
The many ways to retrieve more than 5,000 items from a SharePoint list using Power Automate
The post above also includes links to my sample data and the exported flow.
The methods described in this post will use no premium features whatsoever. For reference my sample data consists of 17,000 records with 7 columns, and looks like this:
Get more than 5000 Items from a SharePoint List – Simple Method
Before you continue, you need to understand a couple of things:
The first thing is that the 5000 limit, cannot be overcome, that is the maximum number of records that you can retrieve in a single request from a SharePoint list using the “Get Items” action or the SharePoint Rest API.
The second thing is that the wording in the “advanced options” of the Power Automate “Get Items” action is misleading:
The default setting of the “Top Count” option says “Total number of entries to retrieve (default = all)”. To my thinking, this means it would get every item in the list! But in actual fact it retrieves just 100 records.
You can set top count to any value up to 5000, but no more. The field will accept a larger figure, but it will not retrieve more than 5000 items.
So getting 5,000 items is easy, how about the rest? To get every item in the list, a loop is required that runs until there are no more records.
Using a Do Until Loop to get all records
If performance does not matter to you, use this method. It is the easiest method to implement. If you are using this flow to return a lot of data to an interactive process, this method is terrible. Examples of interactive processes could be:
- Returning SharePoint list data to a PowerApp.
- Returning JSON data to a web request via the HTTP connector.
- Anything that has an end user waiting for a result.
If your flow is returning data to a user, consider using the SharePoint API instead, which is covered in detail in this post:
If your flow is non-interactive, then this method is fine. Here is how to do it:
Declare some variables
Use the “Initialize Variable” action to declare a bunch of variables to control your loop and store some data:
- arrItems – an array to store data from the SharePoint list
- intID – an Integer variable to store the ID of the last collected record from SharePoint
- boolEmpty – A Boolean that controls the do until loop based on the response from SharePoint
Create the Loop
Create a loop with the control action “Do until”
Configure the do until loop to terminate when boolEmpty equals true:
or edit it in advanced mode and paste the following code:
@equals(variables('boolEmpty'), true)
Now get the items from SharePoint with the “Get Items SharePoint” action and add a filter query:
ID gt @{variables('intID')}
and set the Top Count to 5000
The next step is optional, but I like to include it. The Get items action brings in a LOT of metadata fields from SharePoint, if you don’t need these fields, filter them out. Trim the list down using the Select action:
Reducing the field count is important because it:
- Significantly improves performance by reducing the amount of data.
- Removes potentially sensitive information.
- Makes the data easier to comprehend, by being relevant.
With the sample data I am using, using the Select action halved the execution time of the flow.
Next use a compose action to union the results from the output of the select action with the arrItems array:
union(variables('arrItems'), body('Select'))
Now use the Set variable action to set arrItems to the outputs of the compose action above:
Next, use the Set variable action to set the variable intID to the last item collected by the get items action. This is used as the filter for the subsequent execution of the loop.
last(body('Get_items')?['value']).ID
Finally, set the value of boolEmpty using the set variable action again:
empty(body('Get_items')?['value'])
Important: Now click the ellipsis on the Set boolEmpty action and choose “configure run after”:
The reason to allow this action to run if the previous action has failed is because a failure on the final batch of records is expected, because there will be no results.
Process your final result set
Now your do-until loop is at the end, and arrItems will contain every item in your list. You can now add any actions required to manipulate the entire array contents or send the data back to the calling process. In my example I am sending the data back to a PowerApp, so I am using the response action:
The entire flow without the select action looks like this (click to view full image!):
and here is the flow with the select action included:
This all works nicely, but if you want more speed and to simplify the flow, check out this post on using the SharePoint API in a Flow instead.
JC says
Thanks Paulie. Im getting an error on SetLastSpo and SetBoolempty, Could you please assist? Thanks in advance.
Paulie says
Can do – probably easier to do a zoom meeting or teams chat though. So I can see the flow.
JC says
Excellent. Can i get you email so I can forward the invite?
KJ says
Wonderful Solution!!
Scott says
Thanks for the step by step guide, makes it really easy to follow. Is there limit even when using this method, am getting the following error: “The variable ‘arrItems’ has size of more than ‘107463352’ bytes. This exceeded the maximum size ‘104857600’ allowed.”
Thanks
Scott
Paulie says
Hi Scott,
That is an interesting limitation that I was not aware of. The figure 107463352 bytes is exactly 100MB, which is pretty big! Your list must be huge, how many items!?
Scott says
Hi Paulie, think it is fair to say it is on the large side. There are 216,434 records in the list. I actually only require subset of this data, is there a way to filter the output from a particular column?
Paulie says
Yep – the best way would be to use an odata query when you pull the data from SharePoint, this would filter it before it gets into your flow.
Madhu Prakash says
Hello Paulie, I really impressed with your solution.. I am facing issues to deal with large excel sheet data since my sheet doesn’t have sr nos or table.. Can you give me solution which is best way to deal Large excel sheet data ( more than 1 Lakh rows ) with Power Automate.. I appreciate for your help on this..
David Chu says
Hi! How would you output your solution to an excel file that exists on Sharepoint?
lluis says
Hi ,thanks for this solution, but i have an error in Union_results_with_arrItems: The union template language function expects parameters of the same type, but has encountered different” String, Array “types.”.
In select i only have Id Integer and Sede that is a string. ,for example in Select body i have :[
{
“sede”: “BARCELONA”,
“ID”: 149
},
{
“sede”: “BARCELONA”,
“ID”: 150
},
{
“sede”: “BARCELONA”,
“ID”: 151
}…..
Do you know how i can fix it? Thanks for all
Paulie says
I suspect the problem is not with your array, but during one of the loops your get items action isn’t returning anything so an array is not being returned. Go into the run history and look at the output of Get Items action for each loop iteration, then you should find a good clue.
Lluis Serra says
Hi ,really i can not find the error, i limited Top to 1 record,, and Select just Id ,but always i recive this error: Unable to process template language expressions in action ‘Union_results_with_arrItems’ inputs at line ‘1’ and column ‘23882’: ‘Template language function ‘union’ expects parameters of same type, but found ‘String,Array’ distinct types.’..
In Select Inputs:
{
“@odata.etag”: “\”29\””,
“ItemInternalId”: “149”,
“ID”: 149,
“sede”: “BARCELONA”,
“Created”: “2020-09-29T12:48:32Z”,
… MORE FIELDS….
“Author”: {
“@odata.type”: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser”,
“Claims”: “i:0#.f|membership|[email protected]”,
“DisplayName”: “XXXXXXX”,
“Email”: “XXXXXXX”,
“Picture”: “https://XXXXXXX”,
“Department”: “XXXXXXX”,
“JobTitle”: null
},
“Author#Claims”: “i:0#.f|membership|XXXXXXX”,
“Editor”: {
“@odata.type”: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser”,
“Claims”: “i:0#.f|membership|XXXXXXX”,
“DisplayName”: “XXXXXXX”,
“Email”: “XXXXXXX”,
“Picture”: “https://XXXXXXX”,
“Department”: “XXXXXXX”,
“JobTitle”: null
},
“Editor#Claims”: “i:0#.f|membership|XXXXXXX,
“{Identifier}”: “Lists%252finventario_XXXXXXX%252f149_.000”,
“{IsFolder}”: false,
“{Thumbnail}”: {
“Large”: null,
“Medium”: null,
“Small”: null
},
“{Link}”: “https://XXXXXXX”,
“{Name}”: “”,
“{FilenameWithExtension}”: “”,
“{Path}”: “Lists/inventario_XXXXXXX/”,
“{FullPath}”: “Lists/inventario_XXXXXXX/149_.000”,
“{HasAttachments}”: false,
“{VersionNumber}”: “1.0”
}
]
Select Ouputs:
[
{
“ID”: 149
}
]
GetItems:
Filter query: ID gt @{variables(‘intID’)}
Top count: 1
Any idea?
Thanks for all your time
Paulie says
No idea, but if you fill out the contact form or connect to me on linkedin we can do a quick MS Teams session and I will have a look and see if I can help you out.
Lluis Serra says
Many thanks for all you help ! ,when you come back to spain i will pay you a beer 🙂
Duncan says
Superb, Pauli. Nowadays, my knowledge of Power Automate is still near to zero .. ¿Can you tell me (for noobs) how to export the items to a csv file in your flow? Thank you.
Paulie says
Hi Duncan, there’s a built in action to create CSV. Just pass your JSON into it and you’ll have a CSV
Duncan says
Thank you very much, Paulie for share your knowledge and help other people. Now I understand some better how Microsoft flow works and I was be able to export my SP list to CSV with your method. My next challenge is to export atachments too… ¿Any piece of advice? Regards.
Vicki Snowdon says
Hi. Could you please help me with this error when I followed your flow: “Unable to process template language expressions in action ‘setLastSpoID’ inputs at line ‘1’ and column ‘35441’: ‘The template language expression ‘last(body(‘Get_items’)?[‘Value’]).ID’ cannot be evaluated because property ‘ID’ cannot be selected. Please see https://aka.ms/logicexpressions for usage details.’.” It seems like it got stuck when there were no more records and didn’t know what to do next.
Vicki Snowdon says
All good. I had forgotten to set the “configure run after” parameter. Thank you so much for creating this blog.
Paulie says
Hi Vicki, really glad you found it useful – have you checked out the new version of this post? No need for variables and it’s a bit quicker!
Robert says
Paulie,
This is probably a really basic daft question, but how or rather what will I use to collect the data I am passing back to powerapps?
Does the WorkflowName.Run(xxx) What goes in the xxx – can I create a collection? or a table, or a variable?)
Paulie says
A collection. Something like this:
ClearCollect(
empDetails_v1,
'over5000v7'.Run()
)
Robert says
added something like in, but the collection only has one value of true – but when I look at the workflow output it has all the items in an array! Any idea what I am doing wrong?
Paulie says
Yep – you didn’t update the json schema in the response
Robert says
Paulie,
I worked it out, the JSON part was blank, so I needed to work out how to get that into the output. Its not brought all my info in (three columns and I get only one with data) – but I suspect that is a formatting thing, and in my subsequent use I probably don’t need that anyways.
Thanks for your help – I would never ever have got to your solution
Robert says
Paulie,
How would the JSON function work with an Array from a Get Rows operation from SQL?
K M says
HI Paul – Here is my scenario:
Excel List: 1762 Rows
– Column A ‘Client ID’ (no duplicates in Excel Sheet)
– Column B ‘Value’ to be uploaded
SharePoint List: 8602 Items
– Column 1 ‘Client ID’ (duplicates)
– Column 2 Destination for ‘Value’, based on value in Column 1
Goal: Update all Items in SharePoint list with ‘Value’ from Excel; So one row in Excel should update many items in SharePoint.
I have built a Flow which is updating 90% of the items in the SharePoint list, but I am getting anomalies where Sharepoint Column 2 is not updating/matching Excel Column B.
I wondered if it was a 5000 item limit issue so have employed your Do Until method – but it is still failing. Looking at it again I can see that when you go to configure run after in ‘Set boolEmpty True if no more Sharepoint Items” your variable on the left side says “SetLastSpoID” – but mine says “Set arrItems to Union Value”.
Have I done something wrong?
Any other ideas of why my flow might be glitching?
Thanks!
Ever Guzman says
Hi! Excellent solution! I’m having a hard time using it but instead of Get items with the option Get files (properties only). Do you know why keeps failing? I mean, it is not failing per se, it works perfectly with the first 5000… and in the second iteration of the loop it brings again the same first 5000 records. I’ve been stuck here for a while now, can you help me?
Michael says
This looks amazing but i’m struggling to get it to work with “List Rows in a table”. If I don’t include the filter it runs fine for 5000 records however with the filter in I get this error:
Invalid filter clause: unsupported operation. Only single ‘eq’, ‘ne’, ‘contains’, ‘startswith’ or ‘endswith’ is currently supported.
clientRequestId: e61a7edc-692a-4d31-be75-a521b80b3d0a
serviceRequestId: 9b402780-091e-45cb-8040-35324ae2f451;2680c41e-5e10-4b80-8932-79d1db1e7a1c;e897a133-2cc2-4ac5-843c-85c6ea575e2b;f5c00c4b-6aaa-493d-aad3-84bcb09021c5
Any idea?
Nishitha Nathani says
Hello Paulie, this is an incredible solution. It really helped me out! Thank you so much.
Just a quick time-sensitive question. My arrItems looks like this:
{“Email ID”:”[email protected]”,”App EAI Number”:”247″,”App Name”:”Microsoft”,”Manager Name”:”Hello”,”ID”:5,”Retain Remove”:{“@odata.type”:”#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,”Id”:0,”Value”:”Retain”}},
I would like to perform the following:
1. Filter down this list to get unique Email IDs.
2. App EAI Number/App Name.
3. Retain Remove field is null.