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

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.

#Passing Credentials
$credPath = 'D:\Arvind\safe\secretfile.txt'
$fileCred = Import-Clixml -path $credpath

Change the following parameters before running the script.

#Set Parameters
$todayDate = (Get-Date).toString("yyyy_MM_dd")
$Logfile = "D:\Logs\copyListItems_" + $todayDate + ".txt"
$srcListSiteUrl = "Your Source Site"
$SourceListName = "Employee"
$dstListSiteUrl = "Your Destionation Site"
$TargetListName = "Employee"
$sourceQuery = "<View>
</View>"

Copy the list items from source site Employee list using this code.

Function Copy-ListItems() {
param
(
[Parameter(Mandatory = $true)] [string] $siteURL,
[Parameter(Mandatory = $true)] [string] $destSiteURL,
[Parameter(Mandatory = $true)] [string] $SourceListName,
[Parameter(Mandatory = $true)] [string] $TargetListName,
[Parameter(Mandatory = $true)] [string] $query,
[Parameter(Mandatory = $true)] [string] $Logfile
)
Try {
If (!(test-path $Logfile)) {
New-Item -Path $Logfile -Type File -Force | Out-Null
}
LogWrite "Copy-ListItems Fuction Called"
$Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($fileCred.UserName, $fileCred.Password)
#Setup the source context
$sourceCtx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)
$sourceCtx.Credentials = $Cred
#Setup the destination Context
$destCtx = New-Object Microsoft.SharePoint.Client.ClientContext($destSiteURL)
$destCtx.Credentials = $Cred
LogWrite "User Credential is valid and It is Successfully Login"
#Get Current loged User on destination Site
$currentUser = $destCtx.Web.CurrentUser;
$destCtx.Load($currentUser)
$destCtx.ExecuteQuery()
$currentUser = $destCtx.Web.EnsureUser($currentUser.Email)
$destCtx.Load($currentUser)
$destCtx.ExecuteQuery()
#Get the Source List and Target Lists
$SourceList = $sourceCtx.Web.Lists.GetByTitle($SourceListName)
$TargetList = $destCtx.Web.Lists.GetByTitle($TargetListName)
#Get CAML Query object
$camlquery = New-Object Microsoft.SharePoint.Client.CamlQuery;
$camlquery.ViewXml = $query
LogWrite "Query:" $query
#Get All Items from the Source List in batches
Write-Progress -Activity "Reading Source..." -Status "Getting Items from Source List. Please wait..."
$SourceListItems = $SourceList.GetItems($camlquery)
$sourceCtx.Load($SourceListItems)
$sourceCtx.ExecuteQuery()
$SourceListItemsCount = $SourceListItems.count
Write-host "Total Number of Items Found:"$SourceListItemsCount -foregroundcolor black -backgroundcolor Green
LogWrite "Total Number of Items Found:" $SourceListItemsCount
#Get All fields from Source List & Target List
$SourceListFields = $SourceList.Fields
$sourceCtx.Load($SourceListFields)
$TargetListFields = $TargetList.Fields
$destCtx.Load($TargetListFields)
$sourceCtx.ExecuteQuery()
$destCtx.ExecuteQuery()
#Loop through each item in the source and Get column values, add them to target
[int]$Counter = 1
#Get each column value from source list and add them to target
ForEach ($SourceItem in $SourceListItems) {
$versionColl = $SourceItem.Versions
$sourceCtx.Load($versionColl)
$sourceCtx.ExecuteQuery()
Write-Host "ID: "$SourceItem.ID "Version Count: " $versionColl.Count
LogWrite "ID: "$SourceItem.ID "Version Count: " $versionColl.Count
$ListItem = Create-Item -TargetList $TargetList -versionColl $versionColl -SourceListFields $SourceListFields -TargetListFields $TargetListFields -SourceItem $SourceItem -destCtx $destCtx -SourceListItemsCount $SourceListItemsCount -Counter $Counter
$sourceId = $($SourceItem.Id)
$destionationId = $($ListItem.Id)
$dummyCount = [int]$sourceId - 1
while($destionationId -ne $sourceId) {
if($sourceId -ne $destionationId)
{
Write-Host "Deleting the Item from destionation Site $($destionationId) as not equal to Source Item $($sourceId)" -ForegroundColor Yellow
$ListItem.DeleteObject()
$destCtx.ExecuteQuery()
}
Write-Host "Destionation Id : $($destionationId) : $($dummyCount) : if($($destionationId) -lt $($dummyCount))"
#check the destionation is less than source Id -1 to create dummy item in list.
if($destionationId -lt $dummyCount)
{
$ListItem = Create-Dummy-Item -TargetList $TargetList -destCtx $destCtx
}
else
{
$ListItem = Create-Item -TargetList $TargetList -versionColl $versionColl -SourceListFields $SourceListFields -TargetListFields $TargetListFields -SourceItem $SourceItem -destCtx $destCtx -SourceListItemsCount $SourceListItemsCount -Counter $Counter
}
$destionationId = $($ListItem.Id)
}
Write-Host "Copied Item ID from Source to Target List:$($SourceItem.Id) ($($Counter) of $($SourceListItemsCount))"
$Counter++
}
write-host -f Green "Total List Items Copied from '$SourceListName' to '$TargetListName' : $($SourceListItems.count)"
LogWrite "Total List Items Copied from '$SourceListName' to '$TargetListName' : $($SourceListItems.count)"
}
Catch {
write-host -f Red "Error Copying List Items!" $_.Exception.Message
LogWrite "Error Copying List Items!" $_.Exception.Message
}
}

