SharePoint Online: Get List Items from Large Lists [ >5000 Items] using PowerShell without List View Threshold Exceeded Error
July 6, 2021 9 Comments Get all items in 5000 large list with CSOM in PowerShell, get more than 5000 records powershell sharepoint online list
Problem: When you try to get items from larger lists with more than 5000 items through PowerShell, You get list view threshold exceed issue in SharePoint Online!
A typical PowerShell CSOM script to get list items from a list resulted in an error:
Exception calling ExecuteQuery with 0 argument[s]: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.
Root Cause: 5000 Item list view threshold in SharePoint Online!
As the error message says, we have 5000 as the hard limit for the list view threshold in SharePoint Online. Any attempt to perform bulk operations such as read/write more than 5000 items in SharePoint Online invokes this issue. Unlike SharePoint on-premises, this threshold limit cant be increased, unfortunately!
How to get Items from a large list using PowerShell without List view threshold exceeded Error?
How to overcome list view threshold in SharePoint Online? There are various solutions to deal with this issue. Here is mine: Get list items in batch!
PowerShell to Get List Items from Large Lists:
Here is how to get more than 5000 records using PowerShell in SharePoint Online List without List view threshold exceeded Error.
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
#Config Parameters
$SiteURL="//Crescent.SharePoint.com"
$ListName="Projects"
$BatchSize= 2000
Try {
$Cred= Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials[$Cred.Username, $Cred.Password]
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext[$SiteURL]
$Ctx.Credentials = $Credentials
#Get the List
$List = $Ctx.Web.Lists.GetByTitle[$ListName]
$Ctx.Load[$List]
$Ctx.ExecuteQuery[]
#Define Query to get List Items in batch
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = @"
$BatchSize
"@
#Get List Items in Batch
Do
{
$ListItems = $List.GetItems[$Query]
$Ctx.Load[$ListItems]
$Ctx.ExecuteQuery[]
$ListItems.count
$Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
}
While[$Query.ListItemCollectionPosition -ne $null]
}
Catch {
write-host -f Red "Error Getting List Items:" $_.Exception.Message
}
Just make sure, your batch is lesser than 5000 to avoid the 5000 limit threshold issue on SharePoint Online!
Get List Items from Larger lists using PnP PowerShell
The PnP PowerShell module provides PageSize switch to handles larger lists in batches:
#Parameter
$SiteURL = "//crescent.sharepoint.com/sites/PMO"
$ListName= "Projects"
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
#Get all list items from list in batches
$ListItems = Get-PnPListItem -List $ListName -PageSize 500
Write-host "Total Number of List Items:" $[$ListItems.Count]
#Loop through each Item
ForEach[$Item in $ListItems]
{
Write-Host "Id :" $Item["ID"]
Write-Host "Title :" $Item["Title"]
}
Related Posts
Video liên quan