SharePoint Online: Copy List Items from One List to Another using PowerShell

Requirement: Copy List items to another list in SharePoint Online.

How to copy list items to another list in SharePoint Online?

Do you need to copy list items from one list to another? Perhaps you need to migrate data from an old list to a new one. Or maybe you just need to quickly and easily move a few items between lists. In any case, if you’re looking for a way to copy items from one SharePoint Online list to another, we will show you how to use PowerShell to copy list items between SharePoint Online lists.

Use Quick Edit (Datasheet view) to copy-paste list items between lists in SharePoint Online. Make sure you have matching columns, and column order is the same in both views.

copy list items to another list sharepoint online
Important: Before proceeding with any of the script below, make sure you have cloned the existing list (save list as template!) and created a new list instance (without data). These scripts only copies data, but doesn’t create list!

You can also use: How to Create a New List from an Existing List in SharePoint Online?

SharePoint Online: Copy List Item to Another List using PowerShell

You can utilize this PowerShell script when you want to repeat copying items between SharePoint Online lists.

#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 Copy-ListItems()
{
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $SourceListName,
        [Parameter(Mandatory=$true)] [string] $TargetListName
    )    
    Try {
        #Setup Credentials to connect
        $Cred = Get-Credential
        $Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
    
        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Cred

        #Get the Source List and Target Lists
        $SourceList = $Ctx.Web.Lists.GetByTitle($SourceListName)
        $TargetList = $Ctx.Web.Lists.GetByTitle($TargetListName)
    
        #Get All Items from Source List
        $SourceListItems = $SourceList.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
        $Ctx.Load($SourceListItems)
        $Ctx.ExecuteQuery()
    
        #Get each column value from source list and add them to target
        ForEach($SourceItem in $SourceListItems)
        {
            $NewItem =New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
            $ListItem = $TargetList.AddItem($NewItem)
        
            #Map each field from source list to target list - INTERNAL NAMES
            $ListItem["Title"] = $SourceItem["Title"]
            $ListItem["IsActive"] = $SourceItem["IsActive"]
            $ListItem["ProjectStartDate"] = $SourceItem["ProjectStartDate"]
            $ListItem["Department"] = $SourceItem["Department"]
            $ListItem["Priority"] = $SourceItem["Priority"]
            $ListItem.update()
        }
        $Ctx.ExecuteQuery()

        write-host  -f Green "Total List Items Copied from '$SourceListName' to '$TargetListName' : $($SourceListItems.count)"
    }
    Catch {
        write-host -f Red "Error Copying List Items!" $_.Exception.Message
    }
}

#Set Parameters
$SiteURL= "https://crescent.sharepoint.com/"
$SourceListName="Projects Template"
$TargetListName="Project Innovate"

#Call the function to copy list items
Copy-ListItems -siteURL $SiteURL -SourceListName $SourceListName -TargetListName $TargetListName

This script copies all mapped column values from the source to the target list. Let’s enhance the script bit to automatically copy all columns matching from the source to the target list.

SharePoint Online: Copy List Items using PowerShell

Instead of hard-coding each field, let’s copy all field values between lists this time:

#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 Copy-ListItems()
{
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $SourceListName,
        [Parameter(Mandatory=$true)] [string] $TargetListName
    )    
    Try {
        #Setup Credentials to connect
        $Cred = Get-Credential
        $Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
    
        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Cred

        #Get the Source List and Target Lists
        $SourceList = $Ctx.Web.Lists.GetByTitle($SourceListName)
        $TargetList = $Ctx.Web.Lists.GetByTitle($TargetListName)
    
        #Get All Items from Source List
        $SourceListItems = $SourceList.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
        $Ctx.Load($SourceListItems)
        $Ctx.ExecuteQuery()
    
        #Get All fields from Source List & Target List
        $SourceListFields = $SourceList.Fields
        $Ctx.Load($SourceListFields)
        $TargetListFields = $TargetList.Fields
        $Ctx.Load($TargetListFields)        
        $Ctx.ExecuteQuery()

        #Get each column value from source list and add them to target
        ForEach($SourceItem in $SourceListItems)
        {
            $NewItem =New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
            $ListItem = $TargetList.AddItem($NewItem)        
 
            #Map each field from source list to target list
            Foreach($SourceField in $SourceListFields)
            {  
                #Skip Read only, hidden fields, content type and attachments
                If((-Not ($SourceField.ReadOnlyField)) -and (-Not ($SourceField.Hidden)) -and ($SourceField.InternalName -ne  "ContentType") -and ($SourceField.InternalName -ne  "Attachments") )  
                {
                    $TargetField = $TargetListFields | where { $_.Internalname -eq $SourceField.Internalname}
                    if($TargetField -ne $null)
                    {
                        #Copy column value from source to target
                        $ListItem[$TargetField.InternalName] = $SourceItem[$SourceField.InternalName]  
                    }
                }
            }
            $ListItem.update()
            $Ctx.ExecuteQuery()
        }

        write-host  -f Green "Total List Items Copied from '$SourceListName' to '$TargetListName' : $($SourceListItems.count)"
    }
    Catch {
        write-host -f Red "Error Copying List Items!" $_.Exception.Message
    }
}

#Set Parameters
$SiteURL= "https://crescent.sharepoint.com/"
$SourceListName="Projects Template"
$TargetListName="Project Innovate"

#Call the function to copy list items
Copy-ListItems -siteURL $SiteURL -SourceListName $SourceListName -TargetListName $TargetListName

PowerShell to Copy List Items with Attachments from One List to another List

Let’s copy items along with attachments from one list to another in SharePoint Online using PowerShell:

