PowerShell and CSOM Bulk Delete List Items

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)

#Define Query to get List Items in batch
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = @"



#Get List Items in Batch
$ListItems = $List.GetItems($Query)

if ($ListItems.Count -gt 0)
for ($i = $ListItems.Count-1; $i -ge 0; $i--)
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:

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.

Leave a Reply

Your email address will not be published. Required fields are marked *