SharePoint Online: Archive Large List Items to another List using PowerShell
Requirement: Archive a large list by moving old items to a new list to improve performance.
How to Archive List Items in SharePoint Online?
When it comes to managing your SharePoint Online lists, you may need to archive old or unused items from time to time. How do we archive old lists in SharePoint Online without deleting them? Luckily, this process can be easily accomplished using the built-in features of SharePoint. It turns out PowerShell also can archive a list in SharePoint Online. This guide will walk you through the steps necessary to archive list items in SharePoint Online. Here are the steps:
- Create a new list for the archive from the source list
- Move items from the source list to the archive list
To create the archive list, save the existing list as a template without content and create a new list for archival. You can also clone the list structure by going to Site Contents >> New >> List >> From Existing List” feature in SharePoint Online.
To move list items from the source list with the web browser interface, use the “Site Content and Structure” page to move list items in SharePoint Online. You can access it with the URL: https://tenant.sharepoint.com/_layouts/15/sitemanager.aspx, E.g., https://crescent.sharepoint.com/sites/marketing/_layouts/15/sitemanager.aspx
PowerShell to Archive List Items in SharePoint Online:
Archiving list items in SharePoint Online is a useful process to help keep your site organized and improve performance. By moving older or less frequently used items to an archive location, you can help to streamline your site and reduce clutter. Let’s see how to archive list items in SharePoint Online using PowerShell.
Assuming you have the archive list ready, this script moves all list items created 90+ days ago in a SharePoint Online list to another list.
#Function to copy attachments between list items
Function Copy-SPOAttachments($SourceItem, $TargetItem)
{
Try {
#Get All Attachments from Source
$Attachments = Get-PnPProperty -ClientObject $SourceItem -Property "AttachmentFiles"
$Attachments | ForEach-Object {
#Download the Attachment to Temp
$File = Get-PnPFile -Url $_.ServerRelativeUrl -FileName $_.FileName -Path $env:TEMP -AsFile -force
#Add Attachment to Target List Item
$FileStream = New-Object IO.FileStream(($env:TEMP+"\"+$_.FileName),[System.IO.FileMode]::Open)
$AttachmentInfo = New-Object -TypeName Microsoft.SharePoint.Client.AttachmentCreationInformation
$AttachmentInfo.FileName = $_.FileName
$AttachmentInfo.ContentStream = $FileStream
$AttachFile = $TargetItem.AttachmentFiles.add($AttachmentInfo)
$Context.ExecuteQuery()
#Delete the Temporary File
Remove-Item -Path $env:TEMP\$($_.FileName) -Force
}
}
Catch {
write-host -f Red "Error Copying Attachments:" $_.Exception.Message
}
}
#Function to Archive items from one list to another
Function Archive-SPOListItems()
{
param
(
[Parameter(Mandatory=$true)] [string] $SourceListName,
[Parameter(Mandatory=$true)] [string] $TargetListName
)
Try {
#Get All Items from the Source List in batches
Write-Progress -Activity "Reading Source..." -Status "Getting Items from Source List. Please wait..."
$SourceListItems = Get-PnPListItem -List $SourceListName -PageSize 500
Write-host "Total Number of Items Found:"$SourceListItems.count
#Filter List Items from the source - More than 90 days old!
$TimeStamp = (Get-Date).AddDays(-90)
$FilteredSourceListItems = $SourceListItems | Where {$_["Created"] -Lt $TimeStamp}
Write-Progress -Activity "Filtering List:" -Status "Filtering Items from Source List, Please wait..."
$FilteredSourceListItemsCount= $FilteredSourceListItems.count
Write-host "Total Number of Items After Filter:"$FilteredSourceListItemsCount
#Get fields to Update from the Source List - Skip Read only, hidden fields, content type and attachments
$SourceListFields = Get-PnPField -List $SourceListName | Where { (-Not ($_.ReadOnlyField)) -and (-Not ($_.Hidden)) -and ($_.InternalName -ne "ContentType") -and ($_.InternalName -ne "Attachments") }
#Loop through each item in the source and Get column values, add them to target
[int]$Counter = 1
ForEach($SourceItem in $FilteredSourceListItems)
{
$ItemValue = @{}
#Map each field from source list to target list
Foreach($SourceField in $SourceListFields)
{
#Check if the Field value is not Null
If($SourceItem[$SourceField.InternalName] -ne $Null)
{
#Handle Special Fields
$FieldType = $SourceField.TypeAsString
If($FieldType -eq "User" -or $FieldType -eq "UserMulti" -or $FieldType -eq "Lookup" -or $FieldType -eq "LookupMulti") #People Picker or Lookup Field
{
$LookupIDs = $SourceItem[$SourceField.InternalName] | ForEach-Object { $_.LookupID.ToString()}
$ItemValue.add($SourceField.InternalName,$LookupIDs)
}
ElseIf($FieldType -eq "URL") #Hyperlink
{
$URL = $SourceItem[$SourceField.InternalName].URL
$Description = $SourceItem[$SourceField.InternalName].Description
$ItemValue.add($SourceField.InternalName,"$URL, $Description")
}
ElseIf($FieldType -eq "TaxonomyFieldType" -or $FieldType -eq "TaxonomyFieldTypeMulti") #MMS
{
$TermGUIDs = $SourceItem[$SourceField.InternalName] | ForEach-Object { $_.TermGuid.ToString()}
$ItemValue.add($SourceField.InternalName,$TermGUIDs)
}
Else
{
#Get Source Field Value and add to Hashtable
$ItemValue.add($SourceField.InternalName,$SourceItem[$SourceField.InternalName])
}
}
}
#Copy Created by, Modified by, Created, Modified Metadata values
$ItemValue.add("Created", $SourceItem["Created"])
$ItemValue.add("Modified", $SourceItem["Modified"])
$ItemValue.add("Author", $SourceItem["Author"].Email)
$ItemValue.add("Editor", $SourceItem["Editor"].Email)
Write-Progress -Activity "Copying List Items:" -Status "Copying Item ID '$($SourceItem.Id)' from Source List ($($Counter) of $($FilteredSourceListItemsCount))" -PercentComplete (($Counter / $FilteredSourceListItemsCount) * 100)
#Copy column value from source to target
$NewItem = Add-PnPListItem -List $TargetListName -Values $ItemValue
#Copy Attachments
Copy-SPOAttachments -SourceItem $SourceItem -TargetItem $NewItem
#Delete the Item in the Source List
Remove-PnPListItem -List $SourceListName -Identity ($SourceItem.Id) -Recycle -Force
Write-Host "Archived Item '$($SourceItem.Id)' from Source to Target List ($($Counter) of $($FilteredSourceListItemsCount))"
$Counter++
}
}
Catch {
Write-host -f Red "Error:" $_.Exception.Message
}
}
#Connect to PnP Online
Connect-PnPOnline -Url "https://crescent.sharepoint.com/sites/Projects/" -Interactive
$Context = Get-PnPContext
#Call the Function to Copy List Items between Lists
Archive-SPOListItems -SourceListName "Projects" -TargetListName "ProjectsArchive"
You can run the script on-demand or as part of a scheduled task.
In summary, archiving list items in SharePoint Online using PowerShell is a quick and efficient way to keep your site organized. By using the script provided in this tutorial, you can automate the process of moving older or less frequently used items to an archive location, improving site performance and reducing clutter.
I need to create a new User type field in the archive list to preserve the original Created By owner. The Created By field changes in the archive list when the items are copied. This breaks the views on the archive list. The new User type field must retain the original owner so the views can be remapped to the original owner instead of the one running powershell.
Can this be done?
Sure! The script is updated to preserve the “Created By”, “Modified By”, “Created”, and “Modified” metadata field values.
Hi,
Thanks for your great website.
Is it possible to archive two lists linked via lookup together so that the relationship is retained?
I’m not sure if I exactly got your requirement. But if a lookup field is broken, you can fix it again: How to Fix Lookup Field using SharePoint Online using PowerShell?