Last active
July 8, 2020 11:42
-
-
Save erickoledadevrel/6cf5d1e764839cba8701 to your computer and use it in GitHub Desktop.
Some examples showing how to work with timezones in Apps Script.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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