Choose the Right Get items Retrieval Pattern

When a green run still leaves rows behind

If you have ever watched a Power Automate flow finish successfully and then found SharePoint rows left behind, this is the awkward bit: a green run proves the action ran. It does not prove the action returned every row the process expected.

Get items is the SharePoint action that reads rows from a SharePoint list. A retrieval pattern is simply the way the flow asks for those rows. Before changing a live flow, write down the exact rows you expected, record how many Get items returned, then choose one test and one fallback.

For example, “all open requests” is a testable row scope. “Everything” is not.

The short answer

Start with the smallest test that matches the symptom.

If the flow stops around 100 rows, check Top Count. If a large filtered list returns no matches, test pagination and the filter. If more than 5,000 rows may match, check the flow owner's performance profile before deciding whether pagination is enough or a restartable batch pattern is needed. Use HTTP/REST only when the standard action cannot express or page the query clearly.

Top Count and pagination are different controls. Top Count asks Get items for a specified number of rows and must stay at or below 5,000. Pagination allows the action to request additional pages up to a threshold, but the flow's performance profile, SharePoint query behaviour, connector limits, and request limits still apply.

Terms you will see

• OData filter query: filter text that narrows the rows in SharePoint before the flow receives them.
• Indexed column: a list column SharePoint can use more efficiently when a large-list query filters on it.
• List view threshold: the 5,000-item query boundary SharePoint uses to protect the service. It is not the maximum size of a list.
• Controlled batching: several smaller, ordered reads with progress recorded between them, so the flow can prove where one batch ended and the next begins.
• HTTP/REST: the lower-level SharePoint request action. It offers more query and paging control, but it does not bypass SharePoint permissions or service limits.

What matters

• Default Get items is not “all rows”. Microsoft says the default options return 100 items. Top Count can increase the requested number up to 5,000; a value above 5,000 fails.
• A list can contain far more than 5,000 items. The 5,000 list view threshold is a query boundary, not a list-size limit. Filters and indexes help keep a query selective; they do not prove that every intended row was returned.
• Pagination has two boundaries. The Get items action still works in pages, while Power Automate also applies a total paginated-items ceiling to the flow. Microsoft's current limits page lists 5,000 paginated items for the Low performance profile, which includes Microsoft 365 plans, and 100,000 for other profiles. These are flow-level ceilings, not a promise that every SharePoint query will succeed at that scale.
• A pagination threshold creates a ceiling, not a target. Set it to a defensible maximum for the expected row set. Pagination requests and retries count as action runs.
• Large filtered lists need a specific test. Microsoft warns that Get items can return no records when no matching item appears in the first 5,000 items. Its documented first fix is to enable pagination.
• Batching is a maker pattern, not a universal Microsoft rule. An ordered ID, date, or status boundary can make work restartable and auditable, but the design still needs a stable order, progress logging, a stop condition, and testing for changes made while the flow runs.

Choose the first test

Recommended move

1. Name the expected rows. Write “all open requests”, “items modified this month”, or another scope that can be counted.
2. Capture the returned count. Check the Get items output in run history or add a Compose action for a non-destructive test. The expression will resemble length(body('Get_items')?['value']); use the action's actual internal name.
3. Separate Top Count from pagination. Keep Top Count at or below 5,000. Use pagination only when the expected total and the applicable flow ceiling justify additional pages.
4. Check who owns the flow before relying on a number above 5,000. Power Automate applies the owner's plan and performance profile. If you cannot see that information, ask the owner or an admin before designing around a larger threshold.
5. Fix the filter before scaling the flow. On a large list, narrow the query deliberately and index the column that does the useful narrowing where practical.
6. Use batches when you need control that pagination does not provide. Keep a stable ascending order, record the last returned boundary, and stop only after a partial or empty final batch proves there are no more rows in scope.
7. Escalate to HTTP/REST for a clear query or paging reason. SharePoint REST supports $select, $filter, $orderby, $top, and $skiptoken. $skip does not work for SharePoint list-item paging. REST still uses the caller's permissions and remains subject to SharePoint and Power Automate limits.

Retrieval decision record

Use this before changing a live flow.

Evidence notes

Use Microsoft documentation to trust what the controls and limits mean. Do not use it as proof that your particular list, owner account, connector connection, or live process is safe.

• Microsoft's Get items guidance proves the default 100-item return, Top Count up to 5,000, OData filtering, Order By, and the documented large-list filter behaviour. It does not prove that one settings combination retrieves every row your process intends to use.
• Microsoft's Power Automate limits page gives the current flow-level paginated-item and request boundaries. The owner's plan and performance profile matter, and pagination requests and retries count as action runs. Those ceilings do not guarantee that a SharePoint query will perform well or avoid connector throttling.
• Microsoft's SharePoint threshold guidance explains why indexed columns and selective filters matter. An index helps SharePoint evaluate a query; it does not validate your business definition of the expected rows.
• The ID-based and similar batching suggestions come from practitioner patterns and Collab365 synthesis. Treat them as designs to test, not as official universal fixes.
• SharePoint REST documentation proves which query and paging controls are available. REST gives more control; it does not remove permissions, throttling, or row-completeness testing.

What this proves: you selected a defensible first retrieval test and recorded its result. What it does not prove: the flow is repaired, performance is guaranteed, downstream updates are safe, or the pattern suits every list and tenant.