Create items on destination site with this code.

Function Create-Item(){
param(
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.List] $TargetList,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItemVersionCollection] $versionColl,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.FieldCollection] $SourceListFields,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.FieldCollection] $TargetListFields,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem] $SourceItem,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext] $destCtx,
[Parameter(Mandatory = $true)] [int] $SourceListItemsCount,
[Parameter(Mandatory = $true)] [int] $Counter
)
$NewItem = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$ListItem = $TargetList.AddItem($NewItem)
#check the number of version available. If version is greater than 1.0 then create item by iterating the for loop with descending order.
Write-Progress -Activity "Copying List Items:" -Status "Copying Item ID '$($SourceItem.Id)' from Source List ($($Counter) of $($SourceListItemsCount))" -PercentComplete (($Counter / $SourceListItemsCount) * 100)
#check the number of version.
if ($versionColl.Count -gt 1) {
for ($i = $versionColl.Count - 1; $i -ge 0; $i--) {
$version = $versionColl[$i];
Foreach ($SourceField in $SourceListFields) {
#Handle Special Fields
$FieldType = $SourceField.TypeAsString
#Write-Host "FieldType:" $FieldType
#Skip Read only, hidden fields, content type and attachments and fields is not User fields
If ((-Not ($SourceField.ReadOnlyField)) -and (-Not ($SourceField.Hidden)) -and ($SourceField.InternalName -ne "ContentType") -and ($SourceField.InternalName -ne "Attachments") ) {
$TargetField = $TargetListFields | Where-Object { $_.Internalname -eq $SourceField.Internalname }
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") {
$ListItem[$TargetField.InternalName] = $version[$SourceField.InternalName]
}
elseif ((($FieldType -eq "User") -or ($FieldType -eq "UserMulti")) -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {
$ListItem = Update-User $FieldType $SourceField $TargetField $version $ListItem $destCtx
}
}
}
#To change the CreatedBy and Modified By.
if ($i -eq $versionColl.Count - 1) {
$ListItem = UpdateSystemCol $SourceItem $ListItem $destCtx
}
else { #To Changed Modified by only
$editorUser = ""
if (!([string]::IsNullOrEmpty($SourceItem["Editor"].Email))) {
#check user present in hashtable
if ($global:spoUsers.ContainsKey($SourceItem["Editor"].Email)) {
$ListItem["Editor"] = $global:spoUsers[$SourceItem["Editor"].Email]
}
else {
$editorUser = Ensure-SPOUser $SourceItem["Editor"].Email $destCtx -isMulitUser $false
$ListItem["Editor"] = $editorUser
}
}
elseif (([string]::IsNullOrEmpty($SourceItem["Editor"].Email)) -or $editorUser -eq $null) {
$ListItem["Editor"] = $currentUser
}
$ListItem["Modified"] = $SourceItem["Modified"]
}
$ListItem.Update()
$destCtx.ExecuteQuery()
}
}
else {
#If only one version available
$version = $versionColl[0]
Foreach ($SourceField in $SourceListFields) {
# Write-Host "Id Value: "$version[$SourceField.InternalName]
#Skip Read only, hidden fields, content type and attachments
If ((-Not ($SourceField.ReadOnlyField)) -and (-Not ($SourceField.Hidden)) -and ($SourceField.InternalName -ne "ContentType") -and ($SourceField.InternalName -ne "Attachments") ) {
$TargetField = $TargetListFields | Where-Object { $_.Internalname -eq $SourceField.Internalname }
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") {
$ListItem[$TargetField.InternalName] = $version[$SourceField.InternalName]
}
elseif ((($FieldType -eq "User") -or ($FieldType -eq "UserMulti")) -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {
$ListItem = Update-User $FieldType $SourceField $TargetField $version $ListItem $destCtx
}
}
}
$ListItem = UpdateSystemCol $SourceItem $ListItem $destCtx
$ListItem.Update()
$destCtx.ExecuteQuery();
}
return $ListItem
}