#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 Copy-ListItems()
{
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $SourceListName,
        [Parameter(Mandatory=$true)] [string] $TargetListName
    )    
    Try {
        #Setup Credentials to connect
        $Cred = Get-Credential
        $Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
    
        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Cred

        #Get the Source List and Target Lists
        $SourceList = $Ctx.Web.Lists.GetByTitle($SourceListName)
        $TargetList = $Ctx.Web.Lists.GetByTitle($TargetListName)
    
        #Get All Items from Source List
        $SourceListItems = $SourceList.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
        $Ctx.Load($SourceListItems)
        $Ctx.ExecuteQuery()
    
        Write-host "Total Number of List Items Found in the source:"$SourceListItems.count

        #Get All fields from Source List & Target List
        $SourceListFields = $SourceList.Fields
        $Ctx.Load($SourceListFields)
        $TargetListFields = $TargetList.Fields
        $Ctx.Load($TargetListFields)        
        $Ctx.ExecuteQuery()

        #Get each column value from source list and add them to target
        ForEach($SourceItem in $SourceListItems)
        {
            $NewItem =New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
            $ListItem = $TargetList.AddItem($NewItem)        
 
            #Map each field from source list to target list
            Foreach($SourceField in $SourceListFields)
            {  
                #Skip Read only, hidden fields, content type and attachments
                If((-Not ($SourceField.ReadOnlyField)) -and (-Not ($SourceField.Hidden)) -and ($SourceField.InternalName -ne  "ContentType") -and ($SourceField.InternalName -ne  "Attachments") )  
                {
                    $TargetField = $TargetListFields | where { $_.Internalname -eq $SourceField.Internalname}
                    if($TargetField -ne $null)
                    {
                        #Copy column value from source to target
                        $ListItem[$TargetField.InternalName] = $SourceItem[$SourceField.InternalName]  
                    }
                }
            }
            
            $ListItem.update()
            $Ctx.ExecuteQuery()

            #Copy attachments
            $AttachmentsColl = $SourceItem.AttachmentFiles
            $Ctx.Load($AttachmentsColl)
            $Ctx.ExecuteQuery()

            ForEach($Attachment in $AttachmentsColl)
            {
                Write-host "Copying attachment '$($Attachment.FileName)' from Item ID '$($SourceItem.ID)'"
                #Get attachment File from Source List Item
                $File = $Ctx.Web.GetFileByServerRelativeUrl($Attachment.ServerRelativeUrl)
                $Ctx.Load($File)
                $Ctx.ExecuteQuery()

                #Get the Source File Content
                $FileContent = $File.OpenBinaryStream()
                $Ctx.ExecuteQuery()
                $Buffer = New-Object Byte[]($File.length)
                $BytesRead = $FileContent.Value.Read($Buffer, 0, $Buffer.Length)
                $MemoryStream = New-Object -TypeName System.IO.MemoryStream(,$Buffer)

                #Add Attachment to Target List Item
                $AttachmentCreation = New-Object Microsoft.SharePoint.Client.AttachmentCreationInformation
                $AttachmentCreation.ContentStream = $MemoryStream
                $AttachmentCreation.FileName = $Attachment.FileName 
                [void]$ListItem.AttachmentFiles.Add($AttachmentCreation)
                $Ctx.ExecuteQuery()
                $MemoryStream.Close()
            }
            Write-host "Copied Item to the Target List:"$SourceItem.id -f Yellow
        }

        write-host  -f Green "Total List Items Copied from '$SourceListName' to '$TargetListName' : $($SourceListItems.count)"
    }
    Catch {
        write-host -f Red "Error Copying List Items!" $_.Exception.Message
    }
}

#Set Parameters
$SiteURL= "https://crescent.sharepoint.com/"
$SourceListName="Projects"
$TargetListName="Project Temp"

#Call the function to copy list items
Copy-ListItems -siteURL $SiteURL -SourceListName $SourceListName -TargetListName $TargetListName

Here is another post for SharePoint On-premises to copy list item: Copy SharePoint List Item to Another List using PowerShell

PnP PowerShell to Copy Items between SharePoint Online Lists

The above scripts work fine as long as the list fields are not people picker, lookup, Managed Metadata, or Hyperlink! So here is the script to handle all complex fields and copy list items:

#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 list items from one list to another
Function Copy-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
        $SourceListItemsCount= $SourceListItems.count
        Write-host "Total Number of Items Found:"$SourceListItemsCount        

        #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 $SourceListItems)
        {  
            $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 $($SourceListItemsCount))" -PercentComplete (($Counter / $SourceListItemsCount) * 100)
        
            #Copy column value from source to target
            $NewItem = Add-PnPListItem -List $TargetListName -Values $ItemValue

            #Copy Attachments
            Copy-SPOAttachments -SourceItem $SourceItem -TargetItem $NewItem

            Write-Host "Copied Item ID from Source to Target List:$($SourceItem.Id) ($($Counter) of $($SourceListItemsCount))"
            $Counter++
        }
    }
    Catch {
        Write-host -f Red "Error:" $_.Exception.Message 
    }
}

#Connect to PnP Online
Connect-PnPOnline -Url "https://crescent.sharepoint.com/sites/marketing/" -Credentials (Get-Credential)
$Context = Get-PnPContext

#Call the Function to Copy List Items between Lists
Copy-SPOListItems -SourceListName "Projects" -TargetListName "ProjectsArchive"

If you want to copy list items between sites, use: How to Copy List Items between Site Collections in SharePoint Online using PowerShell?

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

13 thoughts on “SharePoint Online: Copy List Items from One List to Another using PowerShell

Leave a Reply

Your email address will not be published. Required fields are marked *