SharePoint Online: Find and Delete Duplicate List Items using PowerShell

Requirement: Find and Delete Duplicate Items in SharePoint Online list using PowerShell

Find Duplicate Rows in a SharePoint Online List using PowerShell
Let's find duplicate items in "Projects" list in SharePoint based on the column: ProjectName.

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  
#Parameters
$SiteURL = "https://crescenttech.sharepoint.com"
$ListName = "Projects"
$UserName = "[email protected]"
$Password = "Password"
$SecurePassword= $Password | ConvertTo-SecureString -AsPlainText -Force
 
#Setup the Context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
 
#Get All List Items
$List = $Ctx.Web.Lists.GetByTitle($ListName)
$ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()

#Array for Results Data
$DataCollection = @()
ForEach($Item in $ListItems)
{
    #Collect data        
    $Data = New-Object PSObject -Property @{
            ProjectID  = $Item["ProjectID"]
            ProjectName = $Item["ProjectName"]
            CreatedDate = $Item["Created"]
            ID = $Item.Id
        }
    $DataCollection += $Data
}
#Get Duplicate Rows based on Column: ProjectName
$DataCollection | Sort-Object -Property ProjectName | Group-Object -Property ProjectName | Where-Object {$_.Count -gt 1} | Select-Object -ExpandProperty Group

This gets all list items with same "ProjectName" value found on the list.


PowerShell to Delete Duplicate Items from a SharePoint Online List
Now, the second part: How to remove duplicate rows from SharePoint Online List? Well, Here is the PowerShell to delete duplicates from SharePoint Online list. But this time, instead of One column, lets use two columns to match duplicates: "ProjectName" and "ProjectID"

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  
#Parameters
$SiteURL = "https://crescenttech.sharepoint.com"
$ListName = "Projects"
$UserName = "[email protected]"
$Password = "Password"
$SecurePassword= $Password | ConvertTo-SecureString -AsPlainText -Force
 
#Setup the Context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
 
#Get All List Items
$List = $Ctx.Web.Lists.GetByTitle($ListName)
$ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()

#Array for Results Data
$DataCollection = @()
ForEach($Item in $ListItems)
{
    #Collect data        
    $Data = New-Object PSObject -Property @{
            ProjectID  = $Item["ProjectID"]
            ProjectName = $Item["ProjectName"]
            CreatedDate = $Item["Created"]
            ID = $Item.Id
        }
    $DataCollection += $Data
}
#Get Duplicate Rows based on Two Columns: ProjectID and ProjectName
$Duplicates = $DataCollection | Sort-Object -Property CreatedDate | Group-Object -Property ProjectID,ProjectName | Where-Object {$_.Count -gt 1}

#Leave the 1st Item as Original and Select the Second Item in the Group as Duplicate
ForEach($Duplicate in $Duplicates)
{
    #Delete the Duplicate Row
    $Duplicate.Group | Select-Object -Skip 1 | ForEach-Object { 
        $List.GetItemById($_.ID).Recycle() | Out-Null; Write-host "Deleted List Item:"$_.ID
        }
        $Ctx.ExecuteQuery()
}