Skip to content

Instantly share code, notes, and snippets.

@erickoledadevrel
Last active July 8, 2020 11:42
Show Gist options
  • Save erickoledadevrel/6cf5d1e764839cba8701 to your computer and use it in GitHub Desktop.
Save erickoledadevrel/6cf5d1e764839cba8701 to your computer and use it in GitHub Desktop.
Some examples showing how to work with timezones in Apps Script.
// Setup: Spreadsheet in Pacific time, Script in Eastern time, Calendar in Mountain time.
// Sample spreadsheet: https://docs.google.com/spreadsheets/d/1neePK1YPKMKnVwI8dld0HSJjI1kSFPpcY8g7kG8xG4U/edit#gid=0
// A2 = 3/1/2016 9:00 AM, B2 = 3/1/2016 10:00 AM
/**
* The dates and times in the spreadsheet refer to exact moments in time, and the clock
* on the wall may show a different time.
*/
function copyMoment() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheets()[0];
var startDate = sheet.getRange('A2').getValue();
var endDate = sheet.getRange('B2').getValue();
var calendar = CalendarApp.getCalendarsByName('Mountain Time')[0];
calendar.createEvent('Exact Copy', startDate, endDate);
// Result: Calendar event at 10:00 AM Mountain Time
}
/**
* The dates and times in the spreadsheet refer to relative moments in time,
* and the clock on the wall should match.
*/
function copyRelative() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheets()[0];
var startDate = sheet.getRange('A2').getValue();
var endDate = sheet.getRange('B2').getValue();
var calendar = CalendarApp.getCalendarsByName('Mountain Time')[0];
// Change the timezone of the start and end date to match the calendar.
startDate = changeTimezone(startDate, ss.getSpreadsheetTimeZone(), calendar.getTimeZone());
endDate = changeTimezone(endDate, ss.getSpreadsheetTimeZone(), calendar.getTimeZone());
calendar.createEvent('Relative Copy', startDate, endDate);
// Result: Calendar event at 9:00 AM Mountain time
}
/**
* Get the same relative date and time in a different timezone.
*/
function changeTimezone(date, fromTimezone, toTimezone) {
var dateString = Utilities.formatDate(date, fromTimezone, 'yyyy/MM/dd HH:mm:ss');
var offset = Utilities.formatDate(date, toTimezone, 'Z');
return new Date(dateString + offset);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment