I'm trying to delete about a million items from a SharePoint Online Library. I believe the code below (found on the web) batches this to <5000 items but it still fails with error: Error Deleting List Items! Exception calling "ExecuteQuery" with "0" argument(s): "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."
Its using CAML but I think its deleting one file at a time? I can't even delete the library! Any ideas on why ?
#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://sharepoint.com/sites/site"
$ListName="my library"
$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
#Define CAML Query
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
#$Query.ViewXml = "<View><RowLimit>$BatchSize</RowLimit></View>"
$Query.ViewXml = '@
<View Scope="RecursiveAll">
<Query>
<Where>
<And>
<And>
<Gt><FieldRef Name="ID"></FieldRef><Value Type="Number">0</Value></Gt>
<Lt><FieldRef Name="ID"></FieldRef><Value Type="Number">2000</Value></Lt>
</And>
<Leq>
<FieldRef Name="Created" />
<Value IncludeTimeValue="TRUE" Type="DateTime">2011-12-31T00:00:00Z</Value>
</Leq>
</And>
</Where>
</Query>
</View>'
Do {
#Get items from the list in batches
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
#Exit from Loop if No items found
If($ListItems.count -eq 0) { Break; }
Write-host Deleting $($ListItems.count) Items from the List...
#Loop through each item and delete
ForEach($Item in $ListItems)
{
$List.GetItemById($Item.Id).DeleteObject()
}
$Ctx.ExecuteQuery()
} While ($True)
Write-host -f Green "All Items Deleted!"
}
Catch {
write-host -f Red "Error Deleting List Items!" $_.Exception.Message
}
I've pinned pointed the problem to this part of the code:
#Loop through each item and delete
ForEach($Item in $ListItems)
{
$List.GetItemById($Item.Id)
$Item.id
$List.GetItemById($Item.Id).DeleteObject()
}
$Ctx.ExecuteQuery()
When running this code it errors with: Error Deleting List Items! Exception calling "ExecuteQuery" with "0" argument(s): "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."
I'm outputting $Item.id and can see batched of 100. Something to do with DeleteObject(). Maybe its deleting folders which could be the cause of the isue. How can I test for only files and not folders within this code?