Have you ever found yourself in need of duplicating a column from one SharePoint list to another?

Perhaps you’re aiming to use the same column across multiple lists, replicate data between lists, or facilitate the transfer of information from one list to another.

There are several methods available to accomplish this task effectively. Let’s explore the various approaches you can employ to copy columns between SharePoint lists.

To copy a column, including its content, in a SharePoint list, you can follow these steps:

1. Navigate to the SharePoint site where your list is located.
2. Once in your list, click on the gear icon in the top right corner and select List settings from the dropdown menu.

3. In the List Settings page, locate the column you want to copy under the Columns section and click on it.
4. In the settings for the selected column, you should see an option to Save this column as a template. Click on it to save the column as a template. This action will essentially create a copy of the column with all its settings and content.
5. You’ll be prompted to provide a name for the column template. Enter a descriptive name for the template to easily identify it later.
6. After naming the column template, click OK or Save to save the template.
7. Access Site Content Types (Optional): If you want to use this column in other lists or libraries within the site, you can navigate to Site Settings and then click on Site content types under the Web Designer Galleries section.
8. Add Column to Other Lists (Optional): Locate the content type associated with the list where you want to add the copied column. Click on the content type, then select Add from existing site columns. You should see your copied column template listed there. Select it and add it to the content type.

By following these steps, you can copy a column, including its content, in a SharePoint list and optionally add it to other lists within the same site.

How to Copy a SharePoint Online List

Modern SharePoint Online sites offer a convenient method for duplicating lists from existing ones. Here’s a step-by-step guide on how to create a copy of a list in SharePoint Online:

To begin, access the SharePoint site where the source list is located.

1. From the Home page, locate and click on the New button.
2. In the dropdown menu that appears, select List from the options provided. You can also go to the Site Contents Page (Click on Settings gear >> Choose Site Contents), click on the New menu, and choose List.

3. This opens a new list creation page, which provides an option to create a new list from the existing list. Create a list page. Click on the From existing list button on the page.

4. Select the source list to duplicate and click on the Next button on the bottom to copy the existing list structure. You can pick a list from different site collections even

5. Assign a name to your newly created list, and click on Create to complete the process.

The new list will be created with all of the same fields as the original list. The above method copies the structure of the list without copying any data from it.

Copy a List or Document Library using PnP PowerShell

Using the Copy-PnPList cmdlet to duplicate a list or document library within SharePoint Online.

This command duplicates the fields, views, and settings of the specified list; however, it does not include the list items.
Similarly, to copy a list to another site, use:

# Parameters
$SiteURL = "https://3xrmw4.sharepoint.com/_layouts/15/sharepoint.aspx"
$SourceListName = "Documents"

$DestinationSiteURL = "https://3xrmw4.sharepoint.com/sites/SampleTeamSiteNew/Lists/6000%20Items%20List?env=WebViewList"
$DestinationListName = "Documents Backup"

Try {
    # Connect to PnP Online
    Connect-PnPOnline -Url $SiteURL -Interactive

    # Copy list to another site using PowerShell
    Copy-PnPList -Identity $SourceListName -Title $DestinationListName -DestinationWebUrl $DestinationSiteURL
}
Catch {
    Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red
}

The above method copies the document directory of the list.

Copy List from one SharePoint site to another using PowerShell

Using PowerShell, you can easily automate copying a SharePoint list from one site to another, including its content. This method saves time and minimizes errors. Consider the lines of code below:

$SourceSiteURL = "https://3xrmw4.sharepoint.com/_layouts/15/sharepoint.aspx"
$TargetSiteURL = "https://3xrmw4.sharepoint.com/sites/Interns"
$ListName = "Projects"
$TemplateFile = "$env:TEMP\Template.xml"

# Connect to the Source Site
Connect-PnPOnline -Url $SourceSiteURL -Interactive

# Create the Template
Get-PnPSiteTemplate -Out $TemplateFile -ListsToExtract $ListName -Handlers Lists

# Get Data from source List
Add-PnPDataRowsToSiteTemplate -Path $TemplateFile -List $ListName

# Connect to Target Site
Connect-PnPOnline -Url $TargetSiteURL -Interactive

# Apply the Template
Invoke-PnPSiteTemplate -Path $TemplateFile

Ensure to copy the parent list first if your list includes any lookup fields. Failing to do so may result in encountering the Invoke-PnPSiteTemplate Value does not fall within the expected range error.

How to use PowerShell to Copy Columns from One List to Another

Using the web UI to recreate columns could be cumbersome if you have many columns to copy; It’s easy to do it with PowerShell script.

# Parameters
$SourceSiteUrl = "https://crescent.sharepoint.com/sites/Retail" 
$DestinationSiteUrl = "https://crescent.sharepoint.com/sites/Sales"
$SourceListName = "Projects"
$DestinationListName = "Projects"
$FieldsToCopy = "ProjectName", "ProjectDescription", "ProjectManager", "Department" # Internal Names
 
# Connect to the Source site
Connect-PnPOnline -Url $SourceSiteUrl -Interactive
  
# Get All Fields from the Source List
$SourceListFields = Get-PnPField -List $SourceListName
 
# Connect to Destination site
Connect-PnPOnline -Url $DestinationSiteUrl -Interactive
 
# Get All Fields from the Destination List
$DestinationListFields = Get-PnPField -List $DestinationListName
 
# Copy columns from the Source List to Destination List
ForEach ($Field in $FieldsToCopy) {
    # Check if the destination list has the field already
    $DestinationFieldExist = ($DestinationListFields | Select -ExpandProperty InternalName).Contains($Field)
    If ($DestinationFieldExist -eq $false) {
        # Get the field to copy
        $SourceField = $SourceListFields | Where {$_.InternalName -eq $Field}
        If ($SourceField -ne $Null) {
            Add-PnPFieldFromXml -List $DestinationListName -FieldXml $SourceField.SchemaXml | Out-Null
            Write-Host "Copied Field from Source to Destination List: $Field" -ForegroundColor Green
        }
        Else {
            Write-Host "Field '$Field' does not Exist in the Source List!" -ForegroundColor Yellow
        }
    }
    Else {
        Write-Host "Field '$Field' Already Exists in the Destination List!" -ForegroundColor Yellow
    }
}

Here’s how to copy all fields, excluding those marked as Read-Only, Hidden, and some special fields. Use the code below:

$FieldsToCopy = Get-PnPField -List $SourceListName | Where {-not ($_.ReadOnlyField) -and (-Not ($_.Hidden)) -and ($_.InternalName -ne  "ContentType") -and ($_.InternalName -ne  "Attachments") } | Select -ExpandProperty InternalName

Note: The methods described above only copy the columns from one list to another.

How to copy the values of columns from one SharePoint list to another

Below is a PowerShell script for copying column values between lists:

# Parameters
$SourceSiteUrl = "https://3xrmw4.sharepoint.com/_layouts/15/sharepoint.aspx" 
$DestinationSiteUrl = "https://3xrmw4.sharepoint.com/sites/Interns"
$SourceListName = "Projects"
$DestinationListName = "ProjectsV2"
$FieldsToCopy = "ProjectName", "ProjectDescription", "ProjectManager", "Date", "Department"
 
# Connect to Source site
Connect-PnPOnline -Url $SourceSiteUrl -Interactive
  
# Get All Items from the Source List
$SourceListItems = Get-PnPListItem -List $SourceListName -Fields $FieldsToCopy -PageSize 2000
 
# Connect to Destination site
Connect-PnPOnline -Url $DestinationSiteUrl -Interactive
 
# Copy Items from the Source to Destination
[int]$Counter = 1
ForEach ($ListItem in $SourceListItems) {
    $ItemValue = @{}
    ForEach ($Field in $FieldsToCopy) {
        # Check if the Field value is not Null
        If ($ListItem[$Field] -ne $Null) {
            # Handle Special Fields
            $FieldType = (Get-PnPField -List $SourceListName -Identity $Field).TypeAsString
   
            If ($FieldType -eq "User" -or $FieldType -eq "UserMulti" -or $FieldType -eq "Lookup" -or $FieldType -eq "LookupMulti") { # People Picker or Lookup Field
                $LookupIDs = $ListItem[$Field] | ForEach-Object { $_.LookupID.ToString() }
                $ItemValue.Add($Field, $LookupIDs)
            }
            ElseIf ($FieldType -eq "URL") { # Hyperlink
                $URL = $ListItem[$Field].URL
                $Description = $ListItem[$Field].Description
                $ItemValue.Add($Field, "$URL, $Description")
            }
            ElseIf ($FieldType -eq "TaxonomyFieldType" -or $FieldType -eq "TaxonomyFieldTypeMulti") { # MMS
                $TermGUIDs = $ListItem[$Field] | ForEach-Object { $_.TermGuid.ToString() }                   
                $ItemValue.Add($Field, $TermGUIDs)
            }
            Else {
                # Get Source Field Value and add to Hashtable
                $ItemValue.Add($Field, $ListItem[$Field])
            }
        }
    }
    Write-Progress -Activity "Copying List Items:" -Status "Copying Item ID '$($ListItem.Id)' from Source List ($($Counter) of $($SourceListItems.Count))" -PercentComplete (($Counter / $SourceListItems.Count) * 100)
  
    # Copy column value from source to target
    Add-PnPListItem -List $DestinationListName -Values $ItemValue | Out-Null
   
    Write-Host "Copied Values from Source to Target Column of Item '$($ListItem.Id)' ($($Counter) of $($SourceListItems.Count)) "
    $Counter++
}

