Editing Locale and Timezone Settings for Office 365 Groups using PowerShell

Problem definition:

  • Files created under Office 365 Groups or OneDrive for Business exhibit PST timezone formatting. You can replicate this by creating an Excel document and typing 1/12, which is then auto-formatted by Excel to say 12th Jan. In the UK, we mean 1st Dec.
  • This timezone issue occurs even if Groups are created with en-GB as the locale.

The following code snippet allows you to programmatically edit the Locale and Timezone for an Office 365 Group using PowerShell. It requires you to be an owner on the group. It may also be adapted to edit a user’s personal site (see bottom of article).

Prerequisites:

Solution:

# Define required variables:
$Office365GroupURL = 'https://tenancyname.sharepoint.com/sites/YourO365Group'
$TimeZoneID = 2
$LocaleID = 2057

# Specify Office 365 UPN with group (site) ownership permissions:
$User = "user@yourdomain.com"
$Password = Read-Host -Prompt "Please enter your password" -AsSecureString

# Add references to SharePoint client 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"

# Create SharePointOnlineCredentials class object:
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)

# Perform the work:
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($Office365GroupURL)
$Context.Credentials = $Creds
$Context.ExecuteQuery()
$Context.Web.RegionalSettings.LocaleId = $LocaleID
$Context.Web.RegionalSettings.TimeZone = $Context.Web.RegionalSettings.TimeZones.GetById($TimeZoneID)
$Context.Web.Update()
$Context.ExecuteQuery()

Notes:

  • This works against personal sites, using the personal site URL. An example might be: https://tenancyname-my.sharepoint.com/personal/username_domain_com
  • Still investigating how to perform this operation as an administrator against sites (possibly using Set-SPOSite to add an owner, then removing afterwards).

Office 365, PowerShell, SKUs and Service Plans

Working with Office 365 licensing and PowerShell can get a little confusing with SKU names like “STANDARDWOFFPACK_IW_FACULTY” and plan names like “MCOSTANDARD” (which is Skype, of all things!). In order to help reporting and management, I wrote a couple of advanced functions to translate SkuPartNumber and ServicePlanName into friendly names as shown in the Office 365 / O365 Portal. They accept pipeline input. Note the “LU” prefix in the function name; this is our chosen prefix to avoid naming conflicts/overlap with any official cmdlets. I work in Education, so these names are specific to educational plans, but you may adapt them to suit your needs. I’m also happy to add more to the functions if this serves as a useful reference.

function Convert-LUMsolServicePlanName
{
    <#
            .SYNOPSIS 
            This function will convert between the ServicePlanName property relating to an individual service within an Office 365 Licence and vice versa.
            .DESCRIPTION
            This function will convert between the ServicePlanName property relating to individual services within an Office 365 Licence and vice versa.
            It allows you to obtain a user friendly name (as viewed in the O365 Portal) when passed a "backend" property value, and also pass a friendly name value
            back to get the value for using in scripting licensing.
            .PARAMETER ServicePlanName
            .EXAMPLE 
            Get-MsolAccountSku | Select @{Name="ServicePlanName";Expression={$_.ServiceStatus.ServicePlan.ServiceName}} | % { $_.ServicePlanName } | Convert-LUMsolServicePlanName
            .EXAMPLE 
            Convert-LUMsolServicePlanName -ServicePlanName "EXCHANGE_S_STANDARD"
            .EXAMPLE 
            Convert-LUMsolServicePlanName -ServicePlanFriendlyName "Exchange Online (Plan 1)"
            .NOTES
            Author: Robin Malik
    #>

    
    [CmdletBinding()]
    Param
    (
        [Parameter(
                Mandatory = $true,
                Position = 0,
                HelpMessage='ServicePlanName from PowerShell query, e.g. "ONEDRIVESTANDARD"',
                ValueFromPipeline = $true,
                ValueFromPipelineByPropertyName = $true,
        ParameterSetName = 'ByServicePlanName')]
        $ServicePlanName,

        [Parameter(
                Mandatory = $true,
                Position = 0,
                HelpMessage='Friendly name of a plan, e.g. "OneDrive for Business (Plan 1)"',
        ParameterSetName = 'ByFriendlyName')]
        $ServicePlanFriendlyName
    )

    Begin
    {
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): Begin block."
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): Parameter set triggered: $($PSCmdlet.ParameterSetName)"
    }
    Process
    {
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): Process block."
        $ServicePlanFriendlyNameHashtable = @{
            'BI_AZURE_P0'              = 'Power BI' # This AccountSku doesn't show a 'friendly' service plan name in the portal
            'BI_AZURE_P2'              = 'Power BI Pro'
            'CRMSTANDARD'              = 'Microsoft Dynamics CRM Online Professional'
            'EXCHANGE_S_STANDARD'      = 'Exchange Online (Plan 1)'
            'INTUNE_O365'              = 'Mobile Device Management for Office 365'
            'MCOSTANDARD'              = 'Skype for Business Online (Plan 2)'
            'MDM_SALES_COLLABORATION'  = 'Microsoft Dynamics Marketing Sales Collaboration'
            'NBPROFESSIONALFORCRM'     = 'Microsoft Social Engagement Professional'            
            'OFFICESUBSCRIPTION'       = 'Office 365 ProPlus'
            'OFFICE_FORMS_PLAN_2'      = 'Forms (Plan 2)'
            'ONEDRIVESTANDARD'         = 'OneDrive for Business (Plan 1)'
            'PROJECTWORKMANAGEMENT'    = 'Planner' # This AccountSku doesn't show a 'friendly' service plan name in the portal
            'SHAREPOINT_PROJECT_EDU'   = 'Project Online for Education'
            'SHAREPOINTENTERPRISE_EDU' = 'SharePoint Plan 2 for EDU'
            'SHAREPOINTSTANDARD_EDU'   = 'SharePoint Plan 1 for EDU'
            'SHAREPOINTWAC_EDU'        = 'Office Online for Education'
            'SWAY'                     = 'Sway'
            'YAMMER_EDU'               = 'Yammer for Academic'
        }

        if($ServicePlanName)
        {
            return $ServicePlanFriendlyNameHashtable["$ServicePlanName"]
        }

        if($ServicePlanFriendlyName)
        {
            # Look up by value:
            return ($ServicePlanFriendlyNameHashtable.GetEnumerator() | Where-Object -FilterScript {
                    $_.Value -eq "$ServicePlanFriendlyName" 
            }).Name  
        }
    }
    End
    {
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): End block."
    }
}

