How to get over 100 items limit with the ‘Get items’ in Power Automate
Posted On August 8, 2021
“‘Get Items’ returns only 100 entries from all the items it should return, how can I overcome this limit and get all the relevant items with Power Automate?”
When you use the Power Automate actions designed to get data from SharePoint, e.g. ‘Get items’ or ‘Get files’, there’s also a field ‘Top Count’ under the ‘advanced options’. This field defines how many items it should return. If you want just a single item, you enter 1 in that field. If you want the last 10 items, you enter 10. And if you want all the items, you might keep the field empty based on the description: “Total number of entries to retrieve (default = all).” But the ‘all’ value doesn’t really mean ‘all’ items in the actions. If you keep the field empty, it’ll return only the first 100 items and ignore the rest.
Increase the items limit
To return all the items you must type in a number in the ‘Top Count’ field. A number between 1 and 5000 will override the ‘all’ limit of 100 items or files, and give you the corresponding number of items (if they exist).
The actual number you use is up to you. You can use 5000, the maximum, in all the actions. Since it’ll return only the existing items, it won’t slow the flow down. Or you can decrease the number of items down to 1 if you’re implementing the lookup functionality.
I think that ‘Get items’ is one of the most important actions in Power Automate, and the default limit of 100 items shouldn’t be there. Especially not with such a confusing description in the ‘Top Count’ field. It’s hard to notice this limitation right away as you probably won’t have over 100 items during the development, but it can start making problems later. Whether you build some reminder flow or report with SharePoint items, one day you might start missing items just because of this limit.