Dynamically copy Sharepoint lists' content with circular lookups to the another site

Copper Contributor

For about two weeks I've been researching for a solution to move a couple of sites from one site to another. I've come up with two Powershell scripts, first, to create an exact copy of a source site's structure and second, to move all of the items, including lookup fields, to that site.


The idea is to get all of the source list's items, populate the gaps between items with "dummies" or, better to be called, just empty records, upload it to the csv file and then, from the csv file to the destination site. At the end delete the dummies, directly from the list. It may sound dumb but I'm new into Powershell and Microsoft services at all. Scripts use PnP Powershell and work with Sharepoint Online sites.


My problem is with multi lookup fields. I do not know how to correctly write them into the CSV file so they can be later uploaded in to hashtable and then, to the destination list.


Also, maybe there is easier way to do such a thing, so I'd be glad if you could help me to find it out.




Script #1 - create a template, based on the source site's lists, and upload it to the specified site



Connect-PnPOnline -Url https://myenvironment.sharepoint.com/sites/source_site -Interactive
$template = "C:\Users\myuser\...\temp.xml"
Get-PnPSiteTemplate -Out $template -ListsToExtract "list1", "list2", "list3" -Handlers Lists
Connect-PnPOnline -Url https://myenvironment.sharepoint.com/sites/destination_site -Interactive
Invoke-PnPSiteTemplate -Path $template



Script  #2 - copy the source site's items to the destiniation site's newly created lists



Connect-PnPOnline -Url $source_site -Interactive
$source_site = "https://myenvironment.sharepoint.com/sites/source_site"
$destination_site = "https://myenvironment.sharepoint.com/sites/destnation_site"
$source_list = read-host "Set the destination list: "
$csv_path = "C:\Users\myuser\...\temp.csv"
$fields = @(Get-PnPField -List $source_list -ReturnTyped | Where-Object { $_.Hidden -eq $false -and $_.ReadOnlyField -eq $false -and $_.InternalName -ne "Attachments" -and $_.InternalName -ne "ContentType" -or $_.InternalName -eq "ID"}).InternalName
$dummies = 1..((Get-PnPListItem -List $source_list).Length*2)
$list_items = Get-PnPListItem -List $source_list -Fields $fields
$object_array = @()
$list_items | ForEach-Object {
    $object = New-Object PSCustomObject
    $fields_values  = Get-PnPProperty -ClientObject $_ -Property FieldValuesAsText
    ForEach($field in $fields) {
        if((Get-PnPField -List $source_list -Identity $field).TypeAsString -eq "Lookup") {
            Get-PnPListItem -List $source_list -Fields $field | Out-Null
            $lookup = [Microsoft.SharePoint.Client.FieldLookupValue]$_[$field]
            $object | Add-Member Noteproperty $field $lookup.LookupId
        }  else {
            $object | Add-Member Noteproperty $field $fields_values[$field]
    $object_array += $object 

for($i=1; $i -le $dummies.Length; $i++) {
    if($object_array.ID -notcontains $i){
        $object = New-Object PSCustomObject
        ForEach($field in $fields) {
            if($field -eq "ID") {
                $object | Add-Member Noteproperty $field $i
            } else {
                $object | Add-Member Noteproperty $field 0
        $object_array += $object 
$object_array = $object_array | Sort-Object -Property ID
$object_array | Export-CSV $csv_path -NoTypeInformation -Encoding UTF8
$csv_table = Import-Csv $csv_path | Select * -ExcludeProperty ID
Connect-PnPOnline -Url $destination_site -Interactive
foreach($row in $csv_table) {   
    $item_values = @{}
    $row.psobject.properties | Foreach {$item_values[$_.Name] = $_.Value}
    Add-PnPListItem -List $source_list -Values $item_values
$fields_to_delete = Get-PnPListItem -List $source_list
foreach($element in $fields_to_delete) {
    if($element["Title"] -eq "0"){Remove-PnPListItem -List $source_list -Identity $element["ID"] -Force}


Thanks for help!

0 Replies