Deleting doublettes in SharePoint list with PnP PowerShell

1
2
3

$tenant = 'yourtenant'
$SiteName = 'YourSiteName'
$ListName = 'YourListName'

1

Connect-PnPOnline -Url "https://$($tenant).sharepoint.com/sites/$SiteName" -interactive

1

$users = (Get-PnPListItem -List $ListName -Fields 'ID', 'UniqueID', 'userPrincipalName', 'EMail', 'Created' -PageSize 5000).FieldValues

1
2

$users = $users | ForEach-Object { [PSCustomObject]@{ ID = $_.ID; UniqueID = $_.UniqueID; userPrincipalName = $_.userPrincipalName; EMail = $_.EMail; Created = $_.Created } }
$groupedItems = $users | Where-Object { $_.UniqueID -ne $null -and $_.userPrincipalName -notcontains "@specificdomain.com"} | Group-Object -Property UniqueID

1

$duplicates = $groupedItems | Where-Object { $_.Count -gt 1 } | ForEach-Object { $_.Group | Sort-Object Created | Select-Object -Skip 1 } | Sort-Object UniqueID, Created -Descending

1
2
3
4
5
6

$RemoveUsers = New-PnPBatch
foreach ($duplicate in $duplicates) {
    Write-Host "Deleting user with UniqueID $($duplicate.UniqueID) and userPrincipalName $($duplicate.userPrincipalName)"
    Remove-PnPListItem -List 'YourListName' -Identity $duplicate.ID -Batch $RemoveUsers
}
Invoke-PnPBatch -Batch $RemoveUsers

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

#Metadata:
$tenant = 'yourtenant'
$SiteName = 'YourSiteName'
$ListName = 'YourListName'

#Connect to the SharePoint site using PnP-PowerShell
Connect-PnPOnline -Url "https://$($tenant).sharepoint.com/sites/$SiteName" -interactive

#Get the list of all users
$users = (Get-PnPListItem -List $ListName -Fields 'ID', 'UniqueID', 'userPrincipalName', 'EMail', 'Created' -PageSize 5000).FieldValues

# Group items by UniqueID and sort by Created date
#Convert the array to a list of objects
$users = $users | ForEach-Object { [PSCustomObject]@{ ID = $_.ID; UniqueID = $_.UniqueID; userPrincipalName = $_.userPrincipalName; EMail = $_.EMail; Created = $_.Created } }
$groupedItems = $users | Where-Object { $_.UniqueID -ne $null -and $_.userPrincipalName -notcontains "@specificdomain.com"} | Group-Object -Property UniqueID

#Create an array to store the duplicate users
$duplicates = @()

# Find duplicates and skip the first item of each group
$duplicates = $groupedItems | Where-Object { $_.Count -gt 1 } | ForEach-Object { $_.Group | Sort-Object Created | Select-Object -Skip 1 } | Sort-Object UniqueID, Created -Descending

# Loop through the duplicate users and delete them in batches
$RemoveUsers = New-PnPBatch
foreach ($duplicate in $duplicates) {
    Write-Host "Deleting user with UniqueID $($duplicate.UniqueID) and userPrincipalName $($duplicate.userPrincipalName)"
    Remove-PnPListItem -List 'YourListName' -Identity $duplicate.ID -Batch $RemoveUsers
}
Invoke-PnPBatch -Batch $RemoveUsers