Introduction

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