SharePoint Online: Find and Delete Duplicate List Items using PowerShell

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

Find and Delete Duplicate Items in SharePoint Online 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 the 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 the SharePoint Online list. But this time, instead of One column, let’s 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()
}

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

4 thoughts on “SharePoint Online: Find and Delete Duplicate List Items using PowerShell

  • September 5, 2021 at 12:48 AM

    This code does not work for sharepoint 2019 On-premise because of the credential.
    Kindly help.

    Reply
    • September 8, 2021 at 9:44 PM

      For SharePoint On-Premises, Here is how to setup the credentials:

      #Setup Credentials to connect
      $Credentials = [System.Net.CredentialCache]::DefaultCredentials #Current User Credentials

      #Or connect using user account/password
      #$Credentials = New-Object System.Net.NetworkCredential($UserName, (ConvertTo-SecureString $Password -AsPlainText -Force))

      and then:

      #Set up the context
      $Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
      $Context.Credentials = $credentials
      $web = $Context.Web

      Reply
  • April 24, 2020 at 6:23 PM

    Hello
    I need help with my SharePoint online list to prevent double entry of list items.
    Background:
    I have an equipment assignment list with the following column
    • Tag Number
    • Equipment description
    • Assignment date
    • Return date
    • Status
    o Assigned
    o Returned
    My request is that I want to prevent a double entry if an equipment is assigned based on the Tag Number and the status is Assigned.
    Please any help in the right direction will be appreciated.

    Reply
    • September 19, 2020 at 3:58 PM

      As per your requirement, the entry must be validated on saving list item! One quick solution would be using Javascript CSOM code to validate!

      Reply

Leave a Reply