AppsScriptPulse

Formatting date/time values using the user’s timezone in Google Apps Script

Working with dates, times and time zones can often be a bit of a headache. If you’d like to learn more about some of the challenges of dealing with ‘big balls of wibbly-wobbly, timey-wimey… stuff’ I recommend watching Comptuerphile’s Problem with Time & Timezones.

For displaying dates/time values for users in different time zones you can use Utilities.formatDate(date, timeZone, format) , the challenge however is working out the user’s timeZone . For Google Workspace Add-ons developers can configure the manifest option to include user locale information in event objects included as part of action callback functions [See Accessing user locale and timezone].

For web apps and editor add-ons using HTML Service it is possible to get the user’s browser time zone setting with the JavaScript code Intl.DateTimeFormat().resolvedOptions().timeZone (at time of writing this works in 93.85% of browsers). This can be passed back to your server script and used to format date/times for the user’s time zone. This forked gist provides an example of how this can be used in Google Sheets (props to Eric Koleda for the original solution, which uses .getTimezoneOffset() to get the users time zone offset).

The merits of Intl.DateTimeFormat() and .getTimezoneOffset() are discussed in Getting the client’s time zone (and offset) in JavaScript.

If using HTML Service is not an option an alternative workaround is getting the user’s Google Calendar time zone using the Calendar Service and .getTimeZone() . Here is an example script shared on Stack Overflow:

function getUserTimeZone() {
var userTimeZone = CalendarApp.getDefaultCalendar().getTimeZone();
Logger.log(userTimeZone)
}

The downside of this approach is it potentially adds an additional authorisation scope the user will need to approve before the script can run. As noted by Google best practice is always to limit the scopes in your Apps Script projects to the ones you need.

If you’ve got other tips for handling user time zones feel free to pop them in the comments.

Leave a Reply

Your email address will not be published.

Subscribe to Apps Script Pulse...