• 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

Solutions to the SharePoint 5000 item limit using Power Automate

April 19, 2020 by Paulie 16 Comments

If you need to get more than 5000 items from a SharePoint list, there are a number of different approaches. I’ve written up three different ways to solve the problem, which return exactly the same result, but have very different execution times. To get an overall understanding of this problem, check out the YouTube video I produced:

My requirement is to return 17,000 SharePoint list items to a PowerApp. I used Power Automate to get the data but found the standard “Get Items” action to be too slow. A good user experience is essential for application acceptance.

Eventually, I was able to reduce the amount of time that the Flow takes to retrieve the items from 2 minutes down to 8 seconds! That’s 13 times faster!

Since I originally wrote this post, I have figured out a method that is even easier and faster! Check it out here.

Links to my sample data and all the flows created are at the end of the post.

My example data looks like this:

Image showing data from SharePoint list which is going to be used as a DataSource for a flow

It’s 7 columns wide, 17,000 rows of data. Here is how long it took Power Automate to execute the flow with execute each method:

Method Time to Execute
Standard “Get Items” 111 Seconds
Standard “Get Items” with Select Action 67 Seconds
Retreiving same data with SharePoint API 31 Seconds
Retreiving same data with SharePoint API and two parralel queries 19 Seconds
Retreiving same data with SharePoint API and four parralel queries 13 Seconds
Same as above with optimised flow 8 Seconds

The table shows the results from six variations on three different themes. The fundamental principal behind each method is a “Do until” loop which collects the entire contents of a SharePoint list into an array. Each method has some pro’s and cons.

Which method to use?

The right method to use, depends on your circumstances. Here are some suggestions on what method may work best for you:

  • If your flow is not time sensitive, just use the standard “Get Items” action with a “Do Until” Loop in Power Automate. The flow is simple and easy to build.
    Get more than 5000 items from SharePoint with Power Automate
  • If you are already using the “Get Items” method but need it to be a bit faster, enhance the speed of it by adding the “Select” action.
    Get more than 5000 items from SharePoint with Power Automate
  • If the performance gained from the “Select” action still does not meet your requirements, switch to using the SharePoint API. It is almost the same as using “Get Items”, but drastically faster.
    Use the REST API in Flow to get over 5000 items from SharePoint
  • Finally, using parallel execution to retrieve chunks of SharePoint list data simultaneously increases speed further. But it does make the flow more complicated.
    Combine SharePoint API & Flow Parallel branches for Performance

Why is the SharePoint Get Items action so slow?

The “Get Items” from SharePoint in Power Automate is very slow. The primary reason is the size of the payload that it returns from SharePoint. In addition to returning the columns defined in your SharePoint view, it also returns many other columns and metadata.

A single row of the sample data above results in this from SharePoint:

{
	"@odata.etag": "\"1\"",
	"ItemInternalId": "1",
	"ID": 1,
	"Title": "Terry",
	"EmpNo": 1.0,
	"Last": "Carter",
	"Furloughed": 1.0,
	"Telephone": "(462) 723-7695",
	"Email": "[email protected]",
	"Brand": "PowerBI",
	"{Identifier}": "Lists%252f5000%2bItem%2bTest1%252f1_.000",
	"{IsFolder}": false,
	"{Thumbnail}": {
		"Large": null,
		"Medium": null,
		"Small": null
	},
	"{Link}": "https://accendo1.sharepoint.com/sites/DocumentManagementTest/.....",
	"{Name}": "Terry",
	"{FilenameWithExtension}": "Terry",
	"{Path}": "Lists/5000 Item Test1/",
	"{FullPath}": "Lists/5000 Item Test1/1_.000",
	"{HasAttachments}": false,
	"{VersionNumber}": "1.0"
}

That’s a huge amount of extra fields, which results in a massive payload. 5,000 rows of my sample data created a JSON response which was 3,545 KB.

The same 5,000 rows of data when passed through the “Select” action produces a JSON response which looks like this:

{
	"Id": 1,
	"Title": "Terry",
	"EmpNo": 1.0,
	"Last": "Carter",
	"Furloughed": 1.0,
	"Telephone": "(462) 723-7695",
	"Email": "[email protected]",
	"Brand": "PowerBI",
	"ID": 1
}

For the same 5,000 records, the JSON response in flow was only 772 KB – A massive reduction. So passing the response from Get Items through the select action can make a big difference to how quickly your flow will run, and it is super easy to do.

Image of output of Sharepoint Get Items action in Power Automate being reduced down with the select action.

Use the Sharepoint API for maximum speed

Although the “Select” action in Power Automate described above improves the speed of a flow within a do…until loop, the “Get Items” action is still retrieving a lot of unwanted data from SharePoint.

Using the SharePoint API allows you to get exactly the data you want without retrieving any unwanted columns or metadata.

Fortunately, this is very easy to do using the SharePoint API using the “Send an HTTP request to SharePoint” action. Compare these two flow actions:

Standard “Get-Items” action.
The same request using the SharePoint API

These two actions, essentially perform the same operation, but the second one returns only the requested columns. Here is the difference in execution time:

Image displaying the difference in execution time between Power Automate Get Items from SharePoint Action and the Same request to the SharePoint API.

Use Parallel Branching to get batches of 5000 items simultaneously.

Using the SharePoint API to get list data into Power Automate makes a huge difference to the speed of the operation. But it still gets the data in batches of 5000 blocks. With my 17,000 row sample the do-until loop has to execute four times to collect all of the data, all of which takes valuable time.

By using the parallel branch actions in Microsoft Power Automate we can engineer a solution which works much faster.

I tried collecting SharePoint data with two and four parallel branches. With four, I was able to get all 17,000 items in 8 seconds. The downside is that the flow is more complicated.

