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 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 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 high level.
SharePoint Online user profile bulk update

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 "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
#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"

$TenantURL =""
$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)
    #Get User Profile
    $PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Ctx)
    $UserProfile = $PeopleManager.GetPropertiesFor($User.LoginName)

    #Get all User Profile Properties - $UserProfile.UserProfileProperties to get properties along with values
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 which 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 = ""}
                $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)
        #Convert data to required Json format
        $JsonData = $UserProfileDataColl | ConvertTo-Json
        $JSON = @"
        { "value":
        #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)

$SiteUrl = ""
$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.
sharepoint online bulk update user profiles

Check the status of an import job
To check the import job status, use:

How do we automate this PowerShell script?
Well, Create a Windows scheduled task, Create 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 tenant level.

My other posts on SharePoint Online user profile update:

1 comment:

  1. How are you able to get the automation working? Using the ClientId and ClientSecret returns an Access denied error with New-PnPUPBulkImportJob.


Please Login and comment to get your questions answered!

Powered by Blogger.