Power Automate Pagination: How to Retrieve More Than 5000 Items from a SharePoint List

One of the common scenarios that you may encounter when using Power Automate is to retrieve data from a SharePoint list and perform some actions on it. However, if your SharePoint list has more than 5000 items, you may run into some challenges and limitations. In this blog, we will explain why this happens and how to overcome it using pagination.

Why does Power Automate have a limit of 5000 items for SharePoint lists?

The reason why Power Automate has a limit of 5000 items for SharePoint lists is because of the underlying SharePoint API that Power Automate uses to connect to SharePoint. SharePoint has a feature called list view threshold, which limits the number of items that can be returned in a single query to 5000 by default. This is to prevent performance issues and ensure optimal performance for all users. If you try to query more than 5000 items from a SharePoint list, you will get an error message like this:

This error message will also appear in Power Automate if you use the Get items action to retrieve more than 5000 items from a SharePoint list. The Get items action has a property called Top Count, which specifies the maximum number of items to return. By default, this property is set to All, which means that Power Automate will try to retrieve all the items from the SharePoint list. However, if the SharePoint list has more than 5000 items, this will result in the error message shown above.

How to use pagination to retrieve more than 5000 items from a SharePoint list?

One of the solutions to retrieve more than 5000 items from a SharePoint list is to use pagination. Pagination is a technique that divides a large set of data into smaller subsets or pages, and retrieves them one by one. Power Automate has a built-in feature that supports pagination for the Get items action. To use pagination, you need to do the following steps:

  1. Open the … of the Get items action
  2. Choose Settings
  3. Toggle on Pagination
  4. Input a number as Threshold

The Pagination property enables Power Automate to retrieve more than 5000 items from a SharePoint list by using multiple requests. The Threshold property specifies the maximum number of items to retrieve in total. For example, if you set the Threshold to 10000, Power Automate will retrieve up to 10000 items from the SharePoint list, using multiple requests of 5000 items each. The Top Count property still applies to each request, so you can adjust it to a lower number to reduce the load on the SharePoint server. For example, you can set the Top Count to 100, which means that Power Automate will retrieve 100 items per request, and make 100 requests to reach the Threshold of 10000.

Here is an example of how to use pagination to retrieve more than 5000 items from a SharePoint list:

In this example, we have a SharePoint list called Products, which has more than 5000 items. We want to retrieve all the items from this list and send an email with the item title and price. To do this, we use the following steps:

  1. Create a new flow and trigger it manually
  2. Add a Get items action and select the Products list as the Site Address and List Name
  3. Open the … of the Get items action and choose Settings
  4. Toggle on Pagination and input 10000 as Threshold
  5. Set the Top Count to 100
  6. Add an Apply to each action and select the value output from the Get items action as the input
  7. Inside the Apply to each loop, add a Send an email (V2) action and configure it as follows:
    • To: Enter your email address
    • Subject: Enter Product Details
    • Body: Enter Item Title: @{items(‘Apply_to_each’)?[‘Title’]} Item Price: @{items(‘Apply_to_each’)?[‘Price’]}
  8. Save and test the flow

The flow will run and retrieve all the items from the Products list, using pagination. You will receive an email for each item, with the item title and price. Here is an example of the email:

What are the limitations and best practices of using pagination?

While pagination is a useful feature that allows you to retrieve more than 5000 items from a SharePoint list, it also has some limitations and best practices that you should be aware of. Here are some of them:

  • Pagination requires a Power Automate premium license. If you don’t have a premium license, you can only set the Threshold to a maximum of 5000, which means that you can only retrieve up to 5000 items from a SharePoint list. If you need to retrieve more than 5000 items, you will need to purchase a premium license or use another solution, such as using a filter query or a REST API.
  • Pagination may affect the performance and efficiency of your flow. If you use pagination to retrieve a large number of items from a SharePoint list, your flow may take longer to run and consume more resources. This may affect the performance and efficiency of your flow and the SharePoint server. To avoid this, you should use pagination sparingly and only when necessary. You should also optimize your flow by using a lower Top Count, filtering the data, and reducing the actions inside the loop.
  • Pagination may not return all the items from a SharePoint list. If your SharePoint list has frequent changes, such as adding, deleting, or updating items, pagination may not return all the items from the list. This is because pagination uses a token to keep track of the items that have been retrieved, and this token may become invalid or outdated if the list changes. To avoid this, you should use pagination on a SharePoint list that has minimal or no changes, or use a filter query or a REST API to retrieve the items based on a specific condition or order.

Conclusion

Power Automate pagination is a feature that allows you to retrieve more than 5000 items from a SharePoint list by using multiple requests. To use pagination, you need to enable the Pagination property and set the Threshold property for the Get items action. You also need to have a Power Automate premium license and follow some limitations and best practices to ensure the performance and accuracy of your flow. To learn more about Power Automate pagination, you can check out these resources:

Comments

Post a Comment

Popular posts from this blog

Handling Throttling in Power Automate: A Practical Guide

Introduction Throttling is a common issue that users encounter when working with Power Automate. But fret not, this blog post is designed to walk you through how to effectively manage this challenge. Understanding Throttling While I will not be delving into what Power Automate is, it's important to understand the concept of throttling in this context. Essentially, throttling happens when your flows are making requests at a rate that exceeds the limits set by the service you are interacting with. This results in the service slowing down or even stopping the flow of data, which is what we refer to as 'throttling'. You can learn more about the specific limitations that Microsoft puts in place for Power Automate here . How to Handle Throttling Now, let's delve into how you can handle throttling in Power Automate. Here are a few strategies: Use Delays: Adding delay actions between your requests can he...
Read more

How to Use Power Apps Monitor with Trace to Troubleshoot a Power App Issue

Power Apps, with its ability to seamlessly create custom business solutions, has revolutionized the way businesses operate. However, like all tools, there might be instances where an app doesn't function as expected. Enter Power Apps Monitor—a tool to save the day! Coupled with the Trace function, it becomes a formidable duo in troubleshooting issues in Power Apps. Here's how to wield this combination effectively. Power Apps Monitor: A Quick Overview Power Apps Monitor is your go-to tool for real-time insights into the workings of your app. It displays events such as network calls, variable tweaks, and control interactions, making it simpler to pinpoint where things might be going awry. Introducing Trace: Your Custom Logger The Trace function is essentially a custom messaging system that feeds into the Power Apps Monitor. By strategically placing this function in your app, you can monitor the app's flow and easily spot problematic areas. Step-by-Step Troubleshooting with Po...
Read more