Conclusion

The standard Get Items action in Power Automate is fine for basic operations, but if you need more control, or speed, then there are better options.

I’m happy with the performance improvements I managed to create. Retrieving data from SharePoint is key to many applications and SharePoint handles the simultaneous requests well.

Example Data and Flows

You can download both my sample data and the flows discussed in these posts from here:

  • Test Data to upload to SharePoint list
  • v1 – Using the Get Items Action
  • v2 – Using the Get Items Action with Select action
  • v3 – Using SharePoint API to retrieve items
  • v4 – Using SharePoint API and Two Parallel Execution Branches
  • v6 – Using SharePoint API and Four Parallel Execution Branches

Adapting the flow to return SharePoint data to a jQuery grid

In the YouTube video above I also provided a demo of a jQuery grid that drew data from the SharePoint list using Power Automate, you can view that demo here:

Demo of jQuery based grid retrieving SharePoint data via Flow

You can easily copy the source code from that page, the flow has been left unsecured so that you can try it out.

Related

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

Reader Interactions

Comments

  1. Noor says

    July 12, 2020 at 8:15 pm

    Hi

    can you please share the flow that was successfully optimized and completed within 8 sec
    “Same as above with optimised flow 8 Seconds”

    Thank you sooo much

    Really appreciated.
    Best regards

  2. Paulie says

    July 13, 2020 at 10:17 am

    It’s already in the post section titled “Example Data and Flows” and it’s called “v6 – Using SharePoint API and Four Parallel Execution Branches”

  3. Ed says

    July 17, 2020 at 3:26 am

    I’m attempting to use this solution to export a SharePoint list with much more than 5000 items to a CSV file and it all works until I need to take the data returned from the flow and put it into some type of table format so i can then create a csv file on one drive.

    Any idea how to add the last bits to the end of the flow so I can return all the items to a file?

  4. Paulie says

    July 17, 2020 at 8:58 am

    I helped someone else that was trying to do the same thing and it worked fine. As there is obviously a need do you want me to publish a flow that does it?

  5. Ed says

    July 17, 2020 at 1:17 pm

    Paulie,

    That would be so great of you if you would do an write up on it.

    Many times I’ve found that there is a need to automate export of lists greater than 5000 records.

    I’m new to the Power Platform and I’ve been able to do the export to file with the 1st and 2nd flow examples but using the API gives me an error when I attempt to create the file, so any guidance you would provide on exporting this information to a csv / excel file via flow would be greatly appreciated.

  6. Srisahithi says

    August 17, 2020 at 8:43 am

    Hi Paul,
    We are trying to use Microsoft Power Automate to insert/Update items from list file to an excel file. The flow is taking long time to run. For ex, for 3000 records takes 12 hrs.
    Why the flow is slow. Is it because of Standard license. Will upgrading license increase speed ?

  7. Paulie says

    August 17, 2020 at 10:37 am

    For the purposes of clarity, are you trying to write a new Excel file or create a new one with the contents of the SharePoint data? It seems like a lot of people have this requirement and I did say before I was going to do a blog post on it (CSV/Excel). The license you are using is not the cause of your problem, if you look at the flow history you will be able to see why it is slow. I will have a look tonight and see if I can see how to achieve it.

  8. Paulie says

    August 17, 2020 at 3:23 pm

    I tried exporting the 17,000 rows of SharePoint data to a CSV file and it took 30 seconds. Excel file is more complicated so I would need more details to provide you with meaningful information.

  9. Noor says

    September 1, 2020 at 10:16 pm

    Hi can you please advice how to use the output array of 4 parallel branches to add/update list items….
    Usually we Get items > apply condition > create new item or update item in sharepoint lists
    Thanks alot

  10. KJ says

    September 4, 2020 at 1:58 pm

    Excellent way to operate on more than 5000 items in such a less time!!
    For Collecting more than 5000 items of SharePoint List to csv (ex:- 100000 in my case) , use compose as union(variables(‘arrItems’),variables(‘arrItems2’),variables(‘arrItems3’),variables(‘arrItems4’)) followed by a create csv and create a file actions.

    Hope it helps!

  11. Paulie says

    September 4, 2020 at 2:56 pm

    KJ, this is a really useful addition as many people have already asked me how to use this method to create CSV files (and I was going to do a separate post on it). So this is something that a lot of people want to do.

  12. RAJITHA POTLA says

    September 17, 2020 at 4:27 pm

    Hi, my array is not retrieved in powerapps instead it shows only value. Can you show me you binding code in powerapps

  13. Paulie says

    September 17, 2020 at 4:36 pm

    Sure, it is like this:

    ClearCollect
    (
    collectionName,'NameOfFlow'.Run()
    )

  14. RASHMI RANJAN SATAPATHY says

    September 18, 2020 at 6:23 pm

    Hello , I have a similar requirement , can you please help and guide how to bind the array from flow in power app data table. I think i am just getting true/false in power app from the flowname.Run()

  15. Pascal Garcia Esteves says

    November 10, 2020 at 4:10 pm

    Good afternoon Paul, first of all thanks for the docs and youtube explanation. I’m with problem, i import the flow and change the directory, file and items that i want to select to powerapps, but when i make all the steps in powerapps i get your headers and not the headers that i have in my file. Can you help?

    Many thanks

  16. Paulie says

    November 10, 2020 at 5:04 pm

    Hi Pascal,

    I think you just need to adjust the response body to suit your column names (you can use generate from sample and supply it with the output that you are generating). Then it should be correct.

    Paul

Leave a Reply Cancel reply

Primary Sidebar

Power Automate Support Image
Link to my LinkedIn Profile
Go to mobile version