Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
<#
.TERMS
No terms - use on your own risk!
.SYNOPSIS
This script efficiently emtpties are large list and is approximately 30 times faster than deleting item by item.
You need to have the SharePoint Online SDK (https://www.microsoft.com/en-us/download/details.aspx?id=42038) installed to run this script.
.PARAMETER weburl
SharePoint Online web URL, for example 'https://contoso.sharepoint.com'.
.PARAMETER listname
Name of the list that should be emptied
.Author
Max Melcher (@maxmelcher) - more on my blog https://melcher.it
#>
param([Parameter(Mandatory=$true,ValueFromPipeline=$true)]$weburl,[Parameter(Mandatory=$true,ValueFromPipeline=$true)]$listname, $username, $password)
function empty-list($weburl, $listname)
{
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($webUrl)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword)
$clientContext.Credentials = $credentials
if (!$clientContext.ServerObjectIsNull.Value)
{
Write-Host "Connected to SharePoint Online web: '$webUrl'" -ForegroundColor Green
}
try
{
#global counter
$counter=0
$spQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
#create a simple batch. I received the best results with 500 items per batch.
#5000 is the maximum, but i received so many timeouts.
$spQuery.ViewXml = "<View><Query></Query><ViewFields><FieldRef Name='Id' /></ViewFields><RowLimit>500</RowLimit></View>"
#track the performance
$watch = [System.Diagnostics.Stopwatch]::StartNew()
#get the list
$list = $clientContext.Web.Lists.GetByTitle($listname);
do
{
#get the current batch of items
$items = $list.GetItems($spQuery);
$clientContext.Load($items)
#get a batch and delete the previous items in one go
$clientContext.ExecuteQuery()
$count = $items.Count
Write-Host "...deleting $count items" -ForegroundColor Yellow
while ($items.Count -gt 0)
{
$counter++
$items[0].DeleteObject()
if ($counter%500 -eq 0)
{
Write-Progress -Activity "Empty List '$listname'" -status "Item $counter of $total marked for deletion"
}
}
#iterate the collection
$spQuery.ListItemCollectionPosition = $items.ListItemCollectionPosition
}
while ($spQuery.ListItemCollectionPosition -ne $null)
#delete the last batch
$clientContext.ExecuteQuery()
#stats
$seconds = ($watch.ElapsedMilliseconds/1000)
$secondsf = "{0:N0}" -f $seconds
$ips = $counter / $seconds
write-host -ForegroundColor Green "Done! deleted $counter items in $secondsf seconds. $ips items per second"
}
catch
{
Write-Host -ForegroundColor Red $_.Exception.ToString()
}
}
# change to the path of your CSOM dlls and add their types
$csomPath = "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI"
Add-Type -Path "$csomPath\Microsoft.SharePoint.Client.dll"
Add-Type -Path "$csomPath\Microsoft.SharePoint.Client.Runtime.dll"
if([String]::IsNullOrWhiteSpace($username)) {
$username = Read-host "What's your username?"
}
if([String]::IsNullOrWhiteSpace($password)) {
$securePassword = Read-host "What's your password?" -AsSecureString
} else {
$securePassword = ConvertTo-SecureString $password -AsPlainText -Force
}
Empty-List -weburl $weburl -listname $listname -user $username -password -securePassword
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment