Syncing Google Calendar with Sheets: Fixing Time Zone Issues

Other Questions Google Sheets
1

Hey folks, I’m stuck with a time zone problem. I used some code to import my Google Calendar into Sheets, but it’s not playing nice with Daylight Saving Time. I’m in Missouri (Central Time), and ever since DST kicked in, all my imported times are an hour off.

Here’s the deal:

  • My computer is set to Central Time Zone (Chicago)
  • Calendar shows correct times
  • Imported times in Sheets are 1 hour behind

I’m no coding whiz, so I’m hoping someone can help tweak the script to fix this DST issue. Any ideas on how to make the times match up?

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++) {
    // Code to add event details to sheet
    // This is where the time problem occurs
  }
}

Thanks a bunch for any help!

2

hey mike, i’ve dealt with this before. try using .setTimeZone(‘America/Chicago’) on your sheet. also, make sure you’re using getStartTime() and getEndTime() for events. those should grab the right times. if that doesn’t work, lemme know and we can troubleshoot more!

3

I’ve run into similar timezone headaches with Google Sheets and Calendar syncing. One trick that worked for me was explicitly converting the event times to the correct timezone within the script. Try adding something like this in your loop:

var eventStart = events[i].getStartTime();
var centralTime = Utilities.formatDate(eventStart, 'America/Chicago', 'MM/dd/yyyy HH:mm:ss');

This forces the date to be interpreted in your local timezone. Then use ‘centralTime’ when adding to your sheet. Also, double-check that your sheet’s timezone setting matches your local one. These tweaks should align everything properly, even with DST changes. Let me know if you need more help!

4

I encountered a similar issue when syncing calendar events to Sheets. One effective solution is to use the Moment.js library, which handles timezones and DST changes smoothly. You can include it in your project as a library.

To implement this, modify your loop to use Moment.js for time conversion:

var moment = Moment.moment;
for (var i = 0; i < events.length; i++) {
  var eventStart = moment(events[i].getStartTime()).tz('America/Chicago');
  var formattedTime = eventStart.format('MM/DD/YYYY HH:mm:ss');
  // Use formattedTime when adding to your sheet
}

This approach ensures consistent timezone handling and should resolve your DST issues. Remember to add Moment.js as a library in your Apps Script project before using it.