Use the REST API in Flow to get over 5000 items from SharePoint

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