Intro
A SharePoint list was filling up rapidly and had exceeded 5,000 items. Not wanting to use filtered views, the user instead wanted to archive the older items. This seemed like the perfect automation nut to to crack open with the PowerShell ISE editor. Keep reading to learn how to archive a SharePoint list.
The rate at which users were entering items required an archive done every quarter. From a high level this seemed straight forward:
- Connect to the site
- For each year, going back 10
- And for each quarter
- Query the list for items to archive
- Create an archive list if necessary
- Move the items to the archive list
- And for each quarter
Connect to the site
Let’s begin by connecting to the site there are two ways to do this either by hard coded values or passing in the site collection and web site as parameters to the script. You’ll notice the very first line of the script deals with parameters. There are four parameters for this script:
- $sitePath: which is the site collection for the list
- $webPath: which is the relative path for any sites or sub-sites within the site collection
- $sourceList: which is the list that needs to be archived
- $destListPrefix: which is the list we write archived items to
The first line of the script:
[powershell]
param(
[string]$sitePath = “http://sharepoint1”,
[string]$webPath = “”,
[string]$sourceList = “Supervisor Log”,
[string]$destListPrefix = “Supervisor Log”)
[/powershell]
And the actual connection:
[powershell]
$site = new-object Microsoft.SharePoint.SPSite($sitePath)
$web = $site.AllWebs | where { $_.name -eq $webPath}
[/powershell]
Set archive date variables
The main code path is wrapped in a try catch block near the end of the script. The code block gets the current year and month and then calls a function to determine what the archived date variables are. The setArchiveDateVars function has five parameters: current year, current month, archive year, archive month, and archive quarter. The archive parameters are passed by reference. Below is the source code for that function, you can see how it handles moving back to the previous month, previous quarter and previous year.
[powershell]
function SetArchiveDateVars([int]$curYr, [int]$curMonth,
[ref]$archYr, [ref]$archMonth, [ref]$archQtr)
{
#Set archive quarter, year and month
if ($curMonth -lt 4)
{
$archYr.Value = $curYr – 1
$archMonth.Value = 12
$archQtr.Value = 4
}
elseif (3 -lt $curMonth -and $curMonth -lt 7)
{
$archYr.Value = $curYr
$archMonth.Value = 3
$archQtr.Value = 1
}
elseif (6 -lt $curMonth -and $curMonth -lt 10)
{
$archYr.Value = $curYr
$archMonth.Value = 6
$archQtr.Value = 2
}
elseif (9 -lt $curMonth)
{
$archYr.Value = $curYr
$archMonth.Value = 9
$archQtr.Value = 3
}
}
[/powershell]
For each year and quarter
After setting the archived date variables, the script sets the $site, $web and listA objects. $listA is the list to archive. Once we have set those SharePoint objects in our script, we begin looping backward on the year and then quarter.
[powershell]
try
{
…
#Loop thru years
for ($archYr = $archYr; ($curYr – $archYr) -lt 10; $archYr–)
{
#Loop thru quarters
for ($qtrCnt = 1; $qtrCnt -lt 5; $qtrCnt++)
{
…
[/powershell]
Query the list for items to archive
For each quarter we retrieve list items by using the GetItemsToMigrate function. This function uses SPQuery to filter list items.
[powershell]
<# Returns: SPList of items between $startDate and $endDate Globals Used: $listA #>
function GetItemsToMigrate([string]$startDate, [string]$endDate)
{
$spQuery = new-object Microsoft.SharePoint.SPQuery
$camlQuery =
‘<Where>
<And>
<Leq>
<FieldRef Name=”MyCreated” />
<Value Type=”DateTime” IncludeTimeValue=”False”>’ + $endDate + ‘</Value>
</Leq>
<Geq>
<FieldRef Name=”MyCreated” />
<Value Type=”DateTime” IncludeTimeValue=”False”>’ + $startDate + ‘</Value>
</Geq>
</And>
</Where>’
$spQuery.Query = $camlQuery
$localItems = $listA.GetItems($spQuery)
, $localItems
}
[/powershell]
The , at the front of the last line ensures only the list of $localItems is returned from the function.
Create an archive list if necessary
Now that we have a list of items to archive, we can create an archive list and move those items to that list. We only create a list when it doesn’t already exist, we check this with the TryGetList function.
We create the archive list by saving the source list has a template, then creating a new list from that template and finally adding fields to handle CreatedBy, ModifiedBy, Created and Modified.
[powershell]
function CreateList([string]$destListName, [string]$destListDesc)
{
#Make Template
$sourceList = $web.Lists[$sourceListName]
$templateName = $sourceList.Title
$templateFileName = $sourceList.Title
$sourceList.SaveAsTemplate($templateFileName, $templateName, $sourceList.Description, 0)
#Add List
$listTemplate = $site.GetCustomListTemplates($web)[$templateName]
$web.Lists.Add($destListName, $destListDescription, $listTemplate)
$web.Update()
#Remove Template
$listTemplates = $site.RootWeb.Lists[“List Template Gallery”]
$listTemplateItems = $listTemplates.Items
foreach($listTemplateItem in $listTemplateItems)
{
if($listTemplate.Name.Equals($listTemplateItem[“Title”]))
{
$listTemplateItem.Delete()
break
}
}
#Add Columns to List
$listNew =$web.Lists[$destListName]
$listNew.Fields.Add(“Orig Created”, [Microsoft.SharePoint.SPFieldType]::DateTime, $False)
$listNew.Fields.Add(“Orig Created By”, [Microsoft.SharePoint.SPFieldType]::User, $False)
$listNew.Fields.Add(“Orig Modified”, [Microsoft.SharePoint.SPFieldType]::DateTime, $False)
$listNew.Fields.Add(“Orig Modified By”, [Microsoft.SharePoint.SPFieldType]::User, $False)
$listNew.Update()
}
[/powershell]
Move the items to the archive list
The move is a two step process, copy the item to the archive list then delete the item from the source list. I use the CopyItem function to:
- Copy each field
- Handle read only fields
- Handle the CreatedBy, ModifiedBy, Created dates and Modified dates
- Handle file attachments
Files attached to an item are handled in the CopyItem function with this block of code:
[powershell]
#Handle Attachments
foreach($leafName in $item.Value.Attachments)
{
$spFile = $web.GetFile($($item.Value.Attachments.UrlPrefix + $leafName));
$newItem.Attachments.Add($leafName, $spFile.OpenBinary());
}
[/powershell]
Then we decrement archive quarter, making adjustments for the previous year when necessary, and continue the looping.
The Full script file link below contains the entire script needed to archive a SharePoint list. Enjoy the script and feel free to comment.