Maintain Created, Created By, Modified, Modified By with this code.

Function UpdateSystemCol() {
Param(
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem]$SourceItem,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem]$ListItem,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext]$destCtx
)
$authorUser = ""
$editorUser = ""
if (!([string]::IsNullOrEmpty($SourceItem["Author"].Email))) {
#check user present in hashtable
if ($global:spoUsers.ContainsKey($SourceItem["Author"].Email)) {
$ListItem["Author"] = $global:spoUsers[$SourceItem["Author"].Email]
}
else {
$authorUser = Ensure-SPOUser $SourceItem["Author"].Email $destCtx -isMulitUser $false
$ListItem["Author"] = $authorUser
}
}
elseif (([string]::IsNullOrEmpty($SourceItem["Author"].Email)) -or $authorUser -eq $null) {
$ListItem["Author"] = $currentUser
}
if (!([string]::IsNullOrEmpty($SourceItem["Editor"].Email))) {
#check user present in hashtable
if ($global:spoUsers.ContainsKey($SourceItem["Editor"].Email)) {
$ListItem["Editor"] = $global:spoUsers[$SourceItem["Editor"].Email]
}
else {
$editorUser = Ensure-SPOUser $SourceItem["Editor"].Email $destCtx -isMulitUser $false
$ListItem["Editor"] = $editorUser
}
}
elseif (([string]::IsNullOrEmpty($SourceItem["Editor"].Email)) -or $editorUser -eq $null) {
$ListItem["Editor"] = $currentUser
}
$ListItem["Created"] = $SourceItem["Created"]
$ListItem["Modified"] = $SourceItem["Modified"]
#$destCtx.Load($ListItem);
#$destCtx.ExecuteQuery();
return $ListItem
}

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.

Function Ensure-SPOUser() {
Param(
[Parameter(Mandatory = $true)] [string]$emailID,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext]$Ctx,
[Parameter(Mandatory = $true)] [boolean]$isMulitUser
)
Try {
#ensure sharepoint online user
Write-Host "Verify User" $emailID
LogWrite "Verify User" $emailID
$Web = $Ctx.Web
$User = $Web.EnsureUser($emailID)
$Ctx.Load($User)
$global:spoUsers.Add($emailID , $User)
if($isMulitUser){
$Ctx.ExecuteQuery()
}
return $User
}
Catch {
#write-host -f Red "Error:" $_.Exception.Message
return $null
}
}

To update people and group field use this code.

