SharePoint Online: Bulk Update User Profiles using PowerShell
Requirement: By default, SharePoint Online synchronizes only certain properties from Azure AD. As there are no custom property mappings available in the user profile service, we got to write our own script or utility to sync additional attributes to SharePoint Online user profiles.
How to Bulk Update User Profiles in SharePoint Online?
Although we can update user profile properties using the client-side object model, The bulk update user profile properties API is more efficient, especially when dealing with thousands of profiles Bulk update custom user profile properties for SharePoint Online; here is how the process works at a high level.
Please note, this method doesn’t support editable properties in SharePoint Online to avoid overwriting. So, if you want to sync your custom properties or properties that are not synced with SharePoint Online by default, You must uncheck the “Allow users to edit values for this property” value in the “Manage user properties” of the “User profile service” in SharePoint Online admin center. Otherwise, you’ll see “New-PnPUPABulkImportJob : Property Names [Country,CellPhone] are editable by user.” error message!
Map user profile properties between Azure AD and SharePoint Online
To getting started, we need the attributes in both Azure AD and SharePoint Online to map. To get all available attributes from Azure AD, use this PowerShell:
#Set Parameter - Any valid user account
$UserUPN = "[email protected]"
#Connect to AzureAD
Connect-AzureAD -Credential $Cred | Out-Null
#Get All attributes of the User
Get-AzureADUser -ObjectId $UserUPN | Get-Member -MemberType Properties
#To Get Extended properties, use: Get-AzureADUser -ObjectId $UserUPN | Select -ExpandProperty ExtensionProperty
To retrieve all user profile properties from SharePoint Online side, use:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
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.UserProfiles.dll"
#Parameters
$TenantURL ="https://crescent.sharepoint.com"
$UserAccount="[email protected]"
Try {
#Setup Credentials to connect
$Cred= Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($TenantURL)
$Ctx.Credentials = $Credentials
#Get the User
$User = $Ctx.web.EnsureUser($UserAccount)
$Ctx.Load($User)
$Ctx.ExecuteQuery()
#Get User Profile
$PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Ctx)
$UserProfile = $PeopleManager.GetPropertiesFor($User.LoginName)
$Ctx.Load($UserProfile)
$Ctx.ExecuteQuery()
#Get all User Profile Properties - $UserProfile.UserProfileProperties to get properties along with values
$UserProfile.UserProfileProperties.Keys
}
Catch {
write-host -f Red "Error Getting User Profile Properties!" $_.Exception.Message
}
Once you have the fields, map it according to your requirement.
PowerShell to Bulk Update User Profiles in SharePoint Online:
As SharePoint Online doesn’t allow you to map custom fields from Azure AD into the user profiles, you have to handle it manually using PowerShell (or any other custom tool). Create a document library in a SharePoint site to store import logs, set parameters in this script according to your environment, map properties, and then run the script. In my scenario, I’ve mapped Country and Mobile attributes that are not mapped with SharePoint Online by default.
#Function to Extract User profile Property
Function Extract-ADUserProperties
{
Param ([Parameter(Mandatory=$true)][string[]]$ADProperties, [Parameter(Mandatory=$true)][PSCredential]$Cred)
Try {
#Connect to AzureAD
Connect-AzureAD -Credential $Cred | Out-Null
#Get All Users from AzureAD
$AllUsers = Get-AzureADUser -All:$True
Write-host "Total Number of User Profiles Found:"$AllUsers.Count
$UserProfileDataColl = @()
#Iterate through All User profiles
$Counter = 1
ForEach($User in $AllUsers)
{
$UserProfileData = New-Object PSObject
$UserProfileData | Add-Member NoteProperty IDName($User.UserPrincipalName)
#Get Value of each property of the user
ForEach($ADProperty in $ADProperties)
{
#Get User Profile Property value
$ADPropertyValue = $User | Select -ExpandProperty $ADProperty
If(!$ADPropertyValue) {$ADPropertyValue = [string]::Empty}
$UserProfileData | Add-Member NoteProperty $ADProperty($ADPropertyValue)
}
$UserProfileDataColl += $UserProfileData
Write-Progress -Activity "Getting User Profile Data..." -Status "Getting User Profile $Counter of $($AllUsers.Count)" -PercentComplete (($Counter / $AllUsers.Count) * 100)
$Counter++
}
#Convert data to required Json format
$JsonData = $UserProfileDataColl | ConvertTo-Json
$JSON = @"
{ "value":
$JsonData
}
"@
#Export JSON to a File
$JSON | Out-File -FilePath $Env:TEMP/UserProfileData.JSON -Force
Write-host "Extracted user profile Properties Successfully!" -f Green
}
Catch {
write-host -f Red "Error Getting User Profile Properties!" $_.Exception.Message
}
}
#Call the function to Extract User profile data to JSON
Extract-ADUserProperties -ADProperties @("Country", "Mobile") -Cred (Get-Credential)
#Parameters
$SiteUrl = "https://crescent.sharepoint.com"
$SiteRelativeFolderPath = "/User Profile Import"
#Connect to site
Connect-PnPOnline $SiteUrl –Credentials (Get-Credential)
#Call the Bulk Import Job - Mapping in format AzureAD User Attribute = SharePoint User Profile Property
New-PnPUPABulkImportJob -Folder $SiteRelativeFolderPath -Path $Env:TEMP/UserProfileData.JSON -IdProperty "IdName" -UserProfilePropertyMapping @{"Country"="Country";"Mobile"="CellPhone"}
This script extracts mapped properties from Azure AD to a JSON file format and then kicks off the profile properties import job to update user profile attributes from the JSON.
Check the status of an import job
To check the import job status, use:
Get-PnPUPABulkImportStatus
How do we automate this PowerShell script?
Well, create a Windows scheduled task, create an App ID for authentication, and use it in the script. Here is an example: How to Connect to SharePoint Online using PnP PowerShell – using AppID and AppSecret?, make sure you grant full control to the App ID at the tenant level.
My other posts on SharePoint Online user profile update:
How are you able to get the automation working? Using the ClientId and ClientSecret returns an Access denied error with New-PnPUPBulkImportJob.