Ensure that the destination list has matching fields (including Field Internal Name and field settings) as the source list before executing this script.

How to Copy List Items Between SharePoint Lists using PowerShell

Are you interested in transferring items between two SharePoint lists?

If you have a set of test items and wish to copy data from one list to another without the hassle of manual entry, this will guide you through the lines of code required for copying items between SharePoint lists using PowerShell:

# Configuration variables
$WebURL = "https://3xrmw4.sharepoint.com/_layouts/15/sharepoint.aspx"
$SourceListName = "News"
$TargetListName = "NewsArchive"
 
# Get Objects
$web = Get-SPWeb $WebURL
$SourceList = $web.Lists[$SourceListName]
$TargetList = $web.Lists[$TargetListName]
 
# Get all source items
$SourceColumns = $SourceList.Fields
$SourceItems = $SourceList.GetItems()
 
# Iterate through each item and add to target list
foreach ($SourceItem in $SourceItems) {
    $TargetItem = $TargetList.AddItem()
    foreach ($column in $SourceColumns) {
        if ($column.ReadOnlyField -eq $false -and $column.InternalName -ne "Attachments") {
            $TargetItem[$($column.InternalName)] = $SourceItem[$($column.InternalName)]
        }
    }
    $TargetItem.Update()
}

To transfer attachments between lists in SharePoint, you can utilize the following script:

# Configuration variables
$WebURL = "https://3xrmw4.sharepoint.com"
$SourceListName = "News"
$TargetListName = "NewsArchive"
 
# Get Objects
$web = Get-SPWeb $WebURL
$SourceList = $web.Lists[$SourceListName]
$TargetList = $web.Lists[$TargetListName]
 
# Get all source items
$SourceColumns = $SourceList.Fields
$SourceItems = $SourceList.GetItems()
 
# Iterate through each item and add to target list
foreach ($SourceItem in $SourceItems) {
    $TargetItem = $TargetList.AddItem()
    foreach ($column in $SourceColumns) {
        if ($column.ReadOnlyField -eq $false -and $column.InternalName -ne "Attachments") {
            $TargetItem[$($column.InternalName)] = $SourceItem[$($column.InternalName)]
        }
    }
    $TargetItem.Update()
 
    # Copy Attachments
    foreach ($Attachment in $SourceItem.Attachments) {
        $spFile = $SourceList.ParentWeb.GetFile($SourceItem.Attachments.UrlPrefix + $Attachment)
        $TargetItem.Attachments.Add($Attachment, $spFile.OpenBinary())
    }
}

What if the column names are different? you can run the following PowerShell script

$SourceItems = $SourceList.Items
foreach ($SourceItem in $SourceItems) {
    Write-Host -ForegroundColor Yellow "Copying Item: $($SourceItem["Title"])"
    $TargetItem = $TargetList.AddItem()
    $TargetItem["Title"] = $SourceItem["Title-Column"]
    $TargetItem["Another-Field"] = $SourceItem["Another-Field"]
    $TargetItem.Update()
}

Copy Document Library to Another site using PnP PowerShell

The PnP provisioning engine facilitates the replication of site objects like lists and libraries, site columns, content types, composed looks, pages, etc. you can export existing customizations to an XML file, which can then be used as a template to create new objects.

Copying lists and libraries between SharePoint Online site collections is achievable through the PnP Provisioning Engine. Below is the PowerShell script for this operation:

Use the PnP Provisioning Template approach to duplicate a list.

Step 1: Get the List Schema from the Source Site
With the PnP provisioning engine, we need to extract the list schema first:

# Config Variables
$SiteURL = "https://3xrmw4.sharepoint.com/"
$ListName = "Project Tasks"
$TemplateFile = "C:\Temp\ListSchema.xml"
 
# Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
 
# Get the List schema as Template and export to a File
$Templates = Get-PnPSiteTemplate -OutputInstance -Handlers Lists
$ListTemplate = $Templates.Lists | Where-Object { $_.Title -eq $ListName }
$Templates.Lists.Clear()
$Templates.Lists.Add($ListTemplate)
Save-PnPSiteTemplate -InputInstance $Templates -Out $TemplateFile

This script generates an XML template file encompassing all list schemas from the SharePoint Online site, subsequently filtering to retrieve the specific list.

Step 2: Import the Template to the Target Site collection.

Once the template XML file is prepared, it can be imported into any site collection, as follows:

