Hello, here I am back with another script to share with you. Recently a client asked me how to change locale of OneDrive in bulk for his Office 365 users. The thing is that his Office 365 tenant has been created with french as default language, whereas there are many englishspeaking people in the company.

All Excel files created in OneDrive online show formula in French. Really embarrassing for some users.

Of course you can change this default language through OneDrive online site settings > Regional settings

But the big deal is how to manage it for more than twenty thousand users ? This is where you can leverage on the power of PowerShell. Here is a script to help you achieve this goal.

<#
.SYNOPSIS
    This script allows you to change regional settings in Office 365 OneDrive For Business.
.DESCRIPTION
    This script allows you to change regional settings in Office 365 OneDrive For Business.
.PARAMETER UpnPath
    Full path to upns TXT or CSV file (without header), one UserPrincipalName (upn) per line.
.PARAMETER creds
    Specify Admintrator UserPrincipalName
.PARAMETER SiteURL
    Specify SharePoint Admin site url : "https://<yourtenant>-admin.sharepoint.com/", replace <tenant> with your tenant name
.PARAMETER ODUrl
    Specify OneDrive For Business url : "https://<yourtenant>-my.sharepoint.com/Personal/, replace <tenant> with your tenant name
.PARAMETER LocaleID
    Specify the Locale ID. The locale ID for US english (en-us) is 1033
.EXAMPLE
    Set-OD4BLocale -UpnPath "C:\upns.txt" -creds "admin@mytenant.onmicrosoft.com" -SiteURL "https://mytenant-admin.sharepoint.com/" -ODUrl "https://mytenant-my.sharepoint.com/Personal/" -LocaleID 1033
.NOTES
    Version :          1.0
    Auteur :           Jean-Marie AGBO
    Date de création : 04/12/2018
    Version 1.0 :  Développement initial        
#>

Function Set-OD4BLocale{
  [CmdletBinding()]
  Param(                        
        [parameter(Mandatory=$true)]
        [String]$UpnPath,                                                           

        [parameter(Mandatory=$true)]
        [PSCredential]$creds,                                              

        [parameter(Mandatory=$true)]
        [string]$SiteURL,            

        [parameter(Mandatory=$true)]
        [string]$ODUrl, 

        [parameter(Mandatory=$false)]
        [string]$LocaleID = "1033"    
  )

  #Connect msol service
  Connect-SPOService -Url $SiteURL -Credential $creds

  #Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
  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"
     
  #Import upn file
  $Upns = Get-Content -Path $UpnPath

  foreach ($upn in $Upns){      
    #Building user ODrive Full Url
    Write-Host "Building ODrive Full Url for $upn" -ForegroundColor Yellow
    $ODriveFullUrl = $ODUrl +  $Upn.Replace("@","_").replace('.','_') 
    
    #Adding Admin access to user OneDrive
    Write-Host "Adding Admin access to $upn OneDrive" -ForegroundColor Yellow
    Set-SPOUser -Site $ODriveFullUrl -LoginName $creds.UserName -IsSiteCollectionAdmin $true | Out-Null 
     
    #Bind to OD4B Site and change locale
    Write-Host "Changing Locale for $upn" -ForegroundColor Yellow 
    $spocreds = [Microsoft.SharePoint.Client.SharePointOnlineCredentials]::new($Creds.UserName,$creds.Password)
    $Context = New-Object Microsoft.SharePoint.Client.ClientContext($ODriveFullUrl)
    $Context.Credentials = $spocreds
    $Context.ExecuteQuery()
    $Context.Web.RegionalSettings.LocaleId = $Locale                  
    $Context.Web.Update()
    $Context.ExecuteQuery()
     
    #Removing Admin access from User OneDrive
    Write-Host "Removing Admin access from $upn OneDrive" -ForegroundColor Green
    Set-SPOUser -Site $ODriveFullUrl -LoginName $creds.UserName -IsSiteCollectionAdmin $false | Out-Null 
  }
}

How to run the script ?

Set-OD4BLocale -UpnPath "C:\upns.txt" -creds "admin@mytenant.onmicrosoft.com" -SiteURL "https://mytenant-admin.sharepoint.com/" -ODUrl "https://mytenant-my.sharepoint.com/Personal/" -LocaleID 1033

The upns.txt file must contain the UserPrincipalName of users you want to change the locale.

fname1@mytenant.com
fname2@mytenant.com
fname3@mytenant.com
fname4@mytenant.com

You can get help on the script. Run it and use the below PowerShell command line :

Get-Help Set-OD4BLocale -Full

After changing the default language:

Now, you can check your change by creating an excel file in OneDrive Online and note that the formula are in english.

Et voilà 🙂 !