Get All Items from a Large SharePoint Online Document Library with PowerShell

Firstly, I want to caveat this with the fact that the List View Threshold in SharePoint is a good thing. It encourages data integrity and categorisation, as well as efficient queries.

With clean data, indexing, and the right query, the threshold shouldn’t be a stumbling block to overcome.

That being said, there will always be scenarios where you need to get around it because in the real world, nobody is perfect. What makes the problem more difficult is that SharePoint Online’s threshold is static, as opposed to the glorious days of SharePoint On-Premise where an admin could temporarily change it at will, should the need arise.

The list view threshold on SharePoint Online is 5,000.

In this example I’m using PnP.PowerShell 2.12.0, which requires PowerShell 7 and I’m going to connect to a SharePoint Online site and get all items from a document library that are folders:

Import-Module PnP.PowerShell

$siteUrl = "https://yoururl.sharepoint.com/sites/thissite/"

$libraryName = "Documents"

Connect-PnPOnline -Url $siteUrl -Interactive -ClientId abc123

$folders = Get-PnPListItem -List $libraryName | Where-Object {$.FileSystemObjectType -eq "Folder"}

If there are less than 5,000 folders, great that will work. If there are more I get this error:

The attempted operation is prohibited because it exceeds the list view threshold.

So, now what do you do? The answer is suprisingly simple, we add a PageSize to the query, meaning that the response is brought back in batches.

Like so:

Import-Module PnP.PowerShell

$siteUrl = "https://yoururl.sharepoint.com/sites/thissite/"

$libraryName = "Documents"

Connect-PnPOnline -Url $siteUrl -Interactive -ClientId abc123

#Get all items from the Documents library in batches of 1000 where the item is a folder
$folders = Get-PnPListItem -List $libraryName -PageSize 1000 | Where-Object {$.FileSystemObjectType -eq "Folder"}

#Loop through all folders and print out URL
foreach ($folder in $folders)
{
    $folderUrl = $folder.FieldValues["FileRef"]
    Write-host "Folder: $folderUrl"
}

Disconnect-PnPOnline

This will now return all of the folders and loop through them