SharePoint Online：使用 PowerShell 查找并删除重复的列表项

要求：使用 PowerShell 查找并删除 SharePoint Online 列表中的重复项目。

使用 PowerShell 查找 SharePoint Online 列表中的重复行

当您的列表包含大量数据时，很容易出现重复项。也许有人两次输入相同的数据，或者导入过程创建了重复项。无论情况如何，重复的项目都可能导致 SharePoint Online 列表中出现各种问题 - 从不准确的报告到无法进行排序和筛选。单独追踪并删除所有重复项目可能会很烦人且耗时。值得庆幸的是，我们有 PowerShell 可以快速查找和删除 SharePoint Online 中的重复列表项。

让我们根据“项目名称”列在 SharePoint 的“项目”列表中查找重复项目。

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  
#Parameters
$SiteURL = "https://Crescent.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

这将获取列表中具有相同“ProjectName”值的所有列表项。

PowerShell 从 SharePoint Online 列表中删除重复项目

现在，第二部分：如何从 SharePoint Online 列表中删除重复行？好吧，这是用于从 SharePoint Online 列表中删除重复项的 PowerShell。但这一次，我们不再使用一列，而是使用两列来匹配重复项：“ProjectName”和“ProjectID”。

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  
#Parameters
$SiteURL = "https://Crescent.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()
}