This post describes how to use the SharePoint API in Microsoft Power Automate to retrieve over 5,000 items from a SharePoint list. It is the second part of a series of blog posts which covers this topic in detail. Please read this post to get an overall picture of the objective and to download my example data and exported flows:
The many ways to retrieve more than 5,000 items from a SharePoint list using Power Automate
The SharePoint API can be a better option than the “get items” action, because it executes much faster. This post builds on the flow created in the previous post which you should read first:
Get more than 5000 items from SharePoint with Power Automate
The aim is create a simple modification to the flow created in the previous post, to improve performance. The previous flow works fine, but it’s slow. If you need to return data to an interactive process, the execution time may be unacceptable.
In the previously created flow the data from the “Get Items” action was passed into the “Select” action like this:
This method works, but uses “Get Items” to retrieve data from SharePoint and then “Select” action to reshape it. Both actions can be replaced with a single request, like this:
By requesting only the required columns, the need to reshape the data with the select action can be removed. The Uri code above is:
_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand&$filter=ID gt @{variables('intID')}&$top=5000
Using Power Automate, with my sample data, the “Get Items from SharePoint” took 12 seconds to complete the request to SharePoint, whereas the API method took just 1 second:
It’s simple to replace the “Get Items” action with the API action and the execution time is greatly improved. You can also specify any odata filter query that you like, to speed up the action by reducing the results further.
Reduce the metadata output
It’s really important when using the SharePoint API to reduce the metadata output for the fastest possible results. As show in the image above, be sure to include the following in the request header:
application/json; odata=nometadata
This reduces the output payload from SharePoint to contain only the list item data. There is a good explanation of all the options on this Microsoft Blog.
Summary
Getting data from SharePoint lists using the SharePoint API is easy and can provide a significant performance increase over the standard “Get Items” action in Microsoft Power Automate.
It provides the basis for the next post in this series which details how to use parallel execution actions in Flow to get the data even faster:
Combine SharePoint API & Flow Parallel branches for Performance
Herby says
Hello Paulie,
can I also use it to select only the ‘Attachment’ column ?
I always get the error message: The expression ‘Attachment’ is invalid.
Linnet says
Hello,
Can i know how can we add multivalue person colum in the select option of HTTP request to SharePoint, As when i add the column name it gives me an error code as 400.
Thanks in advance
Jericho Rosales says
Hi there.
This is very helpful and i was able to try this and its working properly the only issue i had is when the User field counts in Sharepoint list is >5000, the flow is on pause (retries) and it keeps like that for more than an hour. Not sure what the issue was. But if the User is < 5000 it works fine. Here is my code in Uri.
_api/Web/Lists/GetByTitle('tblProductReturn')/Items?$select=ID,Title,User&$filter=ID gt @{variables('intID')} and User eq 'Jennifer'&$top=5000
User "Jennnifer" count is 2500.
User "Osvaldo" count is 7000.
If i change the "User eq 'Osvaldo' the flow will keep retries and no action for hours. Initially my goal is to get a count of how may records i had based on condition above. Unfortunately i was not able to achieve it. But the solution is very helpful.
Rakesh says
Hi Paul,
I am also facing same issue as Jericho Rosales , flow halts if we use any other filter.