SharePoint PnP roadmap

Good news!
On Sep, 18 during the SIG community call, PnP Team shared their plans on PnP Sites Core library and PnP Core SDK.
“PnP Sites Core v4” library and “PnP Core SDK v1” with .net core support (.net Standard 2.0) – expected in December 2020!

PnP PowerShell v4 for SPO library built for .Net Standard 2.0 / PowerShell 7 will be released in Dec 2020 as well.

How to delete a large SPO list or all/some items in a large SPO list

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, secondswith batcheswith scriptBlockwithout batches
Add-PnPListItem (100 items)6-10 seconds60-120 seconds
Add-PnPListItem (500 items)20-40 seconds230-600 seconds
Add-PnPListItem (7000 items)314-600 seconds
Add-PnPListItem (37000 items)3200 seconds
Remove-PnPListItem (1000 items)58-103 seconds58 seconds430-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: