SharePoint Online: Archive Large List Items to another List using PowerShell

Requirement: Archive a large list by moving old items to a new list, in order to improve performance.

How to Archive List Items in SharePoint Online?
You can 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
archive sharepoint online list to another list
Make sure the archive list is in place! You can save the existing list as a template without content and then create a new list for archival. You can also clone list structure by going to : Site Contents >> New >> List >> From Existing List" feature in SharePoint Online

PowerShell to Archive List Items in SharePoint Online:
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])
                    }
                }
            }
            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/" -UseWebLogin
$Context = Get-PnPContext

#Call the Function to Copy List Items between Lists
Archive-SPOListItems -SourceListName "Projects" -TargetListName "ProjectsArchive"
SharePoint Online: Archive Large List Items to another List using PowerShell SharePoint Online: Archive Large List Items to another List using PowerShell Reviewed by Salaudeen Rajack on March 16, 2019 Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.