kwizcom banner advertisement

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 my 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
So, to mitigate this issue, we'll have to process items in batches. Here is my modified script which deletes items in batch
#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

    Do {  
        #Get all items from the list
        $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
        $Query.ViewXml = "<View><RowLimit>$BatchSize</RowLimit></View>"
        $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
}

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

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.