Introduction

Hi guys, let's discuss an amazing way for a SharePoint List Column to be updated as an Owner Column User Field Type via PowerShell scripting.
It's used after a quick export all the Excel sheet based records onto a SharePoint List with different type of field types.
But what if the Title column has values which have to be converted to the User field property?
Note
The discussed approach is only applicable on an SP On-Prem site collection!
Prerequisites
Windows PowerShell ISE should be installed on the system.
Install the SharePoint Client DLLs after downloading them in some respective paths and mention them in the PS code as shown below.
Valid login details need to be hard coded in the last UserName, Password section in order to run the script without giving the login details at the run time and achieve a seamless program running.
PowerShell script
  1. #reference the SharePoint Client DLLs
  2. Add - Type - Path ".\Microsoft.SharePoint.Client.dll" | Out - Null
  3. Add - Type - Path ".\Microsoft.SharePoint.Client.Runtime.dll" | Out - Null
  4. #Add - Type - Path "C:\Users\123594\Documents\Veera Kaveri\BoxUpdate\BoxUpdate\Microsoft.SharePoint.Client.dll" | Out - Null
  5. #Add - Type - Path "C:\Users\123594\Documents\Veera Kaveri\BoxUpdate\BoxUpdate\Microsoft.SharePoint.Client.Runtime.dll" | Out - Null
  7. function Get - SPOSites {
  8. Param(
  9. [Microsoft.SharePoint.Client.ClientContext] $Context,
  10. [Microsoft.SharePoint.Client.Web] $RootWeb)
  11. #Create array variable to store data
  12. # $siteitems = $null
  13. #$siteitems = @()
  14. $RunDateTime = Get - Date - Format G
  15. #get all webs under root web
  16. $Webs = $RootWeb.Webs
  17. $Context.Load($Webs)
  18. $Context.ExecuteQuery()
  19. #loop through the webs
  20. ForEach($sWeb in $Webs) {
  21. Write - Host $sWeb.url
  22. #Create array variable to store data
  23. $siteitems = $null
  24. $siteUrl = $sWeb.Url;
  25. #if($siteUrl - Match 'http://test.sample.com/sites/EU2/teams/e-Business') {
  26. #if($siteUrl - match 'http://test.sample.com/sites/EU3/tvoe') {
  27. if ($siteUrl - match 'http://eu.test.sample.com/sites/AdvEU3/Megamigration') {
  28. #get all lists in web
  29. $AllLists = $sWeb.Lists
  30. $Context.Load($AllLists)
  31. $Context.ExecuteQuery()
  32. #loop through all lists in web
  33. ForEach($list in $AllLists) {
  34. Write - Host List: $list.Title
  35. #get list title
  36. $listTitle = $list.Title;
  37. If($listTitle - eq '3rdpartytosample_test') {
  38. $itemPosition = $null
  39. $Count = 0;
  40. Do {
  41. $camlQuery = New - Object Microsoft.SharePoint.Client.CamlQuery
  42. $camlQuery.ViewXml = '<View Scope="RecursiveAll"><RowLimit Paged="TRUE">1000</RowLimit></View>'
  43. $camlQuery.ListItemCollectionPosition = $itemPosition;
  44. $AllItems = $list.GetItems($camlQuery)
  45. $Context.Load($AllItems)
  46. $Context.ExecuteQuery()
  47. $itemPosition = $AllItems.ListItemCollectionPosition;
  48. Write - Host itemPosition: $itemPosition
  49. If($AllItems.Count - gt 0) {
  50. ForEach($item in $AllItems) {
  51. #use internal name
  52. $userEmail = $item["Title"]
  53. if ($userEmail - match 'sample.com') {
  54. $userEmail = $userEmail.Replace('ap.sample.com', 'sample.com')
  55. $userEmail = $userEmail.Replace('am.sample.com', 'sample.com')
  56. $userEmail = $userEmail.Replace('eu.sample.com', 'sample.com')
  57. $userEmail = $userEmail.Replace('jp.sample.com', 'sample.com')
  58. $User = $Context.Web.EnsureUser($userEmail);
  59. $Context.Load($user);
  60. $item["Owner"] = $User;
  61. $item.Update();
  62. $Context.ExecuteQuery()
  63. }
  64. }
  65. #end loop
  66. for all items in list
  67. }
  68. #check
  69. if item count is > 0
  70. }
  71. While($itemPosition - ne $null)
  72. }
  73. #check
  74. if it is a 'do not inventory'
  75. list
  76. }
  77. #end loop
  78. for all lists in site
  79. }
  80. Get - SPOSites - RootWeb $sWeb - Context $Context #recursive call
  81. }
  82. #end loop
  83. for all sites in site collection
  84. }
  85. #Set parameter values
  86. $SiteURL = "http://eu.test.sample.com/sites/AdvEU3/Megamigration"
  87. #$LibraryName = "Documents"
  88. $UserName = "###########"
  89. $PassWord = "############"
  90. $securePassword = ConvertTo - SecureString $PassWord - AsPlainText - Force
  91. $spoCred = New - Object System.Net.NetworkCredential($UserName, $securePassword)
  92. #Setup the context
  93. $ctx = New - Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
  94. $ctx.Credentials = $spoCred
  95. $Web = $ctx.Web
  96. $ctx.Load($Web)
  97. $ctx.ExecuteQuery()
  98. Get - SPOSites - RootWeb $Web - Context $ctx
Enter your respective details wherever highlighted or required in the above PowerShell script.
Benefit
Once the above process is finished we can re-use that User field property converted Ttile column to use in many kind of List views.
We can also use the same List column for Filtering, Sorting options on all the custom List views wherever required.
Conclusion
Finally, we got an important way to run PowerShell script to make a given SharePoint List column to be updated as an Owner Column User Field Type via PowerShell scripting...