Table of contents
The problem
As a service protection SharePoint Online will return only a specific amount of items when using the Get items action. The default value is 100. If your list has more than 100 items, you will already need to use one of the options below.
The fixes
Before we go into the fixes: Please always make sure that you are stressing SharePoint Online only as far as you need it. Increasing the load on the system in every flow or API call, will eventually bring you into throttling issues.
Option 1: Increase top
✅ Very simple
❌ Only works for up to 5.000 items
How To | From the advanced parameters in the get items action, select the Top parameter and enter 5000 |
Explanation | In this scenario the SharePoint API returns a single list of up to 5000 items. |
Good to know | The action might take a few (milli)seconds longer to run. The maximum value for top is 5000, if you need more increase pagination. |
Option 2: Pagination + Top
✅ Very simple, works up to 5.000 items (low performance profile) and 100.000 items (all other performance profiles)
Performance profiles are set by the license of the account that runs the flow. If you are using the Power Automate for Office 365 Plan which is included in most M365 enterprise and business licenses, you can only set the pagination value to 5.000 (you can simply work with the top parameter instead). If you flow falls under another performance profile, because it is e.g. a manual or child flow, triggered by a Power App or you (e.g. Premium) or the flow (Process) are licensed additionally, then you can enter up to 100.000. Side note: Same limits apply to the max number of items that you loop in an apply to each.
Official source: https://learn.microsoft.com/en-us/power-automate/limits-and-config
❌ Only works for up to 100.000 items
How To | From the advanced parameters in the get items action, select the Top parameter and enter 5000. Then go to the setting tab of the action, enable Pagination and enter the desired number into the Threshold field. |
Explanation | In this scenario the SharePoint API will return a single list of up to 100.000 items. Each request will ask for up to 5000 items until either all items are returned or the pagination value is reached. It combines all values into one single array. If your list had 100.000 items the action would automatically run 20 times. Each time it will ask SharePoint for 5000 items until either all items are returned or the 100.000 pagination value is reached. |
Good to know | Be aware that each pagination you need/do also counts as an action run. |
Please be aware of a general response size limit. If you have many columns or use complex columns, the response from SharePoint will be larger and you will get this error. You can create a view in SharePoint and only view required columns to see if the action then works. In my case I only had 4 additional columns, one of them was a person column.
Option 3: Do Until +Pagination + Top
✅ Technically works up to the item limit of list (currently 2 million). I have tested with 100.000 items only.
❌ Needs HTTP Request for SharePoint, requires a do until loop, Person columns return the Id instead of the E-Mail
How To
In Summary: We will use the SharePoint HTTP action to get up to 5000 items from a list. The SharePoint HTTP action does not have a pagination value. Therefore, we use a Do Until action to apply the same logic manually.
Steps:
- Initialize two variables
- Name: arrItems Type: Array Value: Empty
- Name: strUri Type: String Value:
_api/web/lists('LIST ID’)/items?$top=5000(Base request uri) - Add a Do Until loop to your flow
- Loop until
- Left: strUri variable
- Operator: is equal to
- Right: empty
- Count: 30
- This depends on the number of items you want to return. Multiply this number with the top number. In my case:
5.000 (top) * 30 (count) = 150.000. So with 30 do until loops I can return up to 150.000 items. The loop will stop before if the condition is reached. For 100.000 items it will stop at 20 loops. - Timeout: PT1H
- You can increase the timeout if necessary. 1H should be more than enough.
- Add the HTTP Send an HTTP request to SharePoint
- Add your site from the dropdown or with a environment variable
- Method: GET
- Add the strUri Variable as uri
- In a loop I usually change the retry policy for this action to none for as long as I am testing. In case I made any errors there, it will not retry and I can troubleshoot faster.
Using the $filter parameter is not supported if your list is more than 5.000 items as it surpasses the list threshold. Filter MUST be done after you collect all your data.
❌ _api/web/lists('LIST ID')/items?$filter=Field1= eq ‘Test’&$top=5000
Using $select is supported and recommended to reduce the size of the response to only include necessary columns. The more columns you decide to add the more likely it is that you will reach limitations in Power Automate. Reducing the amount of returned columns can significantly reduce the run time. Extreme example: A list with 100.000 items took almost three minutes with all columns and only 1.5 minutes when returning the ID column.
✅ _api/web/lists('LIST ID')/items?$select=Field1,ID,Modified&$top=5000
- In my case I used a select action to customize the response to contain only columns that I needed. To reduce runtime include the wanted columns in the uri.
- From:
body('Send_an_HTTP_request_to_SharePoint_-_Items')?['d/results'] - Map and Value to your liking, for example. If you use select to reduce the number of columns you can also skip the select action and directly reference
body('Send_an_HTTP_request_to_SharePoint_-_Items')?['d/results']in the next action. - Add a compose action that helps you save all the items into the arrItems variable without using a loop
union(body('Select_-_beautified'),variables('arrItems'))- union simply combines to arrays together and removes duplicates. As your items at least differ by their ID, in this case, it will combine the results from the HTTP action and whats already stored inside the variable.
- Next set the new combined array in the variable using a set variable action
outputs('Compose_-_Unify_results')
Now the exciting part: The SharePoint API will, together will the item array, return a value called “__next” if the list contains more than the top value of 5.000.
- Add a condition that check if that value is present or not. If the value is present set it as your strUri variable. If not set the setUri variable to “empty”.
- In the condition I used:
Coalesce(body('Send_an_HTTP_request_to_SharePoint_-_Items')?['d/__next'],'-'). Coalesce will check for me if the value is there, otherwise it will output “-”. - If true (and coalesce result is equal to “-”)
- Set the variable strUri to the literal word “empty” (without quotation marks)
- If false
- Set the variable strUri to
body('Send_an_HTTP_request_to_SharePoint_-_Items')?['d/__next']
Option 4: RenderListDataAsStream
How does filtering affect this behavior
How does this affect filter queries? Does the behavior change?
Behavior change with a filter query | 100 or less items | 5.000 or less items | More than 5.000 items | More than 100.000 items |
Without any fixes | No issues | Not supported | Not supported | Not supported |
Top | No change | No change
+might take longer | Not supported | Not supported |
Pagination + Top | No change | No change
+might take longer | Not supported
No change
+will take longer
(1,5min for 100.000 items in a simple list) | Not supported |
Do Until + Pagination + Top | Filtering can only be done using the Filter array action. | Filtering can only be done using the Filter array action. | Filtering can only be done using the Filter array action. | Filtering can only be done using the Filter array action. |
Changelog
Date | Changes |
25.03.26 | Initial version |
❤️ Thanks for reading.