I need to load a large SharePoint list into an Azure Logic App. The Get items action can retrieve the data with pagination, but it takes nearly two minutes. Using an HTTP action and selecting only the fields I need is much faster, but the request stops at 5,000 items, and the HTTP connector does not appear to offer usable pagination. I cannot make changes to the SharePoint list. Is there a way to work around this limit directly in the Logic App, perhaps by following the next link returned by SharePoint?
3 Answers
The HTTP action's built-in pagination often does not recognize the next-link property returned by SharePoint, so you may need to implement the loop yourself. Initialize a string variable such as nextUrl with the first SharePoint REST URL and an array variable for results. In an Until loop, call the URL in nextUrl, append the response's value array to your results, then set nextUrl to the response's odata.nextLink property. Use bracket syntax such as body('HTTP')?['odata.nextLink'] because the property name contains a dot. Requesting odata=nometadata and using $select can also reduce the response size. Set the Until loop's count and timeout explicitly rather than relying on the defaults.
Another reliable approach is to retrieve items in ID ranges rather than depending on an opaque skip token. SharePoint item IDs are indexed, so you can request pages with filters like ID greater than the previous value and ID less than or equal to the next boundary, repeating until a page is empty. This is especially useful when you cannot add an index to the list. If you need to filter by a non-indexed business column, retrieve the records by ID range first and apply the additional filtering inside the Logic App. For very large lists, you can build several ID ranges and process them with controlled For each concurrency, while making sure downstream systems can handle parallel writes.
The 5,000-item threshold is generally a SharePoint list-view threshold, not an absolute maximum number of items that can ever be returned. Queries that scan or sort a large list using non-indexed columns are what usually fail. Filtering or ordering by ID and retrieving results in smaller pages avoids that problem. If the standard items endpoint remains troublesome, SharePoint's RenderListDataAsStream endpoint is another option; it supports CAML, row limits, and a next link, although it requires more setup.
If the list is very large, avoid continually appending to one array because Logic Apps has action and message-size limits. Process or store each page as it arrives instead.