From my desktop, I wanted to delete all the items in a list. The list had 10,000+ item and loop through the items one at a time is slow and not efficient. This is where batch process comes in and will help to quickly delete all the items in a large list.
In this example, I commented out the SharePoint Online Credentials because I’m working with on on-prem environment.
The batch size is set to 1,000
#Load SharePoint CSOM Assemblies (you will need to grab these from a SharePoint server)
Add-Type -Path "C:\SharePoint DLL\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\SharePoint DLL\Microsoft.SharePoint.Client.Runtime.dll"
$SiteURL = "https://mylocalsite/sites/taco/"
$ListName = "GiantList"
$BatchSize = "1000"
Try {
#$Cred= Get-Credential
#$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
#$Ctx.Credentials = $Credentials
#Get the List
$List = $Ctx.Web.Lists.GetByTitle($ListName)
$Ctx.Load($List)
$Ctx.ExecuteQuery()
#Define Query to get List Items in batch
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = @"
$BatchSize
"@
#Get List Items in Batch
Do
{
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
if ($ListItems.Count -gt 0)
{
for ($i = $ListItems.Count-1; $i -ge 0; $i--)
{
$ListItems[$i].DeleteObject()
}
$clientContext.ExecuteQuery()
}
else
{
Write-Host "." -foregroundcolor black -backgroundcolor yellow
$continue = $false;
}
$Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
}
While($Query.ListItemCollectionPosition -ne $null)
}
Catch {
write-host -f Red "Error Adding Items to List!" $_.Exception.Message
}
Limiting the number of rows returned in each query will help to avoid this error:
Exception calling "ExecuteQuery" with "0" argument(s): "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."
Some of the scripts was borrowed from:
http://www.sharepointdiary.com/2016/12/sharepoint-online-get-all-items-from-large-lists-powershell-csom.html
Note:
I plan to revisit this and batch processing where you combine all the delete statements into a large XML string and pass it to SharePoint for processing.
