Export SharePoint List Item Version History to Excel using PowerShell
Want to extract the version history of a SharePoint list to Excel for further analysis? Well, I wrote a PowerShell script and C# object model codes to generate a SharePoint version history report in SharePoint document version history report. Related to that, I got a new requirement to export version history to excel.
This version history report helps to compare what data is changed, who changed the list data, etc., over time. Just replace the values of WebURL, and ListName variables with your Site/List values and run the script to export version history from SharePoint 2013.
PowerShell script to Export SharePoint List version history to excel:
Here is how to export version history from the SharePoint Online list.
# ******* Variables Section ******************
#Define these variables
$WebURL="https://sharepoint.crescent.com/sites/Sales/"
$ListName ="Invoice"
$ReportFile = "D:\Invoice_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!"
Please note, in lines 25 and 36, I’ve added only the “Title” field from the list. You may want to add additional fields as per your requirement. On running the script, all list item versions will be exported to Excel (CSV format).
To export SharePoint document version history, Refer: Extract and Download All Document Versions using PowerShell
How do I get the content type assigned in each version?
How do I get version comments from versions?
Use: $Item.Versions.GetVersionFromLabel($version.VersionLabel).CheckInComment
Where do you put this code?
Worked like a charm, now only to add this as a button in the ribbon for end users to use
How can we trigger this script to run on the button click?
I wanted to find version history for auditing purpose, thanks to the above powershell script, it got me half way. Thought I might share as it might be useful to someone else.
In the below I wanted to know when was the field of “consult status” changed. I omit customer details due to privacy reason. From the version history we can see when consult status has changed.
version
4.0 17/09/2013 11:38 presence information
CONSULT STATUS COMPLETED
3.0 17/09/2013 11:34
CONSULT REASON FOR REVIEW
2.0 17/09/2013 11:28
CONSULT STATUS REVIEWED
1.0 17/09/2013 10:35
—
I modified the above script for my own environment, sharepoint 2010.
# ******* Variables Section ******************
#Define these variables
$WebURL= “https://sharepoint/ “;
$ListName =”CONSULT”
$ReportFile = “c:tempVersionHistory.csv”
# *********************************************
#delete 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;
$versionColl = $ItemColl.Version;
#Write Report Header
Add-Content -Path $ReportFile -Value “status change, version history, version date, customer, customernumber, admission date, req unit, consult unit”
#Loop through each item
foreach ($item in $ItemsColl)
{
# I need to find out what these column are, it is for debugging purpose
#write-host $item[0];
#write-host $item[1];
#write-host $item[19];
#write-host $item[20];
#$ItemData = “$($item.id), $($item[0]), $($item[1]), $($item[19]), $($item[20]), $($item.”Consult Requesting Unit”))”
#Iterate each version
foreach($version in $item.Versions)
{
#Get the version content
#$VersionData = “$($item.id), $($version.VersionLabel), $($version.CreatedBy.User.DisplayName), $(Get-Date ($version.Created) -Format “yyyy-MM-dd-HH:mm:ss”), $($version.ListItem.Title),$($version.Field.Modified)”
#Write to report
#Add-Content -Path $ReportFile -Value $VersionData
#write-host $VersionData;
foreach ($field in $version.Fields)
{
if ($field.Title -eq “CONSULT STATUS”) # I only wanted consult status
{
if ( $(Get-Date ($version.Created) -Format “yyyy”) -eq ‘2017’ ) # as the data is too large, filter for 2017
{
$fieldstatus = $field.GetFieldValueAsText($version[$field.Title]);
$customertile1 = $($version.ListItem.Title) -replace ‘,’, ‘ ‘; # replace commas as it is bad for csv
$VersionData = “$fieldstatus , $($version.VersionLabel), $(Get-Date ($version.Created) -Format “yyyy-MM-dd-HH:mm:ss”), $customertile1, $($item[1]), $(Get-Date ($item[7]) -Format “yyyy-MM-dd-HH:mm:ss”), $($item[15]), $($item[19])”;
Add-Content -Path $ReportFile -Value $VersionData;
#write-host $VersionData;
}
}
}
}
# Add-Content -Path $ReportFile -Value $ItemData
}
}
Write-Host “Version history has been exported successfully!”
result of extract to csv will look something like below. It gave me my audit trail for when consult status changed.
status change version history version date customer customerid
COMPLETED 7 2017-01-03-03:39:58 fred 1105484
COMPLETED 6 2017-01-03-03:39:58 fred 1105484
COMPLETED 5 2017-01-03-03:39:57 fred 1105484
REVIEWED 4 2017-01-03-00:55:59 fred 1105484
REVIEWED 3 2017-01-03-00:55:59 fred 1105484
NEW 2 2017-01-01-00:56:47 fred 1105484
NEW 1 2017-01-01-00:56:42 fred 1105484
This is a great script and it has helped me quite a bit. But does anyone know how I can leverage this script to export all the custom tags/columns that were modified with each version? Let’s say we have a list with 10 custom tags and for each version, we want to export the tags that were changed.
how about SP 2010?
This script works on SharePoint 2010 Also!
Fining this resource was GREAT!!! Thanks for the share. Although, I tried to get this to work on SharePoint Online and it will not. I think there may not be a command equivalent to Get-SPWeb. Is there another way to get this accomplished? Maybe via RESTful API
https://msdn.microsoft.com/en-us/library/office/jj860569(v=office.15).aspx
What do you think? alanshieldsjr at live dot com