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 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à 🙂 !
