How to Copy List Items to Another Site in Sharepoint

How to Copy List Items to Another Site in SharePoint Online

Are you looking for a way to copy list items from one SharePoint site to another?

Copying one list item or two by manually typing them out is not really a problem. Unfortunately, that is not the case if there are hundreds or even thousands of list items.

The good news is, it’s not really that hard to do. In this article, you will learn how to copy list items to another site in SharePoint.

Let’s get started.

Step 1: Create a List From an Existing Template

This step is important since you will be creating a copy of the list without the contents on another site.

To get started, simply navigate to the site where you want to copy the list items to.

On the home page, add a new list by clicking the “New” button on the menu and selecting the “List” option.

How to add a new list in SharePoint

Then, select the option to create a list from an existing list.

Simply select the site and the specific list you want to copy and click the “Next” button.

Select the site and list to make a template from

You will then have to enter the name for the list. Feel free to add some description like mentioning where the list was copied from.

After that, you will have a copy of that list on your other site with the same columns and settings.

Sign up for exclusive updates, tips, and strategies

    Step 2: Copy List Items Using PowerShell

    This step is a little complicated since there’s code involved. However, all you need to do is copy the codes below and change the parameters for the web URL, the source list name, and the target list name.

    If you didn’t follow the first step since you already created a separate list, make sure that both lists (source and target) have the same columns. Also, be sure to open PowerShell as an administrator.

    Now, if all is well, copy the script below to copy all the list items from one list to another but make sure to modify the configuration variables (source):

    #Option 1:
    
    Install-Module -Name PnP.PowerShell
    
    #Connect to the Source Site
    Connect-PnPOnline -Url https://[tenantnamehere].sharepoint.com/sites/[source site name]
    
    #Create the Template
    Get-PnPSiteTemplate -Out C:\Temp\Lists.xml -ListsToExtract "List 1", "List 2" -Handlers Lists
    
    Get the List Data 
    Add-PnPDataRowsToSiteTemplate -Path C:\Temp\Lists.xml -List "List 1"
    Add-PnPDataRowsToSiteTemplate -Path C:\Temp\Lists.xml -List "List 2"
    
    #Connect to Target Site
    Connect-PnPOnline -Url https://[tenantnamehere].sharepoint.com/sites/[destinationsite name]
    
    #Apply the Template
    Invoke-PnPSiteTemplate -Path "C:\Temp\Lists.xml"

    If the list items you want to copy have attachments, use this code (source):

    #Option 2:
    
    #Function to copy attachments between list items
    Function Copy-SPOAttachments()
    {
        param
        (
            [Parameter(Mandatory=$true)] [Microsoft.SharePoint.Client.ListItem] $SourceItem,
            [Parameter(Mandatory=$true)] [Microsoft.SharePoint.Client.ListItem] $DestinationItem
        )
        Try {
            #Get All Attachments from Source list items
            $Attachments = Get-PnPProperty -ClientObject $SourceItem -Property "AttachmentFiles"
            $Attachments | ForEach-Object {
                #Download the Attachment to Temp
                $File  = Get-PnPFile -Connection $SourceConn -Url $_.ServerRelativeUrl -FileName $_.FileName -Path $Env:TEMP -AsFile -force
                #Add Attachment to Destination 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 = $DestinationItem.AttachmentFiles.Add($AttachmentInfo)
                Invoke-PnPQuery -Connection $DestinationConn
           
                #Delete the Temporary File
                Remove-Item -Path $Env:TEMP\$($_.FileName) -Force
            }
        }
        Catch {
            write-host -f Red "Error Copying Attachments:" $_.Exception.Message
        }
    }
      
    #Function to copy list items from one list to another
    Function Copy-SPOListItems()
    {
        param
        (
            [Parameter(Mandatory=$true)] [Microsoft.SharePoint.Client.List] $SourceList,
            [Parameter(Mandatory=$true)] [Microsoft.SharePoint.Client.List] $DestinationList
        )
        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 $SourceList -PageSize 500 -Connection $SourceConn
            $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 $SourceList -Connection $SourceConn | 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 Destination
            [int]$Counter = 1
            ForEach($SourceItem in $SourceListItems)
            { 
                $ItemValue = @{}
                #Map each field from source list to Destination 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") #People Picker Field
                        {
                            $PeoplePickerValues = $SourceItem[$SourceField.InternalName] | ForEach-Object { $_.Email}
                            $ItemValue.add($SourceField.InternalName,$PeoplePickerValues)
                        }
                        ElseIf($FieldType -eq "Lookup" -or $FieldType -eq "LookupMulti") # 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 $($SourceListItemsCount))" -PercentComplete (($Counter / $SourceListItemsCount) * 100)
                 
                #Copy column value from Source to Destination
                $NewItem = Add-PnPListItem -List $DestinationList -Values $ItemValue
       
                #Copy Attachments
                Copy-SPOAttachments -SourceItem $SourceItem -DestinationItem $NewItem
       
                Write-Host "Copied Item ID from Source to Destination List:$($SourceItem.Id) ($($Counter) of $($SourceListItemsCount))"
                $Counter++
            }
        }
        Catch {
            Write-host -f Red "Error:" $_.Exception.Message
        }
    }
       
    #Set Parameters
    $SourceSiteURL = "https://[tenantnamehere].sharepoint.com/sites/[sitenamehere]"
    $SourceListName = "[listnamehere]"
      
    $DestinationSiteURL = "https://[tenantnamehere].sharepoint.com/sites/[sitenamehere]"
    $DestinationListName = "[listnamehere]"
      
    #Connect to Source and destination sites
    $SourceConn = Connect-PnPOnline -Url $SourceSiteURL -Interactive -ReturnConnection
    $SourceList = Get-PnPList -Identity $SourceListName -Connection $SourceConn
      
    $DestinationConn = Connect-PnPOnline -Url $DestinationSiteURL -Interactive -ReturnConnection
    $DestinationList = Get-PnPList -Identity $DestinationListName -Connection $DestinationConn
       
    #Call the Function to Copy List Items between Lists
    Copy-SPOListItems -SourceList $SourceList -DestinationList $DestinationList

    That’s it! If you’re confused with something along the way, don’t hesitate to drop a comment with your questions. You can also reach me directly by sending me a message on my contact page.

    About Ryan Clark

    As the Principal Solutions Architect at Mr. SharePoint, I help companies of all sizes better leverage Modern Workplace and Digital Process Automation investments. I am also a Microsoft Most Valued Professional (MVP) for Office Apps & Services.

    Subscribe
    Notify of
    guest
    8 Comments
    Oldest
    Newest Most Voted
    Inline Feedbacks
    View all comments

    Mohammed Hassan
    Mohammed Hassan
    8 months ago

    I need a way to copy all list items from one list to another with list item comments.

    Adel Brown
    Adel Brown
    4 months ago

    Thanks for the info. Seemed like a good solution but unfortunately, I ran into permission errors constantly along the way. When you connect to the PnpOnline module using Connect-PnPOnline make sure you add “-interactive” at the end or else the module will never authenticate to the O365 tenant. Then, even after that I kept getting 403 forbidden errors and others. I tried giving the app admin permissions but that did not help.

    Adel Brown
    Adel Brown
    Reply to  Adel Brown
    4 months ago

    I finally got this working and it worked well. Just got to make sure to have proper admin access and give the PnPmodule proper admin access as well.

    SPAdam
    SPAdam
    4 months ago

    Giving credit to the second script owner would be appreciated Ryan!

    8
    0
    Would love your thoughts, please comment.x
    ()
    x
    Scroll to Top
    Tweet
    Share
    Share
    Pin