shutterstock_654072133.jpg

SharePoint Column with Multiple Values Using PowerShell and Set-PnPListItem

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:

PARAM( 
    [Parameter(Mandatory=$false)] 
    [string]$MigrationSiteUrl = "https://mywebsiteurl", 
    [Parameter(Mandatory=$false)] 
    [string]$Token = "MyCredentialKey" 
) 
BEGIN{ 
    Write-Host "Update Items in List Starting: $((get-date).ToString('f')) "  
} 
PROCESS{ 
    $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 
        }   
    } 
} 
END{ 
    Write-Host &quot;Update Items in List Ending: $((get-date).ToString('f')) &quot;  
} 

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. 

 

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

Tim CoalsonSharePoint Column with Multiple Values Using PowerShell and Set-PnPListItem

Join the conversation

This site uses Akismet to reduce spam. Learn how your comment data is processed.