Query more than 8 Lakhs (0.8 million) documents from SharePoint Online document library using Power Automate

Question

I have a document library with the document set content type and document content type in it. In the document library there is more than than 8 Lakhs (0.8 million) document. In the "My Document" content type there is a "DocumentStatus" choice column which has value Draft, In Progress, Approved, no default value is set in it - so, there are thousands of documents which has no "DocumentStatus" value.

I want to find all documents where "DocumentStatus" is empty from this big document library. I tried the Do Until loop approach to get more than 5000 items using Power Automate but it is executing for 2 cycles which is giving 5000*2 =10000 items after that the flow is getting successfully completed. But I want to query all 0.8 million documents, performance doesn't matter for me as it is one time activity.

Was anyone able to retrieve about a million documents using Power Automate successfully?

I am looking for such Power Automate and PowerShell reference. If anyone experienced querying big number of items from SharePoint, requesting to share your knowledge here.

Answer 1

When you do something heavy, don't use Power Automate. It's not meant for huge queries, but for bite-size automation.

Use Get-PnPListItem from PnP.PowerShell, with pagesize of 1000 (1000 items per batch).

Get-PnPListItem -List Tasks -PageSize 1000 -ScriptBlock { Param($items) $items.Context.ExecuteQuery() } | ForEach-Object {
    #do something here, the item is in $_ object. Assuming your DocumentStatus is a choice field.
    if ($_.FieldValues.DocumentStatus -eq "Draft") {
    }
}

Reference: https://pnp.github.io/powershell/cmdlets/Get-PnPListItem.html#example-8

Answer 2

According to this Microsoft article, there is a hard limit of 100 000 items. Pagination is not turned on by default. You would need to turn on Pagination and increase the threshold to 100 000.

Name	Limit	Notes
Paginated items	5,000 for Low, 100,000 for all others	To process more items, trigger multiple flow runs over your data.