SharePoint Online: Find and Delete Duplicate List Items using PowerShell
Requirement: Find and Delete Duplicate Items in a SharePoint Online list using PowerShell.
Find Duplicate Rows in a SharePoint Online List using PowerShell
When you have a list with a lot of data, it’s easy for duplicates to sneak in. Maybe someone entered the same data twice, or an import process created duplicates. Whatever the case may be, duplicate items can cause all sorts of problems in your SharePoint Online lists- from inaccurate reporting to throwing off sorting and filtering. It can be annoying and time-consuming to track down and delete all the duplicate items individually. Thankfully, We have PowerShell to find and delete duplicate list items in SharePoint Online quickly.
Let’s find duplicate items in the “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://Crescent.sharepoint.com"
$ListName = "Projects"
$UserName = "Salaudeen@CrescentTech.com"
$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://Crescent.sharepoint.com"
$ListName = "Projects"
$UserName = "Salaudeen@CrescentTech.com"
$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()
}
This code does not work for sharepoint 2019 On-premise because of the credential.
Kindly help.
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
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.
As per your requirement, the entry must be validated on saving list item! One quick solution would be using Javascript CSOM code to validate!
How to delete from Large list having values more than 5000.
Thanks