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

Tachytelic.net

  • Sco Openserver
    • Sco Unix Support
    • SCO Openserver Installation ISOs
    • SCO Openserver Binaries
    • Add a Hard Drive to an Openserver 5 Virtual Machine
    • Install SCO Vision SQL-Retriever ODBC Driver on Windows 10
    • License Expired on Virtual SCO Openserver Installation
    • How to reset the root password on SCO Openserver 5
  • Scripting
    • PowerShell
      • Add leading zeros
      • Check if a File Exists
      • Grep with Powershell
      • Create Environment Variables
      • Test for open Ports
      • Append to a Text File
    • VBScript
      • Check if a File Exists
      • lpad and rpad functions
      • Windows Update E-Mail Notification
  • Office 365
    • Connect to Office 365 with PowerShell
    • Add or remove an email alias using Powershell
    • Change Primary email address of Active Directory user
    • How to hide an AD user from the Global Address List
    • How to hide mail contacts from the Global Address List
    • Change the primary email address for an account with PowerShell
    • Change Primary email address of an AD User
    • Grant a single user access to access to all calendars
    • Forward email to an external address using Powershell
    • Convert shared mailbox to user mailbox with Powershell
  • Get in Touch
  • About Me
    • Privacy Policy

Get more than 5000 items from SharePoint with Power Automate

April 19, 2020 by Paulie 9 Comments

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.

Image showing how to configure the get Items action to get 5000 rows from SharePoint Online

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:

Image of the Initialize Variable action in Microsoft Power Automate
  • arrItems – an array to store data from the SharePoint list
    Image of array being declared in Microsoft Power Automate
  • intID – an Integer variable to store the ID of the last collected record from SharePoint
    Image of Integer variable being declared in Microsoft Power Automate
  • boolEmpty – A Boolean that controls the do until loop based on the response from SharePoint
    Image of Boolean variable being declared in Microsoft Power Automate

Create the Loop

Create a loop with the control action “Do until”

Image of the "Do until" control action in Microsoft Power Automate

Configure the do until loop to terminate when boolEmpty equals true:

Image of a Microsoft Power Automate Do until loop executing until a boolean variable becomes 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:

Image of the select action from Microsoft Power Automate being used to trim down the output from the Sharepoint Get Items 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'))
Image of a compose action in Power Automate being used to make the results of the get items action more efficient.

Now use the Set variable action to set arrItems to the outputs of the compose action above:

Image of the set variable action in Power Automate

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:

Image of the response action in Power Automate, being used to send results back to a PowerApp

The entire flow without the select action looks like this (click to view full image!):

Image showing an entire Flow in Power Automate getting more than 5,000 records from SharePoint

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.

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

Reader Interactions

Comments

  1. JC says

    May 12, 2020 at 2:41 pm

    Thanks Paulie. Im getting an error on SetLastSpo and SetBoolempty, Could you please assist? Thanks in advance.

  2. Paulie says

    May 12, 2020 at 3:42 pm

    Can do – probably easier to do a zoom meeting or teams chat though. So I can see the flow.

  3. JC says

    May 14, 2020 at 5:23 am

    Excellent. Can i get you email so I can forward the invite?

  4. KJ says

    September 4, 2020 at 12:01 pm

    Wonderful Solution!!

  5. Scott says

    September 8, 2020 at 11:30 am

    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

  6. Paulie says

    September 8, 2020 at 4:07 pm

    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!?

  7. Scott says

    September 10, 2020 at 5:02 pm

    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?

  8. Paulie says

    September 10, 2020 at 5:51 pm

    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.

  9. Madhu Prakash says

    December 15, 2020 at 6:33 am

    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..

Leave a Reply Cancel reply

Primary Sidebar

Power Automate Support Image
Link to my LinkedIn Profile

Subscribe to Blog via Email

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

Join 107 other subscribers.

Go to mobile version