Post
Ask Question

Copy List Items By Retaining ID In SharePoint Online

This article will help individuals who are looking for a solution to copy the list items from source site to destination site by retaining the ID's and item versions in SharePoint online without using any third-party tool.
 
When our SharePoint list crossed the thresold limit (5000), we are wanting  to archive the items in a different list on the same site or different site. We can now archive the items on the same site or a different site using these scripts.
 
Prequisites 
  •  Download and install the SharePoint Online SDK from this article
  •  Destination site list should have similar columns.
For demonstration purposes, I have created two lists on the source site; i.e. Hobbies and Employee
 
Source List
 
Copy List Items By Retaining ID In SharePoint Online
 
Employee List 
 
Copy List Items By Retaining ID In SharePoint Online
 
Copy List Items By Retaining ID In SharePoint Online
 
Note
If source site list contains any lookup columns, then first copy the lookup list on the destination site before proceding with the actual list. Since my list Employee contains the lookup column, I will copy the Hobbies list first then Employee list. 
 

Using Powershell 

 
For demo, I have stored the credentials in the file. To learn more refer to my article.
 
As we know Id's in SharePoint lists are autogenerated, hence to retain Id's on destination site we use the following approach,
  • Create new item in destination list and compare with source list item
  • If source list item id is equal to destination list item id then continue copying items.
  • If source list item id is not equal to destination list item id then create and delete the dummy items in destination list till it matches with source item id.
Note
Run this script only if destination list doesn't have any items, If any record exists in list then delete the list and create a new one before running this script. 
  1. #Passing Credentials  
  2. $credPath = 'D:\Arvind\safe\secretfile.txt'  
  3. $fileCred = Import-Clixml -path $credpath  
Change the following parameters before running the script.
  1. #Set Parameters  
  2. $todayDate = (Get-Date).toString("yyyy_MM_dd")  
  3. $Logfile = "D:\Logs\copyListItems_" + $todayDate + ".txt"  
  4.   
  5. $srcListSiteUrl = "Your Source Site"      
  6. $SourceListName = "Employee"       
  7. $dstListSiteUrl = "Your Destionation Site"      
  8. $TargetListName = "Employee"  
  9. $sourceQuery = "<View>  
  10.                </View>"  
Copy the list items from source site Employee list using this code.
  1. Function Copy-ListItems() {  
  2.     param  
  3.     (  
  4.         [Parameter(Mandatory = $true)] [string] $siteURL,  
  5.         [Parameter(Mandatory = $true)] [string] $destSiteURL,  
  6.         [Parameter(Mandatory = $true)] [string] $SourceListName,  
  7.         [Parameter(Mandatory = $true)] [string] $TargetListName,  
  8.         [Parameter(Mandatory = $true)] [string] $query,  
  9.         [Parameter(Mandatory = $true)] [string] $Logfile  
  10.     )     
  11.     Try {  
  12.         If (!(test-path $Logfile)) {  
  13.             New-Item -Path $Logfile -Type File -Force | Out-Null  
  14.         }  
  15.         LogWrite "Copy-ListItems Fuction Called"   
  16.         $Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($fileCred.UserName, $fileCred.Password)  
  17.        
  18.         #Setup the source context  
  19.         $sourceCtx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
  20.         $sourceCtx.Credentials = $Cred  
  21.          
  22.         #Setup the destination Context  
  23.         $destCtx = New-Object Microsoft.SharePoint.Client.ClientContext($destSiteURL)  
  24.         $destCtx.Credentials = $Cred  
  25.         LogWrite "User Credential is valid and It is Successfully Login"  
  26.  
  27.         #Get Current loged User on destination Site  
  28.         $currentUser = $destCtx.Web.CurrentUser;  
  29.         $destCtx.Load($currentUser)  
  30.         $destCtx.ExecuteQuery()  
  31.   
  32.         $currentUser = $destCtx.Web.EnsureUser($currentUser.Email)  
  33.         $destCtx.Load($currentUser)  
  34.         $destCtx.ExecuteQuery()  
  35.  
  36.         #Get the Source List and Target Lists  
  37.         $SourceList = $sourceCtx.Web.Lists.GetByTitle($SourceListName)  
  38.         $TargetList = $destCtx.Web.Lists.GetByTitle($TargetListName)  
  39.          
  40.         #Get CAML Query object  
  41.         $camlquery = New-Object Microsoft.SharePoint.Client.CamlQuery;  
  42.         $camlquery.ViewXml = $query   
  43.         LogWrite "Query:" $query  
  44.  
  45.         #Get All Items from the Source List in batches  
  46.         Write-Progress -Activity "Reading Source..." -Status "Getting Items from Source List. Please wait..."  
  47.         $SourceListItems = $SourceList.GetItems($camlquery)  
  48.         $sourceCtx.Load($SourceListItems)  
  49.         $sourceCtx.ExecuteQuery()  
  50.         $SourceListItemsCount = $SourceListItems.count  
  51.         Write-host "Total Number of Items Found:"$SourceListItemsCount -foregroundcolor black -backgroundcolor Green  
  52.         LogWrite "Total Number of Items Found:" $SourceListItemsCount  
  53.         #Get All fields from Source List & Target List  
  54.         $SourceListFields = $SourceList.Fields  
  55.         $sourceCtx.Load($SourceListFields)  
  56.           
  57.         $TargetListFields = $TargetList.Fields  
  58.         $destCtx.Load($TargetListFields)         
  59.          
  60.         $sourceCtx.ExecuteQuery()  
  61.         $destCtx.ExecuteQuery()  
  62.          
  63.         #Loop through each item in the source and Get column values, add them to target  
  64.         [int]$Counter = 1  
  65.  
  66.         #Get each column value from source list and add them to target  
  67.         ForEach ($SourceItem in $SourceListItems) {   
  68.             $versionColl = $SourceItem.Versions  
  69.             $sourceCtx.Load($versionColl)  
  70.             $sourceCtx.ExecuteQuery()  
  71.             Write-Host "ID: "$SourceItem.ID "Version Count: " $versionColl.Count  
  72.             LogWrite "ID: "$SourceItem.ID "Version Count: " $versionColl.Count  
  73.             $ListItem = Create-Item -TargetList $TargetList -versionColl $versionColl -SourceListFields $SourceListFields -TargetListFields $TargetListFields -SourceItem $SourceItem -destCtx $destCtx -SourceListItemsCount $SourceListItemsCount -Counter $Counter  
  74.             $sourceId = $($SourceItem.Id)  
  75.             $destionationId = $($ListItem.Id)  
  76.             $dummyCount = [int]$sourceId - 1  
  77.             while($destionationId -ne $sourceId) {  
  78.                 if($sourceId -ne $destionationId)   
  79.                 {  
  80.                     Write-Host "Deleting the Item from destionation Site $($destionationId) as not equal to Source Item $($sourceId)" -ForegroundColor Yellow  
  81.                     $ListItem.DeleteObject()  
  82.                     $destCtx.ExecuteQuery()  
  83.                 }  
  84.                 Write-Host "Destionation Id : $($destionationId) : $($dummyCount) : if($($destionationId) -lt $($dummyCount))"  
  85.                 #check the destionation is less than source Id -1 to create dummy item in list.  
  86.                 if($destionationId -lt $dummyCount)  
  87.                 {  
  88.                   $ListItem = Create-Dummy-Item -TargetList $TargetList -destCtx $destCtx  
  89.                 }   
  90.                 else  
  91.                 {  
  92.                     $ListItem = Create-Item -TargetList $TargetList -versionColl $versionColl -SourceListFields $SourceListFields -TargetListFields $TargetListFields -SourceItem $SourceItem -destCtx $destCtx -SourceListItemsCount $SourceListItemsCount -Counter $Counter  
  93.                 }  
  94.                 $destionationId = $($ListItem.Id)  
  95.             }  
  96.             Write-Host "Copied Item ID from Source to Target List:$($SourceItem.Id) ($($Counter) of $($SourceListItemsCount))"  
  97.             $Counter++  
  98.         }  
  99.         write-host  -f Green "Total List Items Copied from '$SourceListName' to '$TargetListName' : $($SourceListItems.count)"  
  100.         LogWrite "Total List Items Copied from '$SourceListName' to '$TargetListName' : $($SourceListItems.count)"  
  101.     }  
  102.     Catch {  
  103.         write-host -f Red "Error Copying List Items!" $_.Exception.Message  
  104.         LogWrite "Error Copying List Items!" $_.Exception.Message  
  105.     }  
  106. }  
Create items on destination site with this code.
  1. Function Create-Item(){  
  2.     param(  
  3.     [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.List] $TargetList,  
  4.     [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItemVersionCollection] $versionColl,  
  5.     [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.FieldCollection] $SourceListFields,  
  6.     [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.FieldCollection] $TargetListFields,  
  7.     [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem] $SourceItem,  
  8.     [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext] $destCtx,  
  9.     [Parameter(Mandatory = $true)] [int] $SourceListItemsCount,  
  10.     [Parameter(Mandatory = $true)] [int] $Counter  
  11.     )  
  12.     $NewItem = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation  
  13.     $ListItem = $TargetList.AddItem($NewItem)  
  14.     #check the number of version available. If version is greater than 1.0 then create item by iterating the for loop with descending order.  
  15.     Write-Progress -Activity "Copying List Items:" -Status "Copying Item ID '$($SourceItem.Id)' from Source List ($($Counter) of $($SourceListItemsCount))" -PercentComplete (($Counter / $SourceListItemsCount) * 100)  
  16.     #check the number of version.  
  17.     if ($versionColl.Count -gt 1) {  
  18.         for ($i = $versionColl.Count - 1; $i -ge 0; $i--) {  
  19.             $version = $versionColl[$i];  
  20.             Foreach ($SourceField in $SourceListFields) {  
  21.                 #Handle Special Fields  
  22.                 $FieldType = $SourceField.TypeAsString  
  23.                 #Write-Host "FieldType:" $FieldType  
  24.                 #Skip Read only, hidden fields, content type and attachments and fields is not User fields  
  25.                 If ((-Not ($SourceField.ReadOnlyField)) -and (-Not ($SourceField.Hidden)) -and ($SourceField.InternalName -ne "ContentType") -and ($SourceField.InternalName -ne "Attachments") ) {  
  26.                     $TargetField = $TargetListFields | Where-Object { $_.Internalname -eq $SourceField.Internalname }  
  27.                     if ($null -ne $TargetField -and ($FieldType -ne "User") -and ($FieldType -ne "UserMulti") -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor" -and $SourceField.InternalName -ne "Created" -and $SourceField.InternalName -ne "Modified") {  
  28.                         $ListItem[$TargetField.InternalName] = $version[$SourceField.InternalName]  
  29.                     }  
  30.                     elseif ((($FieldType -eq "User") -or ($FieldType -eq "UserMulti")) -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {  
  31.                         $ListItem = Update-User $FieldType $SourceField $TargetField $version $ListItem $destCtx   
  32.                     }  
  33.                           
  34.                 }  
  35.                       
  36.             }  
  37.             #To change the CreatedBy and Modified By.  
  38.             if ($i -eq $versionColl.Count - 1) {  
  39.                 $ListItem = UpdateSystemCol $SourceItem $ListItem $destCtx  
  40.             }  
  41.             else {  #To Changed Modified by only  
  42.                 $editorUser = ""  
  43.                 if (!([string]::IsNullOrEmpty($SourceItem["Editor"].Email))) {  
  44.                     #check user present in hashtable  
  45.                     if ($global:spoUsers.ContainsKey($SourceItem["Editor"].Email)) {  
  46.                         $ListItem["Editor"] = $global:spoUsers[$SourceItem["Editor"].Email]  
  47.                     }   
  48.                     else {  
  49.                         $editorUser = Ensure-SPOUser $SourceItem["Editor"].Email $destCtx -isMulitUser $false  
  50.                         $ListItem["Editor"] = $editorUser  
  51.                     }  
  52.                 }  
  53.                 elseif (([string]::IsNullOrEmpty($SourceItem["Editor"].Email)) -or $editorUser -eq $null) {  
  54.                     $ListItem["Editor"] = $currentUser  
  55.                 }  
  56.         
  57.                 $ListItem["Modified"] = $SourceItem["Modified"]  
  58.             }  
  59.             $ListItem.Update()  
  60.             $destCtx.ExecuteQuery()  
  61.         }  
  62.     }  
  63.     else {  
  64.         #If only one version available  
  65.         $version = $versionColl[0]  
  66.         Foreach ($SourceField in $SourceListFields) {   
  67.            # Write-Host "Id Value: "$version[$SourceField.InternalName]  
  68.             #Skip Read only, hidden fields, content type and attachments  
  69.             If ((-Not ($SourceField.ReadOnlyField)) -and (-Not ($SourceField.Hidden)) -and ($SourceField.InternalName -ne "ContentType") -and ($SourceField.InternalName -ne "Attachments") ) {  
  70.                 $TargetField = $TargetListFields | Where-Object { $_.Internalname -eq $SourceField.Internalname }  
  71.                 if ($null -ne $TargetField -and ($FieldType -ne "User") -and ($FieldType -ne "UserMulti") -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor" -and $SourceField.InternalName -ne "Created" -and $SourceField.InternalName -ne "Modified") {  
  72.                     $ListItem[$TargetField.InternalName] = $version[$SourceField.InternalName]  
  73.                 }  
  74.                 elseif ((($FieldType -eq "User") -or ($FieldType -eq "UserMulti")) -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {  
  75.                     $ListItem = Update-User $FieldType $SourceField $TargetField $version $ListItem $destCtx   
  76.                 }  
  77.             }  
  78.         }  
  79.         $ListItem = UpdateSystemCol $SourceItem $ListItem $destCtx  
  80.         $ListItem.Update()  
  81.         $destCtx.ExecuteQuery();  
  82.     }  
  83.     return $ListItem  
  84. }  
Maintain Created, Created By, Modified, Modified By with this code.
  1. Function UpdateSystemCol() {  
  2.     Param(  
  3.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem]$SourceItem,  
  4.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem]$ListItem,  
  5.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext]$destCtx   
  6.     )  
  7.     $authorUser = ""  
  8.     $editorUser = ""  
  9.     if (!([string]::IsNullOrEmpty($SourceItem["Author"].Email))) {  
  10.         #check user present in hashtable  
  11.         if ($global:spoUsers.ContainsKey($SourceItem["Author"].Email)) {  
  12.             $ListItem["Author"] = $global:spoUsers[$SourceItem["Author"].Email]  
  13.         }  
  14.         else {  
  15.             $authorUser = Ensure-SPOUser $SourceItem["Author"].Email $destCtx -isMulitUser $false  
  16.             $ListItem["Author"] = $authorUser  
  17.         }   
  18.     }   
  19.     elseif (([string]::IsNullOrEmpty($SourceItem["Author"].Email)) -or $authorUser -eq $null) {  
  20.         $ListItem["Author"] = $currentUser  
  21.     }  
  22.     if (!([string]::IsNullOrEmpty($SourceItem["Editor"].Email))) {  
  23.         #check user present in hashtable  
  24.         if ($global:spoUsers.ContainsKey($SourceItem["Editor"].Email)) {  
  25.             $ListItem["Editor"] = $global:spoUsers[$SourceItem["Editor"].Email]  
  26.         }  
  27.         else {  
  28.             $editorUser = Ensure-SPOUser $SourceItem["Editor"].Email $destCtx -isMulitUser $false  
  29.             $ListItem["Editor"] = $editorUser  
  30.         }   
  31.     }  
  32.     elseif (([string]::IsNullOrEmpty($SourceItem["Editor"].Email)) -or $editorUser -eq $null) {  
  33.         $ListItem["Editor"] = $currentUser  
  34.     }     
  35.     $ListItem["Created"] = $SourceItem["Created"]  
  36.     $ListItem["Modified"] = $SourceItem["Modified"]  
  37.     #$destCtx.Load($ListItem);  
  38.     #$destCtx.ExecuteQuery();  
  39.     return $ListItem  
  40. }  
