SharePoint Online：使用 PowerShell 将列表版本历史记录导出到 Excel

---

要求：将所有版本的 SharePoint Online 列表项导出到 CSV (Excel) 文件。

PowerShell 将列表项的版本历史提取并导出到 CSV 文件

作为管理员，有时您可能需要将 SharePoint Online 列表版本历史记录导出到 Excel 以便进一步分析。我们可以使用 PowerShell 将列表版本历史记录导出到 Excel。本文假设您有一个现有的 SharePoint Online 列表，其中包含下面给出的列，并且您可能需要根据您的要求创建和更改列。本文将引导您完成使用 PowerShell 将 SharePoint Online 列表版本历史记录导出到 Excel 的步骤。

#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"

Function Export-VersionHistory()
{
  param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ListName,
        [Parameter(Mandatory=$true)] [string] $CSVFile
    )
    Try {

        #Delete the Output report file if exists
        if (Test-Path $CSVFile) { Remove-Item $CSVFile }

        #Get Credentials to connect
        $Cred= Get-Credential

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
        
        #Get the List
        $List = $Ctx.Web.Lists.GetByTitle($ListName)
        $Ctx.Load($List)
        $Ctx.ExecuteQuery()
        
        #Define Query to get List Items in batch
        $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
        $Query.ViewXml = "<View Scope='RecursiveAll'><RowLimit>2000</RowLimit></View>"
  
        #Batch process list items - to mitigate list threshold issue on larger lists
        Do {  
            #Get items from the list in batches
            $ListItems = $List.GetItems($Query)
            $Ctx.Load($ListItems)
            $Ctx.ExecuteQuery()            
            $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition

            #Array to hold result
            $VersionHistoryData = @()

            #Iterate throgh each item
            Foreach ($Item in $ListItems)
            {
                write-host "Processing Item:" $item.id -f Yellow
            
                #Get all versions of the list item
                $Versions = $Item.versions
                $ctx.Load($Versions)
                $Ctx.ExecuteQuery()

                If($Versions.count -gt 0)
                {
                    #Iterate each version
                    Foreach($Version in $Versions)
                    {
                        #Get the Creator object of the version
                        $CreatedBy =  $Version.createdby
                        $Ctx.Load($CreatedBy)
                        $Ctx.ExecuteQuery()

                        #Send Data to object array
                        $VersionHistoryData += New-Object PSObject -Property @{
                        'Item ID' = $Item.ID
                        'Title' =  $Version.FieldValues["Title"]
                        'Version Label' = $Version.VersionLabel 
                        'Version ID' = ($Version.VersionId/512)
                        'Created On' = (Get-Date ($Version.Created) -Format "yyyy-MM-dd/HH:mm:ss")
                        'Created By' = $CreatedBy.Email
                        }
                    }
                }
            }
        }While($Query.ListItemCollectionPosition -ne $null)
        #Export the data to CSV
        $VersionHistoryData | Export-Csv $CSVFile -Append -NoTypeInformation

        Write-host -f Green "Version History Exported Successfully to:" $CSVFile
     }
    Catch {
        write-host -f Red "Error Exporting version History to CSV!" $_.Exception.Message
    }
}

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ListName="Projects"
$CSVFile="C:\Temp\VersionHistory.csv"

#Call the function to generate version History Report
Export-VersionHistory -SiteURL $SiteURL -ListName $ListName -CSVFile $CSVFile

我在脚本中使用了“标题”字段值。您可以使用以下命令添加任何其他列：$version.FieldValues[“ColumnInternalName”]

重要：此脚本仅适用于 CSOM 版本 16.1.6906.1200 或更高版本！从 https://www.microsoft.com/en-us/download/confirmation.aspx?id=42038 下载最新版本的 CSOM SDK

该脚本生成一个包含版本数据的 CSV 文件，例如：

• 版本标签，
• 版本号
• 版本创建时
• 谁创建了版本
• 版本的标题字段值（您也可以添加任何其他字段！）

对于本地 SharePoint，请使用以下脚本：如何使用 PowerShell 将 SharePoint 版本历史记录导出到 CSV？

PnP PowerShell 导出列表中所有项目的版本历史记录

让我们使用 PnP PowerShell 将列表中项目的所有早期版本导出到 CSV 文件。

#Set Variables
$SiteURL = "https://crescent.sharepoint.com/sites/PMO"
$ListName = "Projects"
$CSVPath = "C:\Temp\ProjectsVersionsRpt.csv"
  
#Connect to SharePoint Online site
Connect-PnPOnline -Url $SiteURL -Interactive
 
#Get All Fields from List
$ListFields = Get-PnPField -List $ListName | Where {!$_.Hidden}
$VersionHistoryData = @()
 
#Get All List Items - Exclude Folders
Get-PnPListItem -List $ListName -PageSize 500 | Where {$_.FileSystemObjectType -ne "Folder"} | ForEach-Object {
    Write-host "Getting Versioning Data of Item:"$_.ID
    $Versions = Get-PnPProperty -ClientObject $_ -Property Versions
    ForEach($Version in $Versions)
    {
        $VersionHistory =  New-Object PSObject
        $VersionHistory | Add-Member -MemberType NoteProperty -Name "Version ID" -Value ($Version.VersionId/512)
        $VersionHistory | Add-Member -MemberType NoteProperty -Name "Version Label" -Value $Version.VersionLabel
        $VersionHistory | Add-Member -MemberType NoteProperty -Name "Created On" -Value (Get-Date ($Version.Created) -Format "yyyy-MM-dd/HH:mm:ss")
 
        #Get Value of all other Fields
        $ListFields | ForEach-Object {            
            #Handle Special Fields
            $FieldType  = $_.TypeAsString 
            If($FieldType -eq "User" -or $FieldType -eq "UserMulti" -or $FieldType -eq "Lookup" -or $FieldType -eq "LookupMulti")
            {
                $FieldValue = $Version[$_.InternalName].LookupValue -join "; "
            }
            ElseIf($FieldType -eq "URL") #Hyperlink
            {
                $URL = $Version[$_.InternalName].URL
                $Description  = $Version[$_.InternalName].Description
                $FieldValue = "$URL, $Description"
            }
            ElseIf($FieldType -eq "TaxonomyFieldType" -or $FieldType -eq "TaxonomyFieldTypeMulti") #MMS
            {
                $FieldValue = $Version[$_.InternalName].Label -join "; "
            }
            Else
            {
                #Get Source Field Value
                $FieldValue = $Version[$_.InternalName]
            }
            #Append Version data
            $VersionHistory | Add-Member -MemberType NoteProperty -Name $_.InternalName -Value $FieldValue
        }
        $VersionHistoryData+=$VersionHistory
    }
}
$VersionHistoryData | Export-Csv $CSVPath -NoTypeInformation

不要忘记将变量 SiteURL、ListName 和 CSVPath 替换为与您的环境相关的值。