Function Update-User() {
Param(
[Parameter(Mandatory = $true)] $FieldType,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.Field]$SourceField,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.Field]$TargetField,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItemVersion]$version,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem]$ListItem,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext]$destCtx
)
#check field is user field other than author and editor
if ($FieldType -eq "User" -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {
$FieldValue = [Microsoft.SharePoint.Client.FieldUserValue]$version[$SourceField.InternalName]
Write-Host "Single User Value: $($FieldValue.LookupId) : $($FieldValue.LookupValue) : $($FieldValue.Email)"
#If Field value is not null
if ($null -ne $FieldValue) {
$SingleUser = ""
# Get the user value from hash table
if ($global:spoUsers.ContainsKey($FieldValue.Email)) {
$SingleUser = $global:spoUsers[$FieldValue.Email]
}
else { # IF user is not present in hashtable enuse the user.
$SingleUser = Ensure-SPOUser -emailID $FieldValue.Email -Ctx $destCtx -isMulitUser $false
}
if ($null -ne $SingleUser ) {
$ListItem[$TargetField.InternalName] = $SingleUser # add the user into user field
}
}
}
#check field is Multi User field other than author and editor
if ($FieldType -eq "UserMulti" -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {
#Get the Column Values
$FieldValues = [Microsoft.SharePoint.Client.FieldUserValue[]]$version[$SourceField.InternalName]
Write-host -f Yellow "Number of User Present in Field $($SourceField.InternalName) is : $($FieldValues.Count)"
#Get Each User from the collection
$UserValueColl = @()
ForEach ($FieldValue in $FieldValues) {
#Get the Display Name and Email Field
Write-Host "MultiUser Value are $($FieldValue.LookupId) : $($FieldValue.LookupValue) : $($FieldValue.Email) " -f Green
$SPOUser = ""
# Check user present in hashtable
if ($global:spoUsers.ContainsKey($FieldValue.Email)) {
$SPOUser = $global:spoUsers[$FieldValue.Email]
}
else {
$SPOUser = Ensure-SPOUser -emailID $FieldValue.Email -Ctx $destCtx -isMulitUser $true
}
if ($null -ne $SPOUser) {
$SPOUserValue = New-Object Microsoft.SharePoint.Client.FieldUserValue
$SPOUserValue.LookupId = $SPOUser.Id
$UserValueColl += $SPOUserValue
}
}
If ($UserValueColl.length -gt 0) {
$UserValueCollCollection = [Microsoft.SharePoint.Client.FieldUserValue[]]$UserValueColl
#Update the Multi-People picker column
$ListItem[$TargetField.InternalName] = $UserValueCollCollection
}
}
return $ListItem
}

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.

$sourceId = $($SourceItem.Id)
$destionationId = $($ListItem.Id)
$dummyCount = [int]$sourceId - 1
while($destionationId -ne $sourceId) {
if($sourceId -ne $destionationId)
{
Write-Host "Deleting the Item from destionation Site $($destionationId) as not equal to Source Item $($sourceId)" -ForegroundColor Yellow
$ListItem.DeleteObject()
$destCtx.ExecuteQuery()
}
Write-Host "Destionation Id : $($destionationId) : $($dummyCount) : if($($destionationId) -lt $($dummyCount))"
#check the destionation is less than source Id -1 to create dummy item in list.
if($destionationId -lt $dummyCount)
{
$ListItem = Create-Dummy-Item -TargetList $TargetList -destCtx $destCtx
}
else
{
$ListItem = Create-Item -TargetList $TargetList -versionColl $versionColl -SourceListFields $SourceListFields -TargetListFields $TargetListFields -SourceItem $SourceItem -destCtx $destCtx -SourceListItemsCount $SourceListItemsCount -Counter $Counter
}
$destionationId = $($ListItem.Id)
}
Function Create-Dummy-Item(){
param(
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.List] $TargetList,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext] $destCtx
)
$NewItem = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$ListItem = $TargetList.AddItem($NewItem)
$ListItem["Title"] = "Dummy Text"
$ListItem.Update()
$destCtx.ExecuteQuery();
Write-Host "Created Dummy Item for Id: $($ListItem.Id)" -f DarkMagenta
return $ListItem
}

The complete script will look like:

<#
This Script alllow us to copy items from source list to destination list.
***************************************************************************************************
Prerequisites
***************************************************************************************************
1 - The script requires SharePoint Online SDK, Which can be downloaded here:
https://www.microsoft.com/en-in/download/details.aspx?id=42038
2 - Create the list on destination site prior running this scirpt.
3 - Create the same column type on destination site prior running this scirpt.
***************************************************************************************************
Required Parameters
***************************************************************************************************
1. $srcListSiteUrl
2. $dstListSiteUrl
3. $SourceListName
4. $TargetListName
5. $sourceQuery
6. $Logfile
***************************************************************************************************
Created by : Arvind Kushwaha
Created Date : 25-05-2020
version : 1.0
***************************************************************************************************
***************************************************************************************************
Use of the script:
***************************************************************************************************
Copy-ListItems -siteURL $srcListSiteUrl -destSiteURL $dstListSiteUrl -SourceListName $SourceListName -TargetListName $TargetListName -query $sourceQuery -logFile $Logfile
#>
#Set Global Variable
$global:spoUsers = @{ };
#Load SharePoint CSOM Assemblies
Add-Type -path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll'
Add-Type -path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll'
Function Update-User() {
Param(
[Parameter(Mandatory = $true)] $FieldType,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.Field]$SourceField,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.Field]$TargetField,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItemVersion]$version,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem]$ListItem,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext]$destCtx
)
#check field is user field other than author and editor
if ($FieldType -eq "User" -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {
$FieldValue = [Microsoft.SharePoint.Client.FieldUserValue]$version[$SourceField.InternalName]
Write-Host "Single User Value: $($FieldValue.LookupId) : $($FieldValue.LookupValue) : $($FieldValue.Email)"
#If Field value is not null
if ($null -ne $FieldValue) {
$SingleUser = ""
# Get the user value from hash table
if ($global:spoUsers.ContainsKey($FieldValue.Email)) {
$SingleUser = $global:spoUsers[$FieldValue.Email]
}
else { # IF user is not present in hashtable enuse the user.
$SingleUser = Ensure-SPOUser -emailID $FieldValue.Email -Ctx $destCtx -isMulitUser $false
}
if ($null -ne $SingleUser ) {
$ListItem[$TargetField.InternalName] = $SingleUser # add the user into user field
}
}
}
#check field is Multi User field other than author and editor
if ($FieldType -eq "UserMulti" -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {
#Get the Column Values
$FieldValues = [Microsoft.SharePoint.Client.FieldUserValue[]]$version[$SourceField.InternalName]
Write-host -f Yellow "Number of User Present in Field $($SourceField.InternalName) is : $($FieldValues.Count)"
#Get Each User from the collection
$UserValueColl = @()
ForEach ($FieldValue in $FieldValues) {
#Get the Display Name and Email Field
Write-Host "MultiUser Value are $($FieldValue.LookupId) : $($FieldValue.LookupValue) : $($FieldValue.Email) " -f Green
$SPOUser = ""
# Check user present in hashtable
if ($global:spoUsers.ContainsKey($FieldValue.Email)) {
$SPOUser = $global:spoUsers[$FieldValue.Email]
}
else {
$SPOUser = Ensure-SPOUser -emailID $FieldValue.Email -Ctx $destCtx -isMulitUser $true
}
if ($null -ne $SPOUser) {
$SPOUserValue = New-Object Microsoft.SharePoint.Client.FieldUserValue
$SPOUserValue.LookupId = $SPOUser.Id
$UserValueColl += $SPOUserValue
}
}
If ($UserValueColl.length -gt 0) {
$UserValueCollCollection = [Microsoft.SharePoint.Client.FieldUserValue[]]$UserValueColl
#Update the Multi-People picker column
$ListItem[$TargetField.InternalName] = $UserValueCollCollection
}
}
return $ListItem
}
Function LogWrite {
Param ([string]$logstring)
Add-content $Logfile -value $logstring
}
Function Ensure-SPOUser() {
Param(
[Parameter(Mandatory = $true)] [string]$emailID,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext]$Ctx,
[Parameter(Mandatory = $true)] [boolean]$isMulitUser
)
Try {
#ensure sharepoint online user
Write-Host "Verify User" $emailID
LogWrite "Verify User" $emailID
$Web = $Ctx.Web
$User = $Web.EnsureUser($emailID)
$Ctx.Load($User)
$global:spoUsers.Add($emailID , $User)
if($isMulitUser){
$Ctx.ExecuteQuery()
}
return $User
}
Catch {
#write-host -f Red "Error:" $_.Exception.Message
return $null
}
}
Function UpdateSystemCol() {
Param(
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem]$SourceItem,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem]$ListItem,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext]$destCtx
)
$authorUser = ""
$editorUser = ""
if (!([string]::IsNullOrEmpty($SourceItem["Author"].Email))) {
#check user present in hashtable
if ($global:spoUsers.ContainsKey($SourceItem["Author"].Email)) {
$ListItem["Author"] = $global:spoUsers[$SourceItem["Author"].Email]
}
else {
$authorUser = Ensure-SPOUser $SourceItem["Author"].Email $destCtx -isMulitUser $false
$ListItem["Author"] = $authorUser
}
}
elseif (([string]::IsNullOrEmpty($SourceItem["Author"].Email)) -or $authorUser -eq $null) {
$ListItem["Author"] = $currentUser
}
if (!([string]::IsNullOrEmpty($SourceItem["Editor"].Email))) {
#check user present in hashtable
if ($global:spoUsers.ContainsKey($SourceItem["Editor"].Email)) {
$ListItem["Editor"] = $global:spoUsers[$SourceItem["Editor"].Email]
}
else {
$editorUser = Ensure-SPOUser $SourceItem["Editor"].Email $destCtx -isMulitUser $false
$ListItem["Editor"] = $editorUser
}
}
elseif (([string]::IsNullOrEmpty($SourceItem["Editor"].Email)) -or $editorUser -eq $null) {
$ListItem["Editor"] = $currentUser
}
$ListItem["Created"] = $SourceItem["Created"]
$ListItem["Modified"] = $SourceItem["Modified"]
#$destCtx.Load($ListItem);
#$destCtx.ExecuteQuery();
return $ListItem
}
<#
This function is used to create the Dummy-Items on destination list, untill it matches the item Id with
destination Item Id.
#>
Function Create-Dummy-Item(){
param(
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.List] $TargetList,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext] $destCtx
)
$NewItem = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$ListItem = $TargetList.AddItem($NewItem)
$ListItem["Title"] = "Dummy Text"
$ListItem.Update()
$destCtx.ExecuteQuery();
Write-Host "Created Dummy Item for Id: $($ListItem.Id)" -f DarkMagenta
return $ListItem
}
Function Create-Item(){
param(
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.List] $TargetList,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItemVersionCollection] $versionColl,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.FieldCollection] $SourceListFields,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.FieldCollection] $TargetListFields,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ListItem] $SourceItem,
[Parameter(Mandatory = $true)] [Microsoft.SharePoint.Client.ClientContext] $destCtx,
[Parameter(Mandatory = $true)] [int] $SourceListItemsCount,
[Parameter(Mandatory = $true)] [int] $Counter
)
$NewItem = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$ListItem = $TargetList.AddItem($NewItem)
#check the number of version available. If version is greater than 1.0 then create item by iterating the for loop with descending order.
Write-Progress -Activity "Copying List Items:" -Status "Copying Item ID '$($SourceItem.Id)' from Source List ($($Counter) of $($SourceListItemsCount))" -PercentComplete (($Counter / $SourceListItemsCount) * 100)
#check the number of version.
if ($versionColl.Count -gt 1) {
for ($i = $versionColl.Count - 1; $i -ge 0; $i--) {
$version = $versionColl[$i];
Foreach ($SourceField in $SourceListFields) {
#Handle Special Fields
$FieldType = $SourceField.TypeAsString
#Write-Host "FieldType:" $FieldType
#Skip Read only, hidden fields, content type and attachments and fields is not User fields
If ((-Not ($SourceField.ReadOnlyField)) -and (-Not ($SourceField.Hidden)) -and ($SourceField.InternalName -ne "ContentType") -and ($SourceField.InternalName -ne "Attachments") ) {
$TargetField = $TargetListFields | Where-Object { $_.Internalname -eq $SourceField.Internalname }
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") {
$ListItem[$TargetField.InternalName] = $version[$SourceField.InternalName]
}
elseif ((($FieldType -eq "User") -or ($FieldType -eq "UserMulti")) -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {
$ListItem = Update-User $FieldType $SourceField $TargetField $version $ListItem $destCtx
}
}
}
#To change the CreatedBy and Modified By.
if ($i -eq $versionColl.Count - 1) {
$ListItem = UpdateSystemCol $SourceItem $ListItem $destCtx
}
else { #To Changed Modified by only
$editorUser = ""
if (!([string]::IsNullOrEmpty($SourceItem["Editor"].Email))) {
#check user present in hashtable
if ($global:spoUsers.ContainsKey($SourceItem["Editor"].Email)) {
$ListItem["Editor"] = $global:spoUsers[$SourceItem["Editor"].Email]
}
else {
$editorUser = Ensure-SPOUser $SourceItem["Editor"].Email $destCtx -isMulitUser $false
$ListItem["Editor"] = $editorUser
}
}
elseif (([string]::IsNullOrEmpty($SourceItem["Editor"].Email)) -or $editorUser -eq $null) {
$ListItem["Editor"] = $currentUser
}
$ListItem["Modified"] = $SourceItem["Modified"]
}
$ListItem.Update()
$destCtx.ExecuteQuery()
}
}
else {
#If only one version available
$version = $versionColl[0]
Foreach ($SourceField in $SourceListFields) {
# Write-Host "Id Value: "$version[$SourceField.InternalName]
#Skip Read only, hidden fields, content type and attachments
If ((-Not ($SourceField.ReadOnlyField)) -and (-Not ($SourceField.Hidden)) -and ($SourceField.InternalName -ne "ContentType") -and ($SourceField.InternalName -ne "Attachments") ) {
$TargetField = $TargetListFields | Where-Object { $_.Internalname -eq $SourceField.Internalname }
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") {
$ListItem[$TargetField.InternalName] = $version[$SourceField.InternalName]
}
elseif ((($FieldType -eq "User") -or ($FieldType -eq "UserMulti")) -and $SourceField.InternalName -ne "Author" -and $SourceField.InternalName -ne "Editor") {
$ListItem = Update-User $FieldType $SourceField $TargetField $version $ListItem $destCtx
}
}
}
$ListItem = UpdateSystemCol $SourceItem $ListItem $destCtx
$ListItem.Update()
$destCtx.ExecuteQuery();
}
return $ListItem
}
Function Copy-ListItems() {
param
(
[Parameter(Mandatory = $true)] [string] $siteURL,
[Parameter(Mandatory = $true)] [string] $destSiteURL,
[Parameter(Mandatory = $true)] [string] $SourceListName,
[Parameter(Mandatory = $true)] [string] $TargetListName,
[Parameter(Mandatory = $true)] [string] $query,
[Parameter(Mandatory = $true)] [string] $Logfile
)
Try {
If (!(test-path $Logfile)) {
New-Item -Path $Logfile -Type File -Force | Out-Null
}
LogWrite "Copy-ListItems Fuction Called"
$Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($fileCred.UserName, $fileCred.Password)
#Setup the source context
$sourceCtx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)
$sourceCtx.Credentials = $Cred
#Setup the destination Context
$destCtx = New-Object Microsoft.SharePoint.Client.ClientContext($destSiteURL)
$destCtx.Credentials = $Cred
LogWrite "User Credential is valid and It is Successfully Login"
#Get Current loged User on destination Site
$currentUser = $destCtx.Web.CurrentUser;
$destCtx.Load($currentUser)
$destCtx.ExecuteQuery()
$currentUser = $destCtx.Web.EnsureUser($currentUser.Email)
$destCtx.Load($currentUser)
$destCtx.ExecuteQuery()
#Get the Source List and Target Lists
$SourceList = $sourceCtx.Web.Lists.GetByTitle($SourceListName)
$TargetList = $destCtx.Web.Lists.GetByTitle($TargetListName)
#Get CAML Query object
$camlquery = New-Object Microsoft.SharePoint.Client.CamlQuery;
$camlquery.ViewXml = $query
LogWrite "Query:" $query
#Get All Items from the Source List in batches
Write-Progress -Activity "Reading Source..." -Status "Getting Items from Source List. Please wait..."
$SourceListItems = $SourceList.GetItems($camlquery)
$sourceCtx.Load($SourceListItems)
$sourceCtx.ExecuteQuery()
$SourceListItemsCount = $SourceListItems.count
Write-host "Total Number of Items Found:"$SourceListItemsCount -foregroundcolor black -backgroundcolor Green
LogWrite "Total Number of Items Found:" $SourceListItemsCount
#Get All fields from Source List & Target List
$SourceListFields = $SourceList.Fields
$sourceCtx.Load($SourceListFields)
$TargetListFields = $TargetList.Fields
$destCtx.Load($TargetListFields)
$sourceCtx.ExecuteQuery()
$destCtx.ExecuteQuery()
#Loop through each item in the source and Get column values, add them to target
[int]$Counter = 1
#Get each column value from source list and add them to target
ForEach ($SourceItem in $SourceListItems) {
$versionColl = $SourceItem.Versions
$sourceCtx.Load($versionColl)
$sourceCtx.ExecuteQuery()
Write-Host "ID: "$SourceItem.ID "Version Count: " $versionColl.Count
LogWrite "ID: "$SourceItem.ID "Version Count: " $versionColl.Count
$ListItem = Create-Item -TargetList $TargetList -versionColl $versionColl -SourceListFields $SourceListFields -TargetListFields $TargetListFields -SourceItem $SourceItem -destCtx $destCtx -SourceListItemsCount $SourceListItemsCount -Counter $Counter
$sourceId = $($SourceItem.Id)
$destionationId = $($ListItem.Id)
$dummyCount = [int]$sourceId - 1
while($destionationId -ne $sourceId) {
if($sourceId -ne $destionationId)
{
Write-Host "Deleting the Item from destionation Site $($destionationId) as not equal to Source Item $($sourceId)" -ForegroundColor Yellow
$ListItem.DeleteObject()
$destCtx.ExecuteQuery()
}
Write-Host "Destionation Id : $($destionationId) : $($dummyCount) : if($($destionationId) -lt $($dummyCount))"
#check the destionation is less than source Id -1 to create dummy item in list.
if($destionationId -lt $dummyCount)
{
$ListItem = Create-Dummy-Item -TargetList $TargetList -destCtx $destCtx
}
else
{
$ListItem = Create-Item -TargetList $TargetList -versionColl $versionColl -SourceListFields $SourceListFields -TargetListFields $TargetListFields -SourceItem $SourceItem -destCtx $destCtx -SourceListItemsCount $SourceListItemsCount -Counter $Counter
}
$destionationId = $($ListItem.Id)
}
Write-Host "Copied Item ID from Source to Target List:$($SourceItem.Id) ($($Counter) of $($SourceListItemsCount))"
$Counter++
}
write-host -f Green "Total List Items Copied from '$SourceListName' to '$TargetListName' : $($SourceListItems.count)"
LogWrite "Total List Items Copied from '$SourceListName' to '$TargetListName' : $($SourceListItems.count)"
}
Catch {
write-host -f Red "Error Copying List Items!" $_.Exception.Message
LogWrite "Error Copying List Items!" $_.Exception.Message
}
}
#Set Parameters
$todayDate = (Get-Date).toString("yyyy_MM_dd")
$Logfile = "D:\Logs\copyListItems_" + $todayDate + ".txt"
$srcListSiteUrl = "Your Source Site"
$SourceListName = "Employee"
$dstListSiteUrl = "Your Destionation Site"
$TargetListName = "Employee"
$sourceQuery = "<View>
</View>"
#Passing Credentials
$credPath = 'D:\Arvind\safe\secretfile.txt'
$fileCred = Import-Clixml -path $credpath
#Call the function to copy list items
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

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.

3 Comments

Join the conversation! Your thoughts help the community grow.

Monsur Ahmed5y ago
If user who created or edited the item is no more with the company then I am getting this error Error Copying List Items! Exception calling "ExecuteQuery" with "0" argument(s): "The specified user 'useremail' could not be found." How can I solve this issue?
- Arvind Kushwaha5y ago
  Create one dummy user...Add add dummy user for the user who left the company.
- Arvind Kushwaha5y ago
  If you don,t want to add the dummy user then follow the below method for list migration. For migration from sharepoint on premise to on premise use https://www.c-sharpcorner.com/blogs/migrate-sharepoint-list-from-2007-to-2013