• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

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

April 19, 2020 by Paulie 2 Comments

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:

Image of the Power Automate Get Items from SharePoint Action being reshaped by the Select action

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:

Image of the Power Automate action "Send an HTTP Request to SharePoint" accessing the GetByTitle method

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:

Image showing how fast a SharePoint API request is compared to the Get Items action in Microsoft Power Automate.

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

Filed Under: Power Platform Tagged With: Power Automate, PowerApps, SharePoint Online

Reader Interactions

Comments

  1. Herby says

    May 14, 2021 at 7:36 pm

    Hello Paulie,
    can I also use it to select only the ‘Attachment’ column ?
    I always get the error message: The expression ‘Attachment’ is invalid.

  2. Linnet says

    December 2, 2021 at 5:37 am

    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

Leave a Reply Cancel reply

Primary Sidebar

Link to my LinkedIn Profile
Buy me a coffee
Image link to all Power Automate content

Excellent Power Automate Blogs

  • Damien Bird
  • Dennis (Expiscornovus)
  • Tom Riha

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 254 other subscribers.

Go to mobile version