Syncing Google Calendar with Sheets: Handling Time Zone Changes

Other Questions Google Sheets
1

Hey everyone, I’m not a coding expert but I’ve been trying to get my Google Calendar events into a spreadsheet. I found some code online that does this, but I’m having a problem with the time zones.

I’m in Missouri (Central Time) and my computer is set to Chicago time. Now that we’ve switched to Daylight Saving Time, the times in my spreadsheet are an hour off from what’s in my calendar.

Does anyone know how to tweak the code to fix this? I’d really appreciate some help! Here’s a simplified version of what I’m working with:

function syncCalendarToSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var calId = sheet.getRange('A1').getValue();
  var cal = CalendarApp.getCalendarById(calId);
  
  var start = sheet.getRange('A2').getValue();
  var end = sheet.getRange('A3').getValue();
  
  var events = cal.getEvents(start, end);
  
  for (var i = 0; i < events.length; i++) {
    var row = i + 2;
    sheet.getRange(row, 1).setValue(events[i].getTitle());
    sheet.getRange(row, 2).setValue(events[i].getStartTime());
    sheet.getRange(row, 3).setValue(events[i].getEndTime());
  }
}

Thanks in advance for any suggestions!

2

I’ve faced similar issues syncing calendar events after DST changes. A solution for you would be to explicitly set the time zone using Utilities.formatDate(). For instance, in your loop, you can adjust the code as follows:

var timezone = CalendarApp.getDefaultCalendar().getTimeZone();
sheet.getRange(row, 2).setValue(Utilities.formatDate(events[i].getStartTime(), timezone, ‘MM/dd/yyyy HH:mm:ss’));
sheet.getRange(row, 3).setValue(Utilities.formatDate(events[i].getEndTime(), timezone, ‘MM/dd/yyyy HH:mm:ss’));

This ensures that your time data is correctly formatted according to your calendar’s time zone settings, handling DST smoothly. You can tweak the date format if needed.

3

I encountered a similar issue when working on syncing calendar events across different time zones. One effective solution is to use the ‘setTimeZone’ method when retrieving event times. Here’s how you can modify your code:

var eventStartTime = events[i].getStartTime().setTimeZone(cal.getTimeZone());
var eventEndTime = events[i].getEndTime().setTimeZone(cal.getTimeZone());

sheet.getRange(row, 2).setValue(eventStartTime);
sheet.getRange(row, 3).setValue(eventEndTime);

This approach ensures that the event times are consistently represented in the calendar’s time zone, regardless of DST changes or your local computer settings. It provides a straightforward fix that should resolve the time discrepancy issues without extensive modifications.

4

hey amelial, i’ve dealt with this before. try using the Utilities.formatDate() function with the timezone parameter. it should look somethin like this:

var tz = CalendarApp.getDefaultCalendar().getTimeZone();
sheet.getRange(row, 2).setValue(Utilities.formatDate(events[i].getStartTime(), tz, ‘MM/dd/yyyy HH:mm:ss’));

this should fix ur DST issue. lmk if u need more help!