Scenario: You have a large (>5k items) list in SharePoint Online you need to clean-up, for instance:

• You need to delete the entire list
• You need to delete all the list items, but keep the list
• You need to delete some of list items, but keep the others

Deleting a large SharePoint Online list

There was a problem in SharePoint Online – you could not delete a large list – you had to remove all items first, but removing all items was also a challenge. Microsoft improved SharePoint Online, so now it takes ~1 second to delete any SharePoint list, including 5000+ items list via GUI or PowerShell:

Remove-PnPList -Identity $list

command works very fast – ~1 second to delete entire list with >5000 items.

Delete all items in a large SharePoint Online list

In this scenario we need to keep the list, but make it empty (clean it up).

GUI: You can change the list view settings “Item Limit” to <5000 and try to delete items in chunks, but (at least in my experience) when you try to select, let say, 1000 items and delete them via GUI – it says “775 items were not deleted from large list”:

so this option seems like not a good one.

ShareGate: 3-rd party tools like Sharegate, SysKit give a good results too.

PowerShell

Try this PowerShell command with ScriptBlock:

Get-PnPListItem -List $list -Fields "ID" -PageSize 100 -ScriptBlock { Param($items) $items | Sort-Object -Property Id -Descending | ForEach-Object{ $_.DeleteObject() } } 

or this PowerShell with batches:

$batch = New-PnPBatch
1..12000 | Foreach-Object { Remove-PnPListItem -List $list -Identity $_ -Batch $batch }
Invoke-PnPBatch -Batch $batch

for me both methods gave same good result: ~17 items per second ( ~7 times faster than regular).

Deleting some items from a large SPO list

Consider the following scenario: in a large SharePoint list there are items you need to delete and the rest items must stay (typical case might be to purge old items – e.g. items created last year).

In this case you’d

• get all list items (or use query to get some list items)
• select items that need to be deleted based on your criteria, e.g. created date or last modified date etc.
• use PnP.PowerShell batches to delete only what you need

# to get all list items
$listItems = Get-PnPListItem -List Tasks -PageSize 1000
# or to get some list items 
$listItems = Get-PnPListItem -List Tasks -Query <query>
# select items to delete
$itemsToDelete = $listItems | ?{$_.Modified -lt $threshold}
# delete some list items
$batch = New-PnPBatch 
$itemsToDelete | Foreach-Object { Remove-PnPListItem -List $list -Identity $_ -Batch $batch } 
Invoke-PnPBatch -Batch $batch

PnP.PowerShell batch vs ScriptBlock

How fast are PnP batches? What is better in terms of performance – ScriptBlock or Batching? Here are my measurements:

Time elapsed, seconds	with batches	with scriptBlock	without batches
Add-PnPListItem (100 items)	6-10 seconds		60-120 seconds
Add-PnPListItem (500 items)	20-40 seconds		230-600 seconds
Add-PnPListItem (7000 items)	314-600 seconds
Add-PnPListItem (37000 items)	3200 seconds
Remove-PnPListItem (1000 items)	58-103 seconds	58 seconds	430-1060 seconds
Remove-PnPListItem (7000 items)	395-990 seconds 3000 seconds	397-980 seconds
Remove-PnPListItem (30000 items)	one big batch : 13600 seconds 30 batches 1000 items each: 3500 seconds

both – PnP PowerShell batches and ScriptBlocks are 7-10 times faster than plain PnP PowerShell!

Can we use Microsoft Graph API to complete the same task? TBC…

Note… For the sake of history: It used to be like that for 5k+ lists:
“Remove-PnPList” fails with a message “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator”. Deleting with GUI failed too.

References:

• Get-PnPListItem PageSize ScriptBlock Examples
• Get-PnPListItem
• Delete all SharePoint list items with PowerShell
• Deleting a Library That Exceeds Threshold