Share and Enjoy !

Google to the Rescue – SharePoint Column with Multiple Values

On a recent project, I had the need to update a SharePoint Column with Multiple Values (SharePoint Person or Group column) using Powershell and Set_PnPListItem. The documentation I read indicated I could pass in a comma-delimited set of email strings, but I found this did not work.  I turned to my friend Google and found a post that indicated the email addresses should be passed in a string array.  I also found during my testing that if one of the email addresses in a group of addresses was not valid, none of the email addresses were getting stored.  So, I needed to validate the email addresses before storing them. 

A modified version of my script is included below:

[string]$MigrationSiteUrl = "https://mywebsiteurl",
[string]$Token = "MyCredentialKey"
Write-Host "Update Items in List Starting: $((get-date).ToString(‘f’)) "
$items = @()
Connect-PnPOnline -url $MigrationSiteUrl -Credentials $Token

# Confirm Sharepoint list exists
$list = Get-PnPList -Identity "ListNameToUpdate"

if($null -eq $list){
throw "ListNameToUpdate not found"

# retrieve all users in the site collection at once and store in an array
$allUsers = Get-PnPUser

# load all items that contain email addresses
$items += Get-Content -Path "c:\items.csv" | ConvertFrom-Csv

# to List
foreach($item in $items){
# array to be passed to Set-PnPListItem
$values = @{ }
# temporary array to store email addresses currently stored as semi-colon delimited string
# values in the "SiteOwners" column of the items.csv file
$emailArray = @()
$property = $item.PSObject.Properties["SiteOwners"]
$key = $property.Name
$emailArray = $property.Value.Split(";")
# temporary array to store valid email addresses
$validEmailsArray = @()
foreach($email in $emailArray) {
try {
# confirm if the email address was retrieved above with the Get-PnPUser command
$user = $allUsers | ? email -eq $email
if($null -ne $user) {
# if the email was valid, add it to the validEmailsArray
$validEmailsArray += $email
catch {
Write-Host $_

if($validEmailsArray.Length -gt 0) {
$values.Add($key, $validEmailsArray)
$query = &quot;<View><Query><Where><Eq><FieldRef Name=’Title’/><Value Type=’Text’>&quot; + $item.key + &quot;</Value></Eq></Where></Query></View>&quot;
$listItem = Get-PnPListItem -List &quot;MySharePointList&quot; -Query $query
Set-PnPListItem -List &quot;MySharePointList&quot; -Identity $listItem.Id -Values $values
Write-Host &quot;Update Items in List Ending: $((get-date).ToString(‘f’)) &quot;
} [/code]


Key Things to Observe from the Code

  • Get-PnPUser – retrieves all users 
  • Get-PnPListItem – retrieves a row from the SharePoint list using the caml query syntax.  In the sample code above, I am retrieving a list item and then using its id so I can update the same item 
  • Set-PnPListItem – updates the SharePoint list item with a specific id that I pass in.  This command will update one or more columns based upon the number of rows in the $values hashtable.  In this code example, I am only updating one column called “SiteOwners”.  The $values hashtable has one row and some example data would look like this depending upon whether I am passing in 3 email addresses or just 1. 
  • @{key=”SiteOwners“;value=“”,””,””} 
  • @{key=”SiteOwners“;value=“”} 


In conclusion, I learned that there are often many unknown caveats that you encounter using Powershell and Set_PnPListItem. I found a solution that worked for my purposes. If you have anything additional, please leave it in the comments below and I’ll get back to you. Hope this helps! 

Check out some other blog posts about solving problems using PowerShell

Share and Enjoy !

Related Content: