Fix SharePoint Online “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.” in PowerShell
Problem: When trying to get list items from a SharePoint Online list through PowerShell, I got this error message:
Exception calling “ExecuteQuery” with “0” argument(s): “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”
At line:26 char:5
+ $Ctx.ExecuteQuery()
+ ~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ServerException
Actually, I was simply trying to delete all list items:
#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= "https://crescent.sharepoint.com/Sites/Projects"
$ListName="ProjectDocs"
#Setup Credentials to connect
$Cred = Get-Credential
$Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = $Cred
#Get the web and List
$Web=$Ctx.Web
$List=$web.Lists.GetByTitle($ListName)
#Get all items from the library
$Query = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
#Loop through each file in the library
Foreach($Item in $ListItems)
{
#Delete all Items
$List.GetItemById($Item.Id).DeleteObject()
}
$Ctx.ExecuteQuery()
Write-host -f Green "All Items deleted!"
Root Cause: This is because of the resourced throttling threshold value of 5000 hard-coded on SharePoint Online. The list view threshold ensures that users are not performing expensive operations on the SharePoint Online environment. Unfortunately, we can’t change this threshold value as we do in SharePoint On-Premises.
Solution: Process List Items in Batches to Fix SharePoint Online List View Threshold Issue in PowerShell
So, to mitigate this issue, we have to process items in batches. Here is my modified script, which deletes items in batch to handle the SharePoint Online List View threshold in PowerShell.
#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= "https://crescent.sharepoint.com/"
$ListName="Projects"
$BatchSize = 500
#Setup Credentials to connect
$Cred = Get-Credential
$Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
Try {
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = $Cred
#Get the web and List
$Web=$Ctx.Web
$List=$web.Lists.GetByTitle($ListName)
$Ctx.Load($List)
$Ctx.ExecuteQuery()
Write-host "Total Number of Items Found in the List:"$List.ItemCount
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "<View Scope='RecursiveAll'><Query><OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy></Query><RowLimit Paged='TRUE'>$BatchSize</RowLimit></View>"
Do {
#Get items from the list in batches
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
$Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
Write-host Deleting $($ListItems.count) Items Starting from Item ID $ListItems[0].ID
#Loop through each item and delete
ForEach($Item in $ListItems)
{
$List.GetItemById($Item.Id).DeleteObject()
}
$Ctx.ExecuteQuery()
} While ($Query.ListItemCollectionPosition -ne $null)
Write-host -f Green "All Items Deleted!"
}
Catch {
write-host -f Red "Error Deleting List Items!" $_.Exception.Message
}
Please note, If your CAML query retrieves more than 5000 Rows, It will still fail! E.g., If you try to get all list items (excluding “folders” by specifying the “Where” clause) from a large list that could return more than 5000 rows, You’ll hit the list view threshold issue!
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
<Query>
<Where>
<Eq>
<FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value>
</Eq>
</Where>
</Query>
<RowLimit Paged='True'>$BatchSize</RowLimit>
</View>"
Other scenarios and solutions
You may encounter the “The attempted operation is prohibited because it exceeds the list view threshold” when executing cmdlets on large lists with more than 5000 items. E.g. Add-PnPFile, Copy-PnPFile, find-pnpfile, get-pnpfolder, get-pnplist, measure-pnplist, move-pnpfolder, remove-pnplist, rename-pnpfolder. Or when you are trying to break inheritance, create a calculated column, delete a list, get items, share a file, rename a folder, etc.
As far solution: You must reduce the number of items in the list or document library by moving or deleting items from the file below 5000.
Here is another article addressing updating large list items with the same issue: SharePoint Online: Get All List Items from large lists using PowerShell without List view Exceeded Error
Hi – I have been trying to amedn this to Move a Folder and its contents to another Library to no avail as the library exceeds the list view threshold – do you have this script with the Move Parameters?