Export version history event log of SharePoint List

Question

I'm looking for an event log or full version history which holds all changes ever made to the items of a SharePoint List.

Version history is enabled. When I right-mouse click on an item and select Version History, it actually shows me all the data I need:

• Timestamp of modification
• (Column, value) pair of the new modified column and it's new value
• Modified by who

Now I "only" need to get this data structured in a table or a file (xlsx, xml, json, csv). And for all the List's items.

I can see that the data is there. But I haven't found a way to export it. So far I've tried customizing an .iqy file, using PowerShell and using Power Automate, without success.

I hope there is some way for me to get the full history. I need it to calculate average run times.

Answer 1

For SharePoint Online:

#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"
 
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
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
         
        #Get the List
        $List = $Ctx.Web.Lists.GetByTitle($ListName)
        $Ctx.Load($List)
        $Ctx.ExecuteQuery()
         
        #Get all items
        $Query = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()
        $ListItems = $List.GetItems($Query)
        $Ctx.Load($ListItems)
        $Ctx.ExecuteQuery()
 
        #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
                    }
                }
            }
        }
         
        #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://tenant.sharepoint.com"
$ListName="list name"
$CSVFile="C:\VersionHistory.csv"
 
#Call the function to generate version History Report
Export-VersionHistory -SiteURL $SiteURL -ListName $ListName -CSVFile $CSVFile

For SharePoint Server:

# ******* Variables Section ******************
#Define these variables 
$WebURL="site collection URL"
$ListName ="list name"
$ReportFile = "C:\VersionHistory.csv"
# *********************************************
 
#delete the file if exists
If (Test-Path $ReportFile)
 {
 Remove-Item $ReportFile
 }
 
#Get the Web and List
$Web = Get-SPWeb $WebURL
$List = $web.Lists.TryGetList($ListName) 
 
 #Check if list exists
 if($List -ne $null)
 {
  #Get all list items
  $ItemsColl = $List.Items
   
  #Write Report Header
  Add-Content -Path $ReportFile -Value "Item ID, Version Lable, Created by, Created at, Title"
  
  #Loop through each item
  foreach ($item in $ItemsColl) 
  {
      #Iterate each version
      ForEach($version in $item.Versions)
      {
         #Get the version content
         $VersionData = "$($item.id), $($version.VersionLabel), $($version.CreatedBy.User.DisplayName), $($version.Created), $($version['Title'])"
         #Write to report
         Add-Content -Path $ReportFile -Value $VersionData
       }
    }
 }
Write-Host "Version history has been exported successfully!"