Post
Ask Question

Copy List Items from One Site to Another With Versions using Powershell

Introduction 

 
1) Download and Install SharePoint Online SDK here.
 
2) Pass credentials to SharePoint Online using an external file. There are different ways too, referred to on this site. 
  1. $credentials = Get-Credential  
  2. $filename = 'D:\Arvind\safe\secretfile.txt'    
  3. $credentials | Export-Clixml -path $filename 
3) Make sure that SourceList Column and DestinationList column are the same.
 
4) Now pass the parameter to the script.
  1. #Set Parameters    
  2. $todayDate = (Get-Date).toString("yyyy_MM_dd")    
  3. $Logfile = "D:\Logs\copyListItems_"+$todayDate+".txt"    
  4. $srcListSiteUrl = "Source Site Url"        
  5. $SourceListName = "SourceListName"         
  6. $dstListSiteUrl = "Destination Site Url"        
  7. $TargetListName = "DestinationListName"    
  8. $sourceQuery = "Your Query" 
5) Complete the Powershell Script 
  1. #Set Global Variable    
  2. $global:spoUsers = @{};    
  3.   
  4. #Load SharePoint CSOM Assemblies    
  5. Add-Type -path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll'    
  6. Add-Type -path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll'    
  7.     
  8. Function LogWrite    
  9. {    
  10.    Param ([string]$logstring)    
  11.     
  12.    Add-content $Logfile -value $logstring    
  13. }    
  14. Function Ensure-SPOUser()    
  15. {    
  16.     Param(    
  17.         [Parameter(Mandatory=$true)] [string]$emailID,    
  18.         [Parameter(Mandatory=$true)] [Microsoft.SharePoint.Client.ClientContext]$Ctx    
  19.         )    
  20.     Try {    
  21.         #ensure sharepoint online user    
  22.         Write-Host "Verify User" $emailID    
  23.         LogWrite "Verify User" $emailID    
  24.         $Web = $Ctx.Web    
  25.         $User=$Web.EnsureUser($emailID)    
  26.         $global:spoUsers.Add($emailID , $User)    
  27.         return $User    
  28.     }    
  29.     Catch {       
  30.         #write-host -f Red "Error:" $_.Exception.Message    
  31.         return $null    
  32.     }    
  33. }    
  34.     
  35. Function UpdateSystemCol()    
  36. {    
  37.     Param(    
  38.         [Parameter(Mandatory=$true)] $SourceItem,    
  39.         [Parameter(Mandatory=$true)] $ListItem,    
  40.         [Parameter(Mandatory=$true)] $destCtx     
  41.         )    
  42.         $authorUser = ""    
  43.         $editorUser = ""    
  44.         if(!([string]::IsNullOrEmpty($SourceItem["Author"].Email)))    
  45.         {    
  46.             #check user present in hashtable    
  47.             if($global:spoUsers.ContainsKey($SourceItem["Author"].Email))     
  48.             {    
  49.                 $ListItem["Author"] = $global:spoUsers[$SourceItem["Author"].Email]    
  50.             } else     
  51.             {    
  52.                 $authorUser = Ensure-SPOUser $SourceItem["Author"].Email $destCtx     
  53.                 $ListItem["Author"] = $authorUser    
  54.             }     
  55.         }     
  56.         elseif(([string]::IsNullOrEmpty($SourceItem["Author"].Email)) -or $authorUser -eq $null)    
  57.         {    
  58.             $ListItem["Author"] = $currentUser    
  59.         }    
  60.         if(!([string]::IsNullOrEmpty($SourceItem["Editor"].Email)))    
  61.         {    
  62.             #check user present in hashtable    
  63.             if($global:spoUsers.ContainsKey($SourceItem["Editor"].Email))     
  64.             {    
  65.                 $ListItem["Editor"] = $global:spoUsers[$SourceItem["Editor"].Email]    
  66.             } else     
  67.             {    
  68.                 $editorUser = Ensure-SPOUser $SourceItem["Editor"].Email $destCtx     
  69.                 $ListItem["Editor"] = $editorUser    
  70.             }     
  71.         }    
  72.         elseif(([string]::IsNullOrEmpty($SourceItem["Editor"].Email)) -or $editorUser -eq $null)    
  73.         {    
  74.             $ListItem["Editor"] = $currentUser    
  75.         }       
  76.         $ListItem["Created"] = $SourceItem["Created"]    
  77.         $ListItem["Modified"] = $SourceItem["Modified"]    
  78.         return $ListItem    
  79. }    
  80. Function Copy-ListItems()    
  81. {    
  82.     param    
  83.     (    
  84.         [Parameter(Mandatory=$true)] [string] $siteURL,    
  85.         [Parameter(Mandatory=$true)] [string] $destSiteURL,    
  86.         [Parameter(Mandatory=$true)] [string] $SourceListName,    
  87.         [Parameter(Mandatory=$true)] [string] $TargetListName,    
  88.         [Parameter(Mandatory=$true)] [string] $query,    
  89.         [Parameter(Mandatory=$true)] [string] $Logfile    
  90.     )       
  91.     Try {    
  92.         If(!(test-path $Logfile))    
  93.         {    
  94.             New-Item -Path $Logfile -Type File -Force | Out-Null    
  95.         }    
  96.         LogWrite "Copy-ListItems Fuction Called"     
  97.         #Passing Credentials    
  98.         $credPath = 'D:\Arvind\safe\secretfile.txt'    
  99.         $fileCred = Import-Clixml -path $credpath    
  100.         $Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($fileCred.UserName, $fileCred.Password)    
  101.         
  102.         #Setup the source context    
  103.         $sourceCtx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)    
  104.         $sourceCtx.Credentials = $Cred    
  105.           
  106.         #Setup the destination Context    
  107.         $destCtx = New-Object Microsoft.SharePoint.Client.ClientContext($destSiteURL)    
  108.         $destCtx.Credentials = $Cred    
  109.         LogWrite "User Credential is valid and It is Successfully Login"    
  110.   
  111.         #Get Current loged User on destination Site    
  112.         $currentUser =$destCtx.Web.CurrentUser;    
  113.         $destCtx.Load($currentUser)    
  114.         $destCtx.ExecuteQuery()    
  115.     
  116.         $currentUser= $destCtx.Web.EnsureUser($currentUser.Email)    
  117.         $destCtx.Load($currentUser)    
  118.         $destCtx.ExecuteQuery()    
  119.   
  120.         #Get the Source List and Target Lists    
  121.         $SourceList = $sourceCtx.Web.Lists.GetByTitle($SourceListName)    
  122.         $TargetList = $destCtx.Web.Lists.GetByTitle($TargetListName)    
  123.           
  124.         #Get CAML Query object    
  125.         $camlquery = New-Object Microsoft.SharePoint.Client.CamlQuery;    
  126.         $camlquery.ViewXml= $query     
  127.         LogWrite "Query:" $query    
  128.   
  129.         #Get All Items from the Source List in batches    
  130.         Write-Progress -Activity "Reading Source..." -Status "Getting Items from Source List. Please wait..."    
  131.         $SourceListItems = $SourceList.GetItems($camlquery)    
  132.         $sourceCtx.Load($SourceListItems)    
  133.         $sourceCtx.ExecuteQuery()    
  134.         $SourceListItemsCount= $SourceListItems.count    
  135.         Write-host "Total Number of Items Found:"$SourceListItemsCount -foregroundcolor black -backgroundcolor Green    
  136.         LogWrite "Total Number of Items Found:"$SourceListItemsCount    
  137.         #Get All fields from Source List & Target List    
  138.         $SourceListFields = $SourceList.Fields    
  139.         $sourceCtx.Load($SourceListFields)    
  140.             
  141.         $TargetListFields = $TargetList.Fields    
  142.         $destCtx.Load($TargetListFields)           
  143.            
  144.         $sourceCtx.ExecuteQuery()    
  145.         $destCtx.ExecuteQuery()    
  146.           
  147.         #Loop through each item in the source and Get column values, add them to target    
  148.         [int]$Counter = 1    
  149.   
  150.         #Get each column value from source list and add them to target    
  151.         ForEach($SourceItem in $SourceListItems)    
  152.         {     
  153.             $versionColl = $SourceItem.Versions    
  154.             $sourceCtx.Load($versionColl)    
  155.             $sourceCtx.ExecuteQuery()    
  156.             Write-Host "ID: "$SourceItem.ID "Version Count: " $versionColl.Count    
  157.             LogWrite "ID: "$SourceItem.ID "Version Count: " $versionColl.Count    
  158.             $NewItem =New-Object Microsoft.SharePoint.Client.ListItemCreationInformation    
  159.             $ListItem = $TargetList.AddItem($NewItem)    
  160.               
  161.             #check the number of version available. If version is greater than 1.0 then create item by iterating the for loop with descending order.    
  162.             Write-Progress -Activity "Copying List Items:" -Status "Copying Item ID '$($SourceItem.Id)' from Source List ($($Counter) of $($SourceListItemsCount))" -PercentComplete (($Counter / $SourceListItemsCount) * 100)    
  163.               
  164.             #check the number of version.    
  165.             if($versionColl.Count -gt 1)    
  166.             {    
  167.                 for($i= $versionColl.Count-1; $i -ge 0; $i--)    
  168.                 {    
  169.                   $version = $versionColl[$i];    
  170.                   Foreach($SourceField in $SourceListFields)    
  171.                   {     
  172.                     #Skip Read only, hidden fields, content type and attachments    
  173.                     If((-Not ($SourceField.ReadOnlyField)) -and (-Not ($SourceField.Hidden)) -and ($SourceField.InternalName -ne  "ContentType") -and ($SourceField.InternalName -ne  "Attachments") )     
  174.                     {    
  175.                         $TargetField = $TargetListFields | where { $_.Internalname -eq $SourceField.Internalname}    
  176.                         if($TargetField -ne $null -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor" -and $SourceField.InternalName -ne "Created" -and $SourceField.InternalName -ne "Modified")    
  177.                         {    
  178.                           $ListItem[$TargetField.InternalName] =$version[$SourceField.InternalName]    
  179.                                
  180.                         }    
  181.                     }    
  182.                   }    
  183.                   if($i -eq $versionColl.Count-1)     
  184.                   {    
  185.                     $ListItem =UpdateSystemCol $SourceItem $ListItem $destCtx    
  186.                   }    
  187.                   else    
  188.                   {    
  189.                     $authorUser = ""    
  190.                     $editorUser = ""    
  191.                     if(!([string]::IsNullOrEmpty($SourceItem["Editor"].Email)))     
  192.                     {    
  193.                         #check user present in hashtable    
  194.                         if($global:spoUsers.ContainsKey($SourceItem["Editor"].Email))     
  195.                         {    
  196.                             $ListItem["Editor"] = $global:spoUsers[$SourceItem["Editor"].Email]    
  197.                         }     
  198.                         else     
  199.                         {    
  200.                             $editorUser = Ensure-SPOUser $SourceItem["Editor"].Email $destCtx     
  201.                             $ListItem["Editor"] = $editorUser    
  202.                         }    
  203.                     }    
  204.                     elseif(([string]::IsNullOrEmpty($SourceItem["Editor"].Email)) -or $editorUser -eq $null)    
  205.                     {    
  206.                         $ListItem["Editor"] = $currentUser    
  207.                     }    
  208.                    
  209.                     $ListItem["Created"] = $SourceItem["Created"]    
  210.                     $ListItem["Modified"] = $SourceItem["Modified"]    
  211.                   }     
  212.                  $ListItem.Update()    
  213.                  $destCtx.ExecuteQuery()    
  214.                 }    
  215.             }    
  216.             else #If only one version available    
  217.             {    
  218.                $version = $versionColl[0];    
  219.                Foreach($SourceField in $SourceListFields)    
  220.                {     
  221.                     #Skip Read only, hidden fields, content type and attachments    
  222.                     If((-Not ($SourceField.ReadOnlyField)) -and (-Not ($SourceField.Hidden)) -and ($SourceField.InternalName -ne  "ContentType") -and ($SourceField.InternalName -ne  "Attachments") )     
  223.                     {    
  224.                         $TargetField = $TargetListFields | where { $_.Internalname -eq $SourceField.Internalname}    
  225.                         if($TargetField -ne $null -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor" -and $SourceField.InternalName -ne "Created" -and $SourceField.InternalName -ne "Modified")    
  226.                         {    
  227.                           $ListItem[$TargetField.InternalName] =$version[$SourceField.InternalName]      
  228.                         }    
  229.                     }    
  230.                   }    
  231.                $ListItem = UpdateSystemCol $SourceItem $ListItem $destCtx     
  232.                $ListItem.Update()    
  233.                $destCtx.ExecuteQuery()    
  234.             }    
  235.             Write-Host "Copied Item ID from Source to Target List:$($SourceItem.Id) ($($Counter) of $($SourceListItemsCount))"    
  236.             $Counter++    
  237.         }    
  238.         write-host  -f Green "Total List Items Copied from '$SourceListName' to '$TargetListName' : $($SourceListItems.count)"    
  239.         LogWrite "Total List Items Copied from '$SourceListName' to '$TargetListName' : $($SourceListItems.count)"    
  240.     }    
  241.     Catch {    
  242.         write-host -f Red "Error Copying List Items!" $_.Exception.Message    
  243.         LogWrite "Error Copying List Items!" $_.Exception.Message    
  244.     }    
  245. }    
  246.    
  247. #Set Parameters    
  248. $todayDate = (Get-Date).toString("yyyy_MM_dd")    
  249. $Logfile = "D:\Logs\copyListItems_"+$todayDate+".txt"    
  250.     
  251. $srcListSiteUrl = "SourceSite Url"        
  252. $SourceListName = "SourceList Name"         
  253. $dstListSiteUrl = "Destination Site"        
  254. $TargetListName = "DestinationList Name"    
  255. $sourceQuery = "Your Query"    
  256. #Passing Credentials    
  257. $credPath = 'D:\Arvind\safe\secretfile.txt'    
  258. $fileCred = Import-Clixml -path $credpath    
  259. #Call the function to copy list items    
  260. Copy-ListItems -siteURL $srcListSiteUrl -destSiteURL $dstListSiteUrl -SourceListName $SourceListName -TargetListName $TargetListName -query $sourceQuery -logFile $Logfile 
Next Recommended Reading How To Copy SharePoint List Data From One List To Other Using PnP PowerShell