# Config Variables
$SiteURL = "https://3xrmw4.sharepoint.com/"
$TemplateFile = "C:\Temp\ListSchema.xml"
 
# Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
 
Write-Host "Creating List from Template..."
Invoke-PnPSiteTemplate -Path $TemplateFile

The provided PowerShell script facilitates the copying of a list or document library from the source site to the destination site collection.

Copy List Structure using Site Designs and Site Scripts

To replicate list structure using site designs and scripts with PowerShell, follow these steps:

# Define Parameters
$AdminCenterURL = "https://3xrmw4.sharepoint.com/sites/Mark8ProjectTeam/SitePages/Home.aspx"
$ListURL = "https://3xrmw4.sharepoint.com/sites/SampleTeamSiteNew/Lists/6000%20Items%20List?env=WebViewList"
$TargetSiteURL = "https://3xrmw4.sharepoint.com/sites/SampleTeamSiteNew/SitePages/Test-Wiki-Page.aspx"
 
# Connect to SharePoint Online
Connect-SPOService -Url $AdminCenterURL -Credential (Get-Credential)
 
# Get the list schema
$ListSchema = Get-SPOSiteScriptFromList -ListUrl $ListURL
 
# Add list schema as Site Script
$SiteScript = Add-SPOSiteScript -Title "Project Tasks List" -Content $ListSchema
 
# Create a Site Design
$SiteDesign = Add-SPOSiteDesign -Title "Provision Project Tasks List" -WebTemplate 64 -SiteScripts $SiteScript.Id
 
# Apply Site Design to site
Invoke-SPOSiteDesign -Identity $SiteDesign.Id -WebUrl $TargetSiteURL

How to use PowerShell to Copy List Items between SharePoint Online Site Collections

When the need arises to transfer list items between site collections in SharePoint Online, PowerShell offers a swift and straightforward solution.

This method proves beneficial when migrating data between different sites, creating backups, consolidating information from multiple lists, or executing site collection migrations.

Ensure to adjust the parameters accordingly and execute the script. It’s essential to have your target list already created with the same structure, either by creating a new list from an existing list or saving the list as a template and creating a new list instance to ensure the internal names remain intact.

# 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" -Connection $SourceConn
        $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 -ForegroundColor 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 -Connection $DestinationConn
    
            # 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 -ForegroundColor Red "Error: $($_.Exception.Message)"
    }
}

# Set Parameters
$SourceSiteURL = "https://crescent.sharepoint.com/sites/Retail"
$SourceListName = "Projects"

$DestinationSiteURL = "https://crescent.sharepoint.com/sites/Sales"
$DestinationListName = "Projects"

# 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

If you are copying list items between sites, make sure all parent lookup lists are created in the target site first.

Copy List Permissions in SharePoint using PowerShell

Have you ever needed to replicate permissions between SharePoint lists or libraries?
Below is a handy PowerShell script designed to copy permissions between SharePoint lists:

# PowerShell Function to copy permissions between Lists in SharePoint
Function Copy-ListPermissions {
    param(
        $WebURL,
        $SourceListName,
        $TargetListName
    )
    
    # Get the Web
    $Web = Get-SPWeb $WebURL
    
    # Get Source and Target Lists
    $SourceList = $Web.Lists[$SourceListName]
    $TargetList = $Web.Lists[$TargetListName]
    
    # If permissions are Inherited in Source, apply it in Target list
    if ($SourceList.Permissions.Inherited) {
        $TargetList.ResetRoleInheritance()
    }
    else { # Copy permissions from Source to Target List
        # Reset the Inheritance in Target List
        $TargetList.BreakRoleInheritance($false)
 
        # Copy Source list permissions to Destination List
        $SourceList.RoleAssignments | ForEach-Object {
            $TargetList.RoleAssignments.Add($_)
        }
    }
    $TargetList.Update()
}

# Call the function to copy list permissions
Copy-ListPermissions "https://sharepoint.crescent.com/sites/operations/us" "Documents" "Invoice"

The provided script transfers all users and groups from the source list to the target list. It’s essential to note that any pre-existing custom permissions on the target list will be overwritten by this operation.

Moreover, this method is applicable for replicating permissions across SharePoint list items and sites.

Conclusions

This article has provided a comprehensive guide on efficiently copying lists with content on SharePoint lists.

We explored manual methods like creating and copying lists within SharePoint Online, as well as more advanced PowerShell scripts. Emphasis was placed on understanding dependencies, such as lookup fields, to avoid errors.

Automation was highlighted as a key strategy to streamline list management tasks and enhance productivity.

Overall, by following the outlined strategies and utilizing the recommended tools, you can effectively copy columns and re-use them in other SharePoint lists and improve your adaptability to the SharePoint environments.