function Convert-LUMsolAccountSkuName
{
    <#
            .SYNOPSIS 
            This function will convert between the SkuPartNumber property value in a Get-MsolAccountSku (AccountSkuDetails) object to a friendly SKU/licence name, and vice versa.
            .DESCRIPTION
            This function will convert between the SkuPartNumber property value in a Get-MsolAccountSku (AccountSkuDetails) object to a friendly SKU/licence name, and vice versa.
            It allows you to obtain a user friendly name for the SKU (as viewed in the O365 Portal) when passed a "backend" property value, and also pass a friendly name value
            back to get the value for using in scripting licensing.
            .PARAMETER SkuPartNumber
            .EXAMPLE 
            Get-MsolAccountSku | Select -ExpandProperty SkuPartNumber | Convert-LUMsolAccountSkuName
            .EXAMPLE 
            Convert-LUMsolAccountSkuName -ServicePlanName "STANDARDWOFFPACK_FACULTY"
            .EXAMPLE 
            Convert-LUMsolAccountSkuName  -ServicePlanFriendlyName "Office 365 Education for Faculty"
            .NOTES
            Author: Robin Malik
    #>

    
    [CmdletBinding()]
    Param
    (
        [Parameter(
                Mandatory = $true,
                Position = 0,
                HelpMessage = 'ServicePlanName from PowerShell query, e.g. "OFFICESUBSCRIPTION_STUDENT"',
                ValueFromPipeline = $true,
                ValueFromPipelineByPropertyName = $true,
        ParameterSetName = 'BySkuPartNumber')]
        $SkuPartNumber,

        [Parameter(
                Mandatory = $true,
                Position = 0,
                HelpMessage = 'Friendly name of a plan, e.g. "Office 365 ProPlus for Students"',
        ParameterSetName = 'BySkuFriendlyName')]
        $SkuPartNumberFriendlyName
    )

    Begin
    {
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): Begin block."
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): Parameter set triggered: $($PSCmdlet.ParameterSetName)"
    }
    Process
    {
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): Process block."
        $SkuPartNumberFriendlyNameHashtable = @{
            'CRMSTANDARD'                  = 'Microsoft Dynamics CRM Online Professional'            
            'OFFICESUBSCRIPTION_FACULTY'   = 'Office 365 ProPlus for Faculty'
            'OFFICESUBSCRIPTION_STUDENT'   = 'Office 365 ProPlus for Students'
            'PLANNERSTANDALONE'            = 'Office 365 Planner'
            'POWER_BI_STANDARD_FACULTY'    = 'Power BI (free) for faculty'
            'POWER_BI_PRO_FACULTY'         = 'Power BI Pro for faculty'
            'PROJECTONLINE_PLAN_1_FACULTY' = 'Project Online for Faculty'
            'STANDARDWOFFPACK_FACULTY'     = 'Office 365 Education for Faculty'
            'STANDARDWOFFPACK_IW_FACULTY'  = 'Office 365 Education Plus for Faculty'
            'STANDARDWOFFPACK_STUDENT'     = 'Office 365 Education for Students'
            'STANDARDWOFFPACK_IW_STUDENT'  = 'Office 365 Education Plus for Students'
        }

        if($SkuPartNumber)
        {
            return $SkuPartNumberFriendlyNameHashtable["$SkuPartNumber"]
        }

        if($SkuPartNumberFriendlyName)
        {
            # Look up by value:
            return ($SkuPartNumberFriendlyNameHashtable.GetEnumerator() | Where-Object -FilterScript {
                    $_.Value -eq "$SkuPartNumberFriendlyName"
            }).Name  
        }
    }
    End
    {
        Write-Verbose -Message "$(Get-Date -Format `"dd/MM/yyyy HH:mm:ss`"): End block."
    }
}