SharePoint - Bypassing 5,000 item limit via PnP

If you've used SharePoint Online before you've probably come across some of it's limitations. Of course you can store up to 30 million items or file in a SharePoint list but if a list view shows more than 5,000 item then you will probably see a nice error either in the web UI or from console if you use any of the SharePoint tools.

There are of course some ways to overcome this and Microsoft lists a couple here. Generally it's best to configure indexing and setup multiple views to limit the amount of items shown at any one time. However, sometimes you need to be able to get all the items that may cross over multiple views, or you simply need to export all list items.

Luckily you can do this using a a simple CAML query. In this example I'll be using the PnP PowerShell module to export all items a big list. Overview and setup instructions for the module can be found here.

Open up a PowerShell console

Connect to your SharePoint site

Connect-PnpOnline -Url 'https://\[tenant\].sharepoint.com/sites/\[Site Name\]' -Credentials (Get-Credential)

Declare the CAML query

$query = "<View Scope='RecursiveAll'><RowLimit>5000</RowLimit></View>"

Get all list items

$items = Get-PnPListItem -List '\[List Name\]' -Query $query

Depending on the amount of items in the list, it may take a long time.

Now you have all the items in an array, you can grab the data you need from the $_.FieldValues property.