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"
  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)
  18. #Setup the source context
  19. $sourceCtx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)
  20. $sourceCtx.Credentials = $Cred
  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"
  27. #Get Current loged User on destination Site
  28. $currentUser = $destCtx.Web.CurrentUser;
  29. $destCtx.Load($currentUser)
  30. $destCtx.ExecuteQuery()
  32. $currentUser = $destCtx.Web.EnsureUser($currentUser.Email)
  33. $destCtx.Load($currentUser)
  34. $destCtx.ExecuteQuery()
  36. #Get the Source List and Target Lists
  37. $SourceList = $sourceCtx.Web.Lists.GetByTitle($SourceListName)
  38. $TargetList = $destCtx.Web.Lists.GetByTitle($TargetListName)
  40. #Get CAML Query object
  41. $camlquery = New-Object Microsoft.SharePoint.Client.CamlQuery;
  42. $camlquery.ViewXml = $query
  43. LogWrite "Query:" $query
  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)
  57. $TargetListFields = $TargetList.Fields
  58. $destCtx.Load($TargetListFields)
  60. $sourceCtx.ExecuteQuery()
  61. $destCtx.ExecuteQuery()
  63. #Loop through each item in the source and Get column values, add them to target
  64. [int]$Counter = 1
  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. }
  34. }
  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. }
  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.
  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. }
  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. }
  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.
  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
  10. 2 - Create the list on destination site prior running this scirpt.
  12. 3 - Create the same column type on destination site prior running this scirpt.
  14. ***************************************************************************************************
  15. Required Parameters
  16. ***************************************************************************************************
  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. ***************************************************************************************************
  30. ***************************************************************************************************
  31. Use of the script:
  32. ***************************************************************************************************
  34. Copy-ListItems -siteURL $srcListSiteUrl -destSiteURL $dstListSiteUrl -SourceListName $SourceListName -TargetListName $TargetListName -query $sourceQuery -logFile $Logfile
  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.
  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. }
  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. }
  222. }
  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. }
  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)
  290. #Setup the source context
  291. $sourceCtx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)
  292. $sourceCtx.Credentials = $Cred
  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"
  299. #Get Current loged User on destination Site
  300. $currentUser = $destCtx.Web.CurrentUser;
  301. $destCtx.Load($currentUser)
  302. $destCtx.ExecuteQuery()
  304. $currentUser = $destCtx.Web.EnsureUser($currentUser.Email)
  305. $destCtx.Load($currentUser)
  306. $destCtx.ExecuteQuery()
  308. #Get the Source List and Target Lists
  309. $SourceList = $sourceCtx.Web.Lists.GetByTitle($SourceListName)
  310. $TargetList = $destCtx.Web.Lists.GetByTitle($TargetListName)
  312. #Get CAML Query object
  313. $camlquery = New-Object Microsoft.SharePoint.Client.CamlQuery;
  314. $camlquery.ViewXml = $query
  315. LogWrite "Query:" $query
  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)
  329. $TargetListFields = $TargetList.Fields
  330. $destCtx.Load($TargetListFields)
  332. $sourceCtx.ExecuteQuery()
  333. $destCtx.ExecuteQuery()
  335. #Loop through each item in the source and Get column values, add them to target
  336. [int]$Counter = 1
  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"
  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
  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.