Powershell, SharePoint

SharePoint – Delete List and Items Under Retention

2 min read

Introduction

One of the results of enabling retention policies in SharePoint is that you cannot delete a container, such as a site, list or folder, without deleting the content it contains first.

This is particularly annoying when you work with a list that has thousands or hundreds of thousands of items. This issue is aggravated if these items are in folders and subfolders.

For example, if you have a list with a folder and this folder has subfolders with items, and retention policies are enabled, you will first need to remove the items from the subfolder, then delete the subfolder, then delete the main folder, and finally, once the list is empty, you will be able to remove the list.

If you write a script to delete these items, you might also hit the throttling threshold, further delaying the deletion process.

The script below is my attempt to address both these issues: delete all items, and be a script that can be executed multiple times, even with different accounts, to delete the content of the list under retention as soon as possible.

The Script

Import-Module SharePointPnPPowerShellOnline

$SiteUrl = "https://dundermifflinpapercompany.sharepoint.com/";

# Can be credentials from file
$UserName = "";
$Password = "";
$List = "DeleteDemo";
$ListCacheFile = "C:\Temp\DeleteDemo.xml";
$CacheTimeoutInMinutes = 30;

$Cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, (ConvertTo-SecureString $Password -AsPlainText -Force)

Connect-PnPOnline -Url $SiteUrl -Credentials $Cred;

$CacheAge = ((Get-Date) - (Get-Item $ListCacheFile).LastWriteTime).TotalMinutes;

if ((test-path -Path $ListCacheFile) -and ($CacheAge -lt $CacheTimeoutInMinutes))
{
    # Cache exist and have not expired yet
    $items = Import-Clixml -Path $ListCacheFile;
} else {
    # Get items from list
    $items = Get-PnPListItem -List $List -PageSize 5000|Select-Object ID;
    # Save items into cache file
    $items|Export-Clixml -Path $ListCacheFile;
}

# Enumerate through all items randomly and remove them
foreach ($item in ($items.GetEnumerator() | Sort-Object {Get-Random}))
{
    $item;
    Remove-PnPListItem -List $List -Identity $item.Id -force;
}

How It Works

The script uses the powerful SharePoint PnP package to interact with SharePoint.

Import-Module SharePointPnPPowerShellOnline

$SiteUrl = "https://dundermifflinpapercompany.sharepoint.com/";

# Can be credentials from file
$UserName = "";
$Password = "";
$List = "DeleteDemo";
$ListCacheFile = "C:\Temp\DeleteDemo.xml";
$CacheTimeoutInMinutes = 30;

$Cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, (ConvertTo-SecureString $Password -AsPlainText -Force)

Connect-PnPOnline -Url $SiteUrl -Credentials $Cred;

On the first run, after connecting to SharePoint, it loads the items into a collection, $items, and exports it to XML. This exported XML can then be used by any subsequent executions of the script while the cache period is still valid.

$CacheAge = ((Get-Date) - (Get-Item $ListCacheFile).LastWriteTime).TotalMinutes;

if ((test-path -Path $ListCacheFile) -and ($CacheAge -lt $CacheTimeoutInMinutes))
{
    # Cache exist and have not expired yet
    $items = Import-Clixml -Path $ListCacheFile;
} else {
    # Get items from list
    $items = Get-PnPListItem -List $List -PageSize 5000;
    # Save items into cache file
    $items|Export-Clixml -Path $ListCacheFile;
}

This collection of items, whether it was loaded from cache or pulled directly from SharePoint, is randomised before any deletions are attempted.

# Enumerate through all items randomly and remove them
foreach ($item in ($items.GetEnumerator() | Sort-Object {Get-Random}))
{
    $item;
    Remove-PnPListItem -List $List -Identity $item.Id -force;
}

This randomisation is key to allowing multiple instances of the script to run at the same time. If the items were not randomised, the running scripts would try to delete the same items, and the deletion process would take longer than a single execution.

Note: SharePointPnPPowerShellOnline has since been retired in favour of the PnP.PowerShell module, where the same cmdlets exist. On the first run the cache file does not exist yet, so Get-Item reports an error before the script reads the list instead; the script carries on.

Conclusion

I hope you found this useful. Questions, bug reports and other comments are welcome below.

Originally published on Experts Exchange.

Posted on

No comments yet

Comments are moderated and appear once approved.