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; 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 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 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="[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
}

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:

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:

• SharePoint Online: Update User Profile Properties using PowerShell
• SharePoint Online: Sync User Profile Property from Azure AD using PowerShell