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, 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

Exception calling ExecuteQuery with 0 argument(s) - The attempted operation is prohibited because it exceeds the list view threshold enforced by the Administrator.

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 set on SharePoint Online. 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 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 would fail still! E.g: If you try to get all list items (excluding "folders" by specifying "Where" clause) from a large list which could return more than 5000 rows, You'll hit by 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 my another article addressing updating large list items with the same issue: SharePoint Online: Get All List Items from large lists using PowerShell
Fix SharePoint Online "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator." in PowerShell Fix SharePoint Online "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator." in PowerShell Reviewed by Salaudeen Rajack on October 10, 2017 Rating: 5

1 comment:

  1. 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?

    ReplyDelete

Please Login and comment to get your questions answered!

Powered by Blogger.