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

Tachytelic.net

  • Get in Touch
  • About Me

PowerApps

Solutions to the SharePoint 5000 item limit using Power Automate

April 19, 2020 by Paulie 46 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.

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

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

Get more than 5000 items from SharePoint with Power Automate

April 19, 2020 by Paulie 31 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

How to Print from PowerApps to on-premise printers

April 7, 2020 by Paulie Leave a Comment

PowerApps is a marvellous product, but printing support is quite weak. I am currently in the process of developing a PowerApp and printing support to on-premise printers is a must. YouTube video that demos the functionality:

PrintNode works really well with Microsoft Power Automate and Power Apps. After only a few minutes of setup, I was able to print PDF files directly from PowerApps to my on-premise printers. In my case the PDF files were stored in a SharePoint list.

The printing is performed with a HTTP interaction between Power Automate and PrintNode, so these instructions would work equally well from any flow, but my use case was PowerApps.

The high level steps are as follows:

  • Register on PrintNode and setup the client.
  • Select which Printers you want to print to and gather their unique ids.
  • Create a flow which uses the HTTP Connector to send print jobs to Printnode.

Setup PrintNode Client

After you have registered on printnode.com, install the client on a machine that has local access to the printers you want to print. Then take a note of the printers that you want to print to. Mine look like this:

From the screenshot above, you can see I have two printers enabled with the unique IDs of 69363024 and 68663025.

Setup Printer Definitions in a SharePoint list

A simple SharePoint list with four fields works well to drive a printer selection drop down in PowerApps. My list looks like this:

Image showing list of printers in SharePoint

And the resulting drop down looks like this:

Image of printer selection screen from PowerApps

Or you could set up the printer definitions directly in your PowerApp by using the Table Function. This code would produce the same result as the SharePoint list:

Table(
{printNodeID:"69363025", Description:"HP Laserjet M506", Location: "Pauls Office", Image: "https://www.tachytelic.net/wp-content/uploads/HP-LaserJet-Enterprise-M506-Printer.jpg" },
{printNodeID:"69363024", Description:"HP PageWide Pro 477DW", Location: "Merrians Office", Image: "https://www.tachytelic.net/wp-content/uploads/HP-PageWide-Pro-477dn-web.jpg" }
)

Create a flow to receive print request from PowerApps and submit to PrintNode

The flow is simple to create. Here is a high level image of my flow:

An Image showing the steps of a Flow in Microsoft Power Automate used to print from PowerApps to On-Premise printers.

Define the required variables for the Flow from PowerApps

The flow starts with the creation of the three variables that are needed to complete the flow. The three variables are

  • printNodeID
  • pdfPath
  • documentName

This step isn’t strictly required, you can simply use “Ask in PowerApps” to fill the required values further down the flow. I prefer to do it this way as it makes the flow easier to comprehend when you are calling it from PowerApps. Each variable is initialized like this:

Image of a flow variable being initialized to be a parameter for a PowerApp

Retrieve the PDF that you want to print

In my case, the document I want to print is already in PDF format, if your’s is not, then you will need to add some steps to your flow to get it into PDF format.

I am using the flow action “Get file content using path” to get the PDF file from a SharePoint list.

Compose your JSON and submit to PrintNode

The PrintNode API is extensive and I have only scratched the surface. The code I am using simply submits a PDF for printing to a particular printer. I am using the “Compose” action to create the required JSON.

{
  "printerId": "@{variables('printNodeID')}",
  "title": "@{variables('documentName')}",
  "contentType": "pdf_base64",
  "content": "@{base64(body('Get_file_content_using_path'))}",
  "source": "PowerApps"
}

Note that the PDF file needs to be Base64 encoded, otherwise the submission to PrintNode will fail.

Image of the compose action in Power Automate being used to generate JSON for submission to PrintNode

The final step is to use the HTTP action to submit the job to PrintNode:

This step is simple. You need the URI, which is: https://api.printnode.com/printjobs

The Authorization header. I used a concat expression for the value of:

concat('Basic ', base64('apiKeyFromPrintNode:'))  

and the body of the HTTP request is the output of the compose step.

Connect your new PrintNode flow to your PowerApp

The final step is to connect your PowerApp to your newly created PrintNode flow. I have connected mine to a button control which initiates the flow with the following code:

printViaPrintNode.Run(dropDownSelectPrinter.Selected.printerID, pdfURL, pdfName)

Obviously I named the flow “printViaPrintNode” and the values being passed in are associated to the variables assigned in the flow.

Image showing expression used to execute a flow from PowerApps to print a PDF to an on-premise printer.

This is the Print screen withing my PowerApp. PDF Preview on the right hand side with the printer options on the left and an image of the printer that is going to process the job.

Image of a screen from PowerApps which is used to preview a PDF document and then print it to an on-premise printer.

It’s also interesting to note that PrintNode do have a “Raw” printing option which can be used to drive Label printers, but I can think of some other uses for that option to, which I am going to play with.

So overall, although PowerApps does not have great native printing support, PrintNode goes a long way to solving the problem!

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

  • « Go to Previous Page
  • Go to page 1
  • Go to page 2

Primary Sidebar

Link to my LinkedIn Profile
Buy me a coffee

Subscribe to Blog via Email

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

Join 259 other subscribers.

Go to mobile version