How to get more than 100 items using the Get items action in Power Automate?

Are you wondering why the Get Items action is not returning all the list items from the SharePoint list? This Power Automate tutorial will help you to get more than 100 items using the Get items and different ways to achieve them using Microsoft Flow. Also, we will cover, how to increase the list rows present in a table action limit using Power Automate.

I also faced the same issue and solved it. You can follow the same steps, and I’m sure you will solve it.

Scenario:

The Flow triggers manually; by using the get items action, it retrieves information from the SharePoint list in two different ways( Top count and Pagination-> Threshold).

Before that, you have to know, by default, that the SharePoint Get items action retrieves only 100 items from the SharePoint list using Power Automate flow.

For example, The SharePoint list consists of 300 items, and the get item action retrieves data for the first 100 list items only, it will not retrieve for another 200 list items presented in the SharePoint list. We will see how to overcome this.

Get more than 100 items using the Get items action in Power Automate

Let us see how to get more than 100 items in SharePoint using the get items action in Power Automate

Here, I am using the below SharePoint list called Financial Data, consisting of 700 list items. So when I use the get item action, it will return only the first 100 list items from the SharePoint list by default.

To get all the list items from the SharePoint list, follow the different ways explained below:

First way (Top Count)

Log in to Microsoft Power Automate flow, select +create -> Instant cloud flow, Enter the flow name, choose the trigger to manually trigger a flow, and click the create button.

• Select + new step and Get items action, Configure the SharePoint site address and list name. Expand the get items trigger action, and set the Top count value as 1000. So, this will return the first 1000 items that are presented in the SharePoint list.
• Click Save and Run the flow by selecting the run flow option.

• Once the flow runs successfully, it retrieves all 700 SharePoint list items from my SharePoint list(Financial Data).
• In the Outputs section, select the Click to Download option to view all the 700 list items retrieved from the SharePoint list using the get items action in JSON format.

Second way (Turn on Pagination)

• Similarly, another way is to turn on pagination and set the Threshold.
• For that, Select the Get items action – > click on ellipsis -> Settings as below:

• Turn on the pagination toggle control and set the threshold value as highlighted below: Here, I have set the threshold limit as 3000, scroll down and click Done.
• Save and Run the flow.

• It retrieves all the 700 list items from the SharePoint list(Financial Data), once the flow runs successfully like below:
• select the Click to Download option presented in the Output section to view all the 700 list items retrieved from the SharePoint list using the get items action in JSON format.

This is how to get more than 100 items in SharePoint using the get items action in Power Automate flow.

Power Automate list rows present in a table limit

Let us see how we can increase the list rows present in a table action limit using the Power Automate flow

By default, list rows present in a table action return 256 rows from the Excel table. To set the table limit, we set a threshold value based on the rows in the Excel workbook.

Steps to increase table limit for list rows present in a table action

Select +create -> Instant cloud flow, Enter the flow name, choose the trigger as manually trigger a flow, and click on the create button.

• Select + new step and list rows present in a table action, Provide the below required parameters.
  • Location – Select the SharePoint site address where the Excel file exists
  • Document Library – Select the document library from the drop-down
  • File – Select the Excel file for which you want to increase the table limit
  • Table – Choose the table from the dropdown

Select the list rows present in a table action – > click on ellipsis -> Settings as below:

• Turn on the pagination toggle control and set the threshold value as highlighted below. Here, I have set the threshold limit as 1500, Scroll down and click Done.
• Click Save and Run the flow by selecting the run flow option.

• It returns all the 700 list items from the document library Excel workbook(Financials table), once the flow runs successfully like below:
• Select the Click to Download option presented in the Output section to view all the 700 list items retrieved from the Excel workbook table using the list rows present in a table action in JSON format.

This is how to increase the list rows present in a table action limit using the Power Automate flow.

Conclusion

In this Power Automate tutorial, we have learned how to get more than 100 items using the Get items and different ways to achieve them using Microsoft Flow. It also covered, how to increase the list rows present in a table action limit using Flow.

You may also like the following tutorials:

• How to get SharePoint list items using Rest API in Microsoft Power Automate
• Create an Excel file from SharePoint list items using Power Automate
• Create an Array from SharePoint list Items using Power Automate
• Get Attachments from a SharePoint list item using Power Automate
• Run a Query against a Dataset Using Power Automate
• How to Convert String to GUID in Power Automate?