PowerAutomate: Tips for working with more than 100 items in Flow

by HubSite 365 about Shane Young [MVP]

SharePoint & PowerApps MVP - SharePoint, O365, Flow, Power Apps consulting & Training

Citizen DeveloperPower AutomatePower Selection

Power Automate's "Get Items" action in SharePoint returns a maximum of 5000 items.

By default, Power Automate’s “Get Items” action in SharePoint returns a maximum of 5000 items. However, you can increase this limit by using the “Filter Query” option.

Here are the steps to follow:

In your “Get Items” action, click on “Show advanced options”.
Scroll down to “Filter Query” and enter a filter query that will limit the number of items returned. For example, you could filter by a specific column value or by date range.
Test your flow to ensure that the filter query is correctly limiting the number of items returned.
If you still need to retrieve more than 5000 items, you can use the “Top Count” option in the “Get Items” action to retrieve multiple batches of items. For example, you could retrieve the first 5000 items, then use a “Skip Count” option to retrieve the next 5000 items, and so on.
Once you have retrieved all the batches of items, you can combine them using the “Append to array variable” action.

Note that retrieving large amounts of data can affect performance and cause timeouts. It’s recommended to filter your data as much as possible and to test your flow thoroughly before running it in production.

A field called "Top Count" appears under the "advanced options" when you use Power Automate actions like "Get items" or "Get files" that are intended to retrieve data from SharePoint. The number of items it should return is defined by this field. You put 1 in that field if you only want one item. You enter 10 to get the last 10 items. Additionally, based on the description, you may choose to leave the field empty if you want every item: "Total number of entries to retrieve (default = all)." However, not all of the actions' items are actually included in the "all" value. If the field is left blank, it will only return the first 100 items and ignore the rest.

What are the 5000 Limits on SharePoint Online

The 5000 limit on SharePoint refers to the default threshold for the maximum number of items that can be displayed in a list or library view before SharePoint uses paging to display the remaining items. This limit is in place to prevent performance issues, such as slow loading times or timeouts, that can occur when working with large lists or libraries.

When a list or library contains more than 5000 items, SharePoint automatically enables server-side paging to display only a subset of the items at a time. This means that when a user navigates to a view that exceeds the 5000 limit, they will only see the first 5000 items and will need to use pagination or filtering to view the remaining items.

It is possible to increase the threshold beyond the default limit of 5000, but doing so can impact performance and should only be done after careful consideration and testing. Additionally, certain features such as lookup columns, indexing, and certain types of queries may be impacted by the threshold limit.