SharePoint Online: Bulk Update User Profiles using PowerShell
Requirement: By default, SharePoint Online synchronizes only specific properties from Azure AD. 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 to bulk update custom user profile properties for SharePoint Online; In this guide, we will walk through the steps of using PowerShell to update user profiles in 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. Suppose you want to sync your custom properties or properties that are not synced with SharePoint Online by default. In that case, You must uncheck the “Allow users to edit values for this property” value in the “Manage user properties” of the “User profile service” in the SharePoint Online admin center. Otherwise, you’ll see “New-PnPUPABulkImportJob : Property Names [Country, CellPhone] are editable by the user.” error message!
Map user profile properties between Azure AD and SharePoint Online
To get 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 = "Salaudeen@crescent.com"
#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 available in SharePoint Online, 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="salaudeen@crescent.com"
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
}
Here is the list of all user profile properties available in SharePoint Online:
- UserProfile_GUID
- SID
- ADGuid
- AccountName
- FirstName
- SPS-PhoneticFirstName
- LastName
- SPS-PhoneticLastName
- PreferredName
- SPS-PhoneticDisplayName
- WorkPhone
- Department
- Title
- SPS-Department
- Manager
- AboutMe
- PersonalSpace
- PictureURL
- UserName
- QuickLinks
- WebSite
- PublicSiteRedirect
- SPS-JobTitle
- SPS-DataSource
- SPS-MemberOf
- SPS-Dotted-line
- SPS-Peers
- SPS-Responsibility
- SPS-SipAddress
- SPS-MySiteUpgrade
- SPS-DontSuggestList
- SPS-ProxyAddresses
- SPS-HireDate
- SPS-DisplayOrder
- SPS-ClaimID
- SPS-ClaimProviderID
- SPS-LastColleagueAdded
- SPS-OWAUrl
- SPS-ResourceSID
- SPS-ResourceAccountName
- SPS-MasterAccountName
- SPS-UserPrincipalName
- SPS-O15FirstRunExperience
- SPS-PersonalSiteInstantiationState
- SPS-DistinguishedName
- SPS-SourceObjectDN
- SPS-LastKeywordAdded
- SPS-ClaimProviderType
- SPS-SavedAccountName
- SPS-SavedSID
- SPS-ObjectExists
- SPS-PersonalSiteCapabilities
- SPS-PersonalSiteFirstCreationTime
- SPS-PersonalSiteLastCreationTime
- SPS-PersonalSiteNumberOfRetries
- SPS-PersonalSiteFirstCreationError
- SPS-FeedIdentifier
- WorkEmail
- CellPhone
- Fax
- HomePhone
- Office
- SPS-Location
- Assistant
- SPS-PastProjects
- SPS-Skills
- SPS-School
- SPS-Birthday
- SPS-StatusNotes
- SPS-Interests
- SPS-HashTags
- SPS-EmailOptin
- SPS-PrivacyPeople
- SPS-PrivacyActivity
- SPS-PictureTimestamp
- SPS-PicturePlaceholderState
- SPS-PictureExchangeSyncState
- SPS-MUILanguages
- SPS-ContentLanguages
- SPS-TimeZone
- SPS-RegionalSettings-FollowWeb
- SPS-Locale
- SPS-CalendarType
- SPS-AltCalendarType
- SPS-AdjustHijriDays
- SPS-ShowWeeks
- SPS-WorkDays
- SPS-WorkDayStartHour
- SPS-WorkDayEndHour
- SPS-Time24
- SPS-FirstDayOfWeek
- SPS-FirstWeekOfYear
- SPS-RegionalSettings-Initialized
- OfficeGraphEnabled
- SPS-UserType
- SPS-HideFromAddressLists
- SPS-RecipientTypeDetails
- DelveFlags
- VideoUserPopup
- PulseMRUPeople
- msOnline-ObjectId
- SPS-PointPublishingUrl
- SPS-TenantInstanceId
- SPS-SharePointHomeExperienceState
- SPS-RefreshToken
- SPS-MultiGeoFlags
- PreferredDataLocation
- Country
Once you have the fields, map them 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 must 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 the following:
Get-PnPUPABulkImportStatus
How do we automate this PowerShell script?
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:
- SharePoint Online: Update User Profile Properties using PowerShell
- SharePoint Online: Sync User Profile Property from Azure AD using PowerShell
In conclusion, using PowerShell to bulk update user profiles in SharePoint Online can save a significant amount of time and effort compared to manually updating each profile individually. By utilizing the New-PnPUPABulkImportJob cmdlet from PnP PowerShell, administrators can easily update multiple user profiles with just a few lines of code.
We are receiving an issue where our JSON looks correct, but it is returning this error: UnexpectedToken email@email.com Unexpected EndArry token found. Our JSON follows what it should. It does pull over 1,000 records.
Were you ever able to resolve this? I’m experiencing the same.
Is it possible to get profiles Directory Extensions synced to SharePoint using this same method? For example, I’m trying to sync the IP Phone and Notes attributes in AD (ipPhone and info) to custom SharePoint user properties. It looks like all directory extensions are bundled within the ExtensionProperty attribute in Azure AD, and when I try to extract that AD user property it messes up the JSON since there are multiple entries within that attribute. Any ideas would be welcome. Perhaps something with Get-AzureADUserExtension?
I am updating About Me of user profile using this script. If About Me contains & it is updating as \u0026 in the json file. About Me on Delve is not showing the entire content. It is trimming the content after &. If I go to backend user profile, edit profile and update, it shows the complete About Me text.
Ex: He is an expert in SharePoint & Powershell. This gets update in json as “He is an expert in SharePoint \u0026 Powershell.”
on Delve it shows He is an expert in SharePoint
How can we resolve this?
I’m seeing the following error message: “New-PnPUPABulkImportJob : Folder /User Profile Import does not exist.” Is the path correct?
Create a new document library “User Profile Import” in your root site, so that the log files gets saved there.
How are you able to get the automation working? Using the ClientId and ClientSecret returns an Access denied error with New-PnPUPBulkImportJob.
You got to grant access to your Client ID on the tenant, If you are using ClientId and ClientSecret method!