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 = "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 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 = "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()
}

SharePoint Online: Find and Delete Duplicate List Items using PowerShell SharePoint Online: Find and Delete Duplicate List Items using PowerShell Reviewed by Salaudeen Rajack on June 23, 2018 Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.