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

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= "//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.

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= "//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>"

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

Related Posts