If the list has any people and groups field, we have to verify the user or groups on destination site before updating the people and group field.
 
To verify user use this code.
  1. Function Ensure-SPOUser() {  
  2.     Param(  
  3.         [Parameter(Mandatory = $true)] [string]$emailID,  
  4.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext]$Ctx,  
  5.         [Parameter(Mandatory = $true)] [boolean]$isMulitUser  
  6.     )  
  7.     Try {  
  8.         #ensure sharepoint online user  
  9.         Write-Host "Verify User" $emailID  
  10.         LogWrite "Verify User" $emailID  
  11.         $Web = $Ctx.Web  
  12.         $User = $Web.EnsureUser($emailID)  
  13.         $Ctx.Load($User)  
  14.         $global:spoUsers.Add($emailID , $User)  
  15.         if($isMulitUser){  
  16.             $Ctx.ExecuteQuery()  
  17.         }  
  18.         return $User  
  19.     }  
  20.     Catch {     
  21.         #write-host -f Red "Error:" $_.Exception.Message  
  22.         return $null  
  23.     }  
  24. }  
To update people and group field use this code.
  1. Function Update-User() {  
  2.     Param(  
  3.         [Parameter(Mandatory = $true)] $FieldType,  
  4.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.Field]$SourceField,  
  5.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.Field]$TargetField,  
  6.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItemVersion]$version,  
  7.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem]$ListItem,  
  8.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext]$destCtx  
  9.     )  
  10.     #check field is user field other than author and editor  
  11.     if ($FieldType -eq "User" -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {  
  12.         $FieldValue = [Microsoft.SharePoint.Client.FieldUserValue]$version[$SourceField.InternalName]  
  13.         Write-Host "Single User Value: $($FieldValue.LookupId) : $($FieldValue.LookupValue) : $($FieldValue.Email)"  
  14.         #If Field value is not null  
  15.         if ($null -ne $FieldValue) {  
  16.             $SingleUser = ""   
  17.             # Get the user value from hash table  
  18.             if ($global:spoUsers.ContainsKey($FieldValue.Email)) {  
  19.                 $SingleUser = $global:spoUsers[$FieldValue.Email]  
  20.             }   
  21.             else { # IF user is not present in hashtable enuse the user.  
  22.                 
  23.                 $SingleUser = Ensure-SPOUser -emailID $FieldValue.Email -Ctx $destCtx -isMulitUser $false  
  24.             }  
  25.             if ($null -ne $SingleUser ) {  
  26.                 $ListItem[$TargetField.InternalName] = $SingleUser # add the user into user field  
  27.             }  
  28.         }  
  29.     }  
  30.      
  31.     #check field is Multi User field other than author and editor  
  32.     if ($FieldType -eq "UserMulti" -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {  
  33.         #Get the Column Values  
  34.         $FieldValues = [Microsoft.SharePoint.Client.FieldUserValue[]]$version[$SourceField.InternalName]  
  35.         Write-host -f Yellow "Number of User Present in Field $($SourceField.InternalName) is : $($FieldValues.Count)"  
  36.         #Get Each User from the collection  
  37.         $UserValueColl = @()  
  38.         ForEach ($FieldValue in $FieldValues) {  
  39.             #Get the Display Name and Email Field  
  40.             Write-Host "MultiUser Value are $($FieldValue.LookupId) : $($FieldValue.LookupValue) : $($FieldValue.Email) " -f Green              
  41.             $SPOUser = ""  
  42.             # Check user present in hashtable  
  43.             if ($global:spoUsers.ContainsKey($FieldValue.Email)) {  
  44.                 $SPOUser = $global:spoUsers[$FieldValue.Email]  
  45.             }   
  46.             else {  
  47.                 $SPOUser = Ensure-SPOUser -emailID $FieldValue.Email -Ctx $destCtx -isMulitUser $true  
  48.             }  
  49.             if ($null -ne $SPOUser) {  
  50.                 $SPOUserValue = New-Object Microsoft.SharePoint.Client.FieldUserValue  
  51.                 $SPOUserValue.LookupId = $SPOUser.Id  
  52.                 $UserValueColl += $SPOUserValue  
  53.             }  
  54.         }  
  55.         If ($UserValueColl.length -gt 0) {  
  56.             $UserValueCollCollection = [Microsoft.SharePoint.Client.FieldUserValue[]]$UserValueColl  
  57.             #Update the Multi-People picker column  
  58.             $ListItem[$TargetField.InternalName] = $UserValueCollCollection  
  59.         }  
  60.     }  
  61.     return $ListItem  
  62. }  
Check  if the newly created item Id in destionation list matches with source list list item Id.  If it doesn't match then create the dummy item until it matches the actual one.
 
We can skip retaining Id's on destination site by commenting the while loop in Copy-ListItems function.  
 
E.g.  
 
Suppose the source list item id is 16 and newly created item id in destionation list is 12 then will we create the dummy item's for Id 13, 14 and 15.
  1.  $sourceId = $($SourceItem.Id)  
  2.  $destionationId = $($ListItem.Id)  
  3.  $dummyCount = [int]$sourceId - 1  
  4.  while($destionationId -ne $sourceId) {  
  5.        if($sourceId -ne $destionationId)   
  6.                 {  
  7.                     Write-Host "Deleting the Item from destionation Site $($destionationId) as not equal to Source Item $($sourceId)" -ForegroundColor Yellow  
  8.                     $ListItem.DeleteObject()  
  9.                     $destCtx.ExecuteQuery()  
  10.                 }  
  11.                 Write-Host "Destionation Id : $($destionationId) : $($dummyCount) : if($($destionationId) -lt $($dummyCount))"  
  12.                 #check the destionation is less than source Id -1 to create dummy item in list.  
  13.                 if($destionationId -lt $dummyCount)  
  14.                 {  
  15.                   $ListItem = Create-Dummy-Item -TargetList $TargetList -destCtx $destCtx  
  16.                 }   
  17.                 else  
  18.                 {  
  19.                     $ListItem = Create-Item -TargetList $TargetList -versionColl $versionColl -SourceListFields $SourceListFields -TargetListFields $TargetListFields -SourceItem $SourceItem -destCtx $destCtx -SourceListItemsCount $SourceListItemsCount -Counter $Counter  
  20.                 }  
  21.                 $destionationId = $($ListItem.Id)  
  22.             }  
  23.   
  24.   
  25. Function Create-Dummy-Item(){  
  26.  param(  
  27.   [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.List] $TargetList,  
  28.   [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext] $destCtx  
  29.  )  
  30.  $NewItem = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation  
  31.  $ListItem = $TargetList.AddItem($NewItem)  
  32.  $ListItem["Title"] = "Dummy Text"  
  33.  $ListItem.Update()  
  34.  $destCtx.ExecuteQuery();  
  35.  Write-Host "Created Dummy Item for Id: $($ListItem.Id)" -f DarkMagenta  
  36.  return $ListItem  
  37. }  
The complete script will look like:
  1. <#  
  2.     This Script alllow us to copy items from source list to destination list.  
  3.   
  4.     ***************************************************************************************************                                      
  5.                                         Prerequisites  
  6.     ***************************************************************************************************  
  7.         1 - The script requires SharePoint Online SDK, Which can be downloaded here:  
  8.             https://www.microsoft.com/en-in/download/details.aspx?id=42038  
  9.   
  10.         2 - Create the list on destination site prior running this scirpt.  
  11.   
  12.         3 - Create the same column type on destination site prior running this scirpt.  
  13.       
  14.     ***************************************************************************************************  
  15.                                         Required Parameters  
  16.     ***************************************************************************************************  
  17.   
  18.      1. $srcListSiteUrl  
  19.      2. $dstListSiteUrl  
  20.      3. $SourceListName  
  21.      4. $TargetListName  
  22.      5. $sourceQuery  
  23.      6. $Logfile  
  24.     ***************************************************************************************************  
  25.                                         Created by    : Arvind Kushwaha 
  26.                                         Created Date  : 25-05-2020  
  27.                                         version       : 1.0   
  28.     ***************************************************************************************************  
  29.    
  30.     ***************************************************************************************************  
  31.                                        Use of the script:  
  32.     ***************************************************************************************************   
  33.   
  34.      Copy-ListItems -siteURL $srcListSiteUrl -destSiteURL $dstListSiteUrl -SourceListName $SourceListName -TargetListName $TargetListName -query $sourceQuery -logFile $Logfile  
  35.  
  36. #>  
  37. #Set Global Variable  
  38. $global:spoUsers = @{ };  
  39. #Load SharePoint CSOM Assemblies  
  40. Add-Type -path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll'  
  41. Add-Type -path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll'  
  42. Function Update-User() {  
  43.     Param(  
  44.         [Parameter(Mandatory = $true)] $FieldType,  
  45.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.Field]$SourceField,  
  46.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.Field]$TargetField,  
  47.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItemVersion]$version,  
  48.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem]$ListItem,  
  49.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext]$destCtx  
  50.     )  
  51.     #check field is user field other than author and editor  
  52.     if ($FieldType -eq "User" -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {  
  53.         $FieldValue = [Microsoft.SharePoint.Client.FieldUserValue]$version[$SourceField.InternalName]  
  54.         Write-Host "Single User Value: $($FieldValue.LookupId) : $($FieldValue.LookupValue) : $($FieldValue.Email)"  
  55.         #If Field value is not null  
  56.         if ($null -ne $FieldValue) {  
  57.             $SingleUser = ""   
  58.             # Get the user value from hash table  
  59.             if ($global:spoUsers.ContainsKey($FieldValue.Email)) {  
  60.                 $SingleUser = $global:spoUsers[$FieldValue.Email]  
  61.             }   
  62.             else { # IF user is not present in hashtable enuse the user.  
  63.                 
  64.                 $SingleUser = Ensure-SPOUser -emailID $FieldValue.Email -Ctx $destCtx -isMulitUser $false  
  65.             }  
  66.             if ($null -ne $SingleUser ) {  
  67.                 $ListItem[$TargetField.InternalName] = $SingleUser # add the user into user field  
  68.             }  
  69.         }  
  70.     }  
  71.      
  72.     #check field is Multi User field other than author and editor  
  73.     if ($FieldType -eq "UserMulti" -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {  
  74.         #Get the Column Values  
  75.         $FieldValues = [Microsoft.SharePoint.Client.FieldUserValue[]]$version[$SourceField.InternalName]  
  76.         Write-host -f Yellow "Number of User Present in Field $($SourceField.InternalName) is : $($FieldValues.Count)"  
  77.         #Get Each User from the collection  
  78.         $UserValueColl = @()  
  79.         ForEach ($FieldValue in $FieldValues) {  
  80.             #Get the Display Name and Email Field  
  81.             Write-Host "MultiUser Value are $($FieldValue.LookupId) : $($FieldValue.LookupValue) : $($FieldValue.Email) " -f Green              
  82.             $SPOUser = ""  
  83.             # Check user present in hashtable  
  84.             if ($global:spoUsers.ContainsKey($FieldValue.Email)) {  
  85.                 $SPOUser = $global:spoUsers[$FieldValue.Email]  
  86.             }   
  87.             else {  
  88.                 $SPOUser = Ensure-SPOUser -emailID $FieldValue.Email -Ctx $destCtx -isMulitUser $true  
  89.             }  
  90.             if ($null -ne $SPOUser) {  
  91.                 $SPOUserValue = New-Object Microsoft.SharePoint.Client.FieldUserValue  
  92.                 $SPOUserValue.LookupId = $SPOUser.Id  
  93.                 $UserValueColl += $SPOUserValue  
  94.             }  
  95.         }  
  96.         If ($UserValueColl.length -gt 0) {  
  97.             $UserValueCollCollection = [Microsoft.SharePoint.Client.FieldUserValue[]]$UserValueColl  
  98.             #Update the Multi-People picker column  
  99.             $ListItem[$TargetField.InternalName] = $UserValueCollCollection  
  100.         }  
  101.     }  
  102.     return $ListItem  
  103. }  
  104. Function LogWrite {  
  105.     Param ([string]$logstring)  
  106.     Add-content $Logfile -value $logstring  
  107. }  
  108. Function Ensure-SPOUser() {  
  109.     Param(  
  110.         [Parameter(Mandatory = $true)] [string]$emailID,  
  111.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext]$Ctx,  
  112.         [Parameter(Mandatory = $true)] [boolean]$isMulitUser  
  113.     )  
  114.     Try {  
  115.         #ensure sharepoint online user  
  116.         Write-Host "Verify User" $emailID  
  117.         LogWrite "Verify User" $emailID  
  118.         $Web = $Ctx.Web  
  119.         $User = $Web.EnsureUser($emailID)  
  120.         $Ctx.Load($User)  
  121.         $global:spoUsers.Add($emailID , $User)  
  122.         if($isMulitUser){  
  123.             $Ctx.ExecuteQuery()  
  124.         }  
  125.         return $User  
  126.     }  
  127.     Catch {     
  128.         #write-host -f Red "Error:" $_.Exception.Message  
  129.         return $null  
  130.     }  
  131. }  
  132. Function UpdateSystemCol() {  
  133.     Param(  
  134.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem]$SourceItem,  
  135.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem]$ListItem,  
  136.         [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext]$destCtx   
  137.     )  
  138.     $authorUser = ""  
  139.     $editorUser = ""  
  140.     if (!([string]::IsNullOrEmpty($SourceItem["Author"].Email))) {  
  141.         #check user present in hashtable  
  142.         if ($global:spoUsers.ContainsKey($SourceItem["Author"].Email)) {  
  143.             $ListItem["Author"] = $global:spoUsers[$SourceItem["Author"].Email]  
  144.         }  
  145.         else {  
  146.             $authorUser = Ensure-SPOUser $SourceItem["Author"].Email $destCtx -isMulitUser $false  
  147.             $ListItem["Author"] = $authorUser  
  148.         }   
  149.     }   
  150.     elseif (([string]::IsNullOrEmpty($SourceItem["Author"].Email)) -or $authorUser -eq $null) {  
  151.         $ListItem["Author"] = $currentUser  
  152.     }  
  153.     if (!([string]::IsNullOrEmpty($SourceItem["Editor"].Email))) {  
  154.         #check user present in hashtable  
  155.         if ($global:spoUsers.ContainsKey($SourceItem["Editor"].Email)) {  
  156.             $ListItem["Editor"] = $global:spoUsers[$SourceItem["Editor"].Email]  
  157.         }  
  158.         else {  
  159.             $editorUser = Ensure-SPOUser $SourceItem["Editor"].Email $destCtx -isMulitUser $false  
  160.             $ListItem["Editor"] = $editorUser  
  161.         }   
  162.     }  
  163.     elseif (([string]::IsNullOrEmpty($SourceItem["Editor"].Email)) -or $editorUser -eq $null) {  
  164.         $ListItem["Editor"] = $currentUser  
  165.     }     
  166.     $ListItem["Created"] = $SourceItem["Created"]  
  167.     $ListItem["Modified"] = $SourceItem["Modified"]  
  168.     #$destCtx.Load($ListItem);  
  169.     #$destCtx.ExecuteQuery();  
  170.     return $ListItem  
  171. }  
  172. <#  
  173.   This function is used to create the Dummy-Items on destination list, untill it matches the item Id with  
  174.   destination Item Id.  
  175. #>  
  176. Function Create-Dummy-Item(){  
  177.  param(  
  178.   [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.List] $TargetList,  
  179.   [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext] $destCtx  
  180.  )  
  181.  $NewItem = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation  
  182.  $ListItem = $TargetList.AddItem($NewItem)  
  183.  $ListItem["Title"] = "Dummy Text"  
  184.  $ListItem.Update()  
  185.  $destCtx.ExecuteQuery();  
  186.  Write-Host "Created Dummy Item for Id: $($ListItem.Id)" -f DarkMagenta  
  187.  return $ListItem  
  188. }  
  189. Function Create-Item(){  
  190.     param(  
  191.     [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.List] $TargetList,  
  192.     [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItemVersionCollection] $versionColl,  
  193.     [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.FieldCollection] $SourceListFields,  
  194.     [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.FieldCollection] $TargetListFields,  
  195.     [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem] $SourceItem,  
  196.     [Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext] $destCtx,  
  197.     [Parameter(Mandatory = $true)] [int] $SourceListItemsCount,  
  198.     [Parameter(Mandatory = $true)] [int] $Counter  
  199.     )  
  200.     $NewItem = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation  
  201.     $ListItem = $TargetList.AddItem($NewItem)  
  202.     #check the number of version available. If version is greater than 1.0 then create item by iterating the for loop with descending order.  
  203.     Write-Progress -Activity "Copying List Items:" -Status "Copying Item ID '$($SourceItem.Id)' from Source List ($($Counter) of $($SourceListItemsCount))" -PercentComplete (($Counter / $SourceListItemsCount) * 100)  
  204.     #check the number of version.  
  205.     if ($versionColl.Count -gt 1) {  
  206.         for ($i = $versionColl.Count - 1; $i -ge 0; $i--) {  
  207.             $version = $versionColl[$i];  
  208.             Foreach ($SourceField in $SourceListFields) {  
  209.                 #Handle Special Fields  
  210.                 $FieldType = $SourceField.TypeAsString  
  211.                 #Write-Host "FieldType:" $FieldType  
  212.                 #Skip Read only, hidden fields, content type and attachments and fields is not User fields  
  213.                 If ((-Not ($SourceField.ReadOnlyField)) -and (-Not ($SourceField.Hidden)) -and ($SourceField.InternalName -ne "ContentType") -and ($SourceField.InternalName -ne "Attachments") ) {  
  214.                     $TargetField = $TargetListFields | Where-Object { $_.Internalname -eq $SourceField.Internalname }  
  215.                     if ($null -ne $TargetField -and ($FieldType -ne "User") -and ($FieldType -ne "UserMulti") -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor" -and $SourceField.InternalName -ne "Created" -and $SourceField.InternalName -ne "Modified") {  
  216.                         $ListItem[$TargetField.InternalName] = $version[$SourceField.InternalName]  
  217.                     }  
  218.                     elseif ((($FieldType -eq "User") -or ($FieldType -eq "UserMulti")) -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {  
  219.                         $ListItem = Update-User $FieldType $SourceField $TargetField $version $ListItem $destCtx   
  220.                     }  
  221.                           
  222.                 }  
  223.                       
  224.             }  
  225.             #To change the CreatedBy and Modified By.  
  226.             if ($i -eq $versionColl.Count - 1) {  
  227.                 $ListItem = UpdateSystemCol $SourceItem $ListItem $destCtx  
  228.             }  
  229.             else {  #To Changed Modified by only  
  230.                 $editorUser = ""  
  231.                 if (!([string]::IsNullOrEmpty($SourceItem["Editor"].Email))) {  
  232.                     #check user present in hashtable  
  233.                     if ($global:spoUsers.ContainsKey($SourceItem["Editor"].Email)) {  
  234.                         $ListItem["Editor"] = $global:spoUsers[$SourceItem["Editor"].Email]  
  235.                     }   
  236.                     else {  
  237.                         $editorUser = Ensure-SPOUser $SourceItem["Editor"].Email $destCtx -isMulitUser $false  
  238.                         $ListItem["Editor"] = $editorUser  
  239.                     }  
  240.                 }  
  241.                 elseif (([string]::IsNullOrEmpty($SourceItem["Editor"].Email)) -or $editorUser -eq $null) {  
  242.                     $ListItem["Editor"] = $currentUser  
  243.                 }  
  244.         
  245.                 $ListItem["Modified"] = $SourceItem["Modified"]  
  246.             }  
  247.             $ListItem.Update()  
  248.             $destCtx.ExecuteQuery()  
  249.         }  
  250.     }  
  251.     else {  
  252.         #If only one version available  
  253.         $version = $versionColl[0]  
  254.         Foreach ($SourceField in $SourceListFields) {   
  255.            # Write-Host "Id Value: "$version[$SourceField.InternalName]  
  256.             #Skip Read only, hidden fields, content type and attachments  
  257.             If ((-Not ($SourceField.ReadOnlyField)) -and (-Not ($SourceField.Hidden)) -and ($SourceField.InternalName -ne "ContentType") -and ($SourceField.InternalName -ne "Attachments") ) {  
  258.                 $TargetField = $TargetListFields | Where-Object { $_.Internalname -eq $SourceField.Internalname }  
  259.                 if ($null -ne $TargetField -and ($FieldType -ne "User") -and ($FieldType -ne "UserMulti") -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor" -and $SourceField.InternalName -ne "Created" -and $SourceField.InternalName -ne "Modified") {  
  260.                     $ListItem[$TargetField.InternalName] = $version[$SourceField.InternalName]  
  261.                 }  
  262.                 elseif ((($FieldType -eq "User") -or ($FieldType -eq "UserMulti")) -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {  
  263.                     $ListItem = Update-User $FieldType $SourceField $TargetField $version $ListItem $destCtx   
  264.                 }  
  265.             }  
  266.         }  
  267.         $ListItem = UpdateSystemCol $SourceItem $ListItem $destCtx  
  268.         $ListItem.Update()  
  269.         $destCtx.ExecuteQuery();  
  270.     }  
  271.     return $ListItem  
  272. }  
  273. Function Copy-ListItems() {  
  274.     param  
  275.     (  
  276.         [Parameter(Mandatory = $true)] [string] $siteURL,  
  277.         [Parameter(Mandatory = $true)] [string] $destSiteURL,  
  278.         [Parameter(Mandatory = $true)] [string] $SourceListName,  
  279.         [Parameter(Mandatory = $true)] [string] $TargetListName,  
  280.         [Parameter(Mandatory = $true)] [string] $query,  
  281.         [Parameter(Mandatory = $true)] [string] $Logfile  
  282.     )     
  283.     Try {  
  284.         If (!(test-path $Logfile)) {  
  285.             New-Item -Path $Logfile -Type File -Force | Out-Null  
  286.         }  
  287.         LogWrite "Copy-ListItems Fuction Called"   
  288.         $Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($fileCred.UserName, $fileCred.Password)  
  289.        
  290.         #Setup the source context  
  291.         $sourceCtx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
  292.         $sourceCtx.Credentials = $Cred  
  293.          
  294.         #Setup the destination Context  
  295.         $destCtx = New-Object Microsoft.SharePoint.Client.ClientContext($destSiteURL)  
  296.         $destCtx.Credentials = $Cred  
  297.         LogWrite "User Credential is valid and It is Successfully Login"  
  298.  
  299.         #Get Current loged User on destination Site  
  300.         $currentUser = $destCtx.Web.CurrentUser;  
  301.         $destCtx.Load($currentUser)  
  302.         $destCtx.ExecuteQuery()  
  303.   
  304.         $currentUser = $destCtx.Web.EnsureUser($currentUser.Email)  
  305.         $destCtx.Load($currentUser)  
  306.         $destCtx.ExecuteQuery()  
  307.  
  308.         #Get the Source List and Target Lists  
  309.         $SourceList = $sourceCtx.Web.Lists.GetByTitle($SourceListName)  
  310.         $TargetList = $destCtx.Web.Lists.GetByTitle($TargetListName)  
  311.          
  312.         #Get CAML Query object  
  313.         $camlquery = New-Object Microsoft.SharePoint.Client.CamlQuery;  
  314.         $camlquery.ViewXml = $query   
  315.         LogWrite "Query:" $query  
  316.  
  317.         #Get All Items from the Source List in batches  
  318.         Write-Progress -Activity "Reading Source..." -Status "Getting Items from Source List. Please wait..."  
  319.         $SourceListItems = $SourceList.GetItems($camlquery)  
  320.         $sourceCtx.Load($SourceListItems)  
  321.         $sourceCtx.ExecuteQuery()  
  322.         $SourceListItemsCount = $SourceListItems.count  
  323.         Write-host "Total Number of Items Found:"$SourceListItemsCount -foregroundcolor black -backgroundcolor Green  
  324.         LogWrite "Total Number of Items Found:" $SourceListItemsCount  
  325.         #Get All fields from Source List & Target List  
  326.         $SourceListFields = $SourceList.Fields  
  327.         $sourceCtx.Load($SourceListFields)  
  328.           
  329.         $TargetListFields = $TargetList.Fields  
  330.         $destCtx.Load($TargetListFields)         
  331.          
  332.         $sourceCtx.ExecuteQuery()  
  333.         $destCtx.ExecuteQuery()  
  334.          
  335.         #Loop through each item in the source and Get column values, add them to target  
  336.         [int]$Counter = 1  
  337.  
  338.         #Get each column value from source list and add them to target  
  339.         ForEach ($SourceItem in $SourceListItems) {   
  340.             $versionColl = $SourceItem.Versions  
  341.             $sourceCtx.Load($versionColl)  
  342.             $sourceCtx.ExecuteQuery()  
  343.             Write-Host "ID: "$SourceItem.ID "Version Count: " $versionColl.Count  
  344.             LogWrite "ID: "$SourceItem.ID "Version Count: " $versionColl.Count  
  345.             $ListItem = Create-Item -TargetList $TargetList -versionColl $versionColl -SourceListFields $SourceListFields -TargetListFields $TargetListFields -SourceItem $SourceItem -destCtx $destCtx -SourceListItemsCount $SourceListItemsCount -Counter $Counter  
  346.             $sourceId = $($SourceItem.Id)  
  347.             $destionationId = $($ListItem.Id)  
  348.             $dummyCount = [int]$sourceId - 1  
  349.             while($destionationId -ne $sourceId) {  
  350.                 if($sourceId -ne $destionationId)   
  351.                 {  
  352.                     Write-Host "Deleting the Item from destionation Site $($destionationId) as not equal to Source Item $($sourceId)" -ForegroundColor Yellow  
  353.                     $ListItem.DeleteObject()  
  354.                     $destCtx.ExecuteQuery()  
  355.                 }  
  356.                 Write-Host "Destionation Id : $($destionationId) : $($dummyCount) : if($($destionationId) -lt $($dummyCount))"  
  357.                 #check the destionation is less than source Id -1 to create dummy item in list.  
  358.                 if($destionationId -lt $dummyCount)  
  359.                 {  
  360.                   $ListItem = Create-Dummy-Item -TargetList $TargetList -destCtx $destCtx  
  361.                 }   
  362.                 else  
  363.                 {  
  364.                     $ListItem = Create-Item -TargetList $TargetList -versionColl $versionColl -SourceListFields $SourceListFields -TargetListFields $TargetListFields -SourceItem $SourceItem -destCtx $destCtx -SourceListItemsCount $SourceListItemsCount -Counter $Counter  
  365.                 }  
  366.                 $destionationId = $($ListItem.Id)  
  367.             }  
  368.             Write-Host "Copied Item ID from Source to Target List:$($SourceItem.Id) ($($Counter) of $($SourceListItemsCount))"  
  369.             $Counter++  
  370.         }  
  371.         write-host  -f Green "Total List Items Copied from '$SourceListName' to '$TargetListName' : $($SourceListItems.count)"  
  372.         LogWrite "Total List Items Copied from '$SourceListName' to '$TargetListName' : $($SourceListItems.count)"  
  373.     }  
  374.     Catch {  
  375.         write-host -f Red "Error Copying List Items!" $_.Exception.Message  
  376.         LogWrite "Error Copying List Items!" $_.Exception.Message  
  377.     }  
  378. }  
  379. #Set Parameters  
  380. $todayDate = (Get-Date).toString("yyyy_MM_dd")  
  381. $Logfile = "D:\Logs\copyListItems_" + $todayDate + ".txt"  
  382.   
  383. $srcListSiteUrl = "Your Source Site"      
  384. $SourceListName = "Employee"       
  385. $dstListSiteUrl = "Your Destionation Site"      
  386. $TargetListName = "Employee"  
  387. $sourceQuery = "<View>  
  388.                </View>"  
  389. #Passing Credentials  
  390. $credPath = 'D:\Arvind\safe\secretfile.txt'  
  391. $fileCred = Import-Clixml -path $credpath  
  392.  
  393. #Call the function to copy list items  
  394. Copy-ListItems -siteURL $srcListSiteUrl -destSiteURL $dstListSiteUrl -SourceListName $SourceListName -TargetListName $TargetListName -query $sourceQuery -logFile $Logfile  
Run the powershell scirpt and check the results on destination site.
 
Result
 
Copy List Items By Retaining ID In SharePoint Online
 
Conclusion
 
We have seen how to copy the list items from source site to destination site by retaining Id's and versions. And we can easily skip Id's by just commenting the while loop from Copy-Items function.
 
Hope this script will help you. Copy List Items By Retaining ID In SharePoint Online 
 
You can use another uploaded scirpt for creating columns and repairing lookup column CreateColumn.ps1 and RepairLookupColumn respectively.


Similar Articles