How to Handle Time Zone Issues When Syncing Google Calendar Events to Google Sheets

Other Questions Google Sheets
1

Hey everyone! I found some code online to pull calendar events into a spreadsheet but I’m running into timezone problems. I’m not really a coder, just trying to make this work.

The issue is that I’m in Central Time (Missouri area) and my system timezone is set correctly to Chicago. But ever since daylight saving time kicked in, all the event times that get imported are showing up one hour earlier than what I see in my actual calendar.

Is there a way to modify this script to handle the timezone conversion properly? I’d really appreciate any suggestions!

function syncCalendarEvents() {
  var worksheet = SpreadsheetApp.getActiveSheet();
  var calId = worksheet.getRange('C1').getValue().toString();
  var userCalendar = CalendarApp.getCalendarById(calId);
  
  // Define date range
  var fromDate = worksheet.getRange('C2').getValue();
  var toDate = worksheet.getRange('C3').getValue();
  var filterText = '';
  
  // Create header row
  var headerRow = [["Event Name", "Venue", "Begin Time", "Finish Time", "Hours"]];
  var headerRange = worksheet.getRange("A8:E8");
  headerRange.setValues(headerRow);
  headerRange.setFontWeight("bold")
  
  // Retrieve events
  var eventList = (filterText == '') ? userCalendar.getEvents(fromDate, toDate) : userCalendar.getEvents(fromDate, toDate, {search: filterText})
  
  // Process each event
  for (var j=0; j<eventList.length; j++) {
    var currentRow = j+9;
    
    var eventData = [[eventList[j].getTitle(), eventList[j].getLocation(), eventList[j].getStartTime(), eventList[j].getEndTime(), '']];
    
    var dataRange = worksheet.getRange(currentRow,1,1,5);
    dataRange.setValues(eventData);
    
    // Apply time formatting
    var startCell = worksheet.getRange(currentRow, 3);
    startCell.setNumberFormat('mm/dd/yyyy hh:mm');
    var endCell = worksheet.getRange(currentRow, 4);
    endCell.setNumberFormat('mm/dd/yyyy hh:mm');
    
    // Calculate duration
    var durationCell = worksheet.getRange(currentRow, 5);
    durationCell.setFormula('=(HOUR(D' + currentRow + ')+(MINUTE(D' +currentRow+ ')/60))-(HOUR(C' +currentRow+ ')+(MINUTE(C' +currentRow+ ')/60))');
    durationCell.setNumberFormat('0.00');
  }
}
2

I’ve hit this same headache before. The problem is how Google Apps Script handles datetime objects when DST kicks in. It’s not your script timezone setting - it’s how the Calendar API returns date objects.

Here’s what happens: getStartTime() and getEndTime() return JavaScript Date objects that get interpreted based on where the script runs, and this shifts during DST changes. Don’t rely on direct date formatting - wrap your date retrieval with explicit timezone handling instead.

Replace your event data line with this:

var startTime = Utilities.formatDate(eventList[j].getStartTime(), "America/Chicago", "MM/dd/yyyy HH:mm");
var endTime = Utilities.formatDate(eventList[j].getEndTime(), "America/Chicago", "MM/dd/yyyy HH:mm");
var eventData = [[eventList[j].getTitle(), eventList[j].getLocation(), startTime, endTime, '']];

This forces the timezone conversion to happen explicitly instead of relying on Apps Script’s automatic handling, which breaks around DST boundaries.

3

DST drives me nuts every year. Had the same problem a few months ago. First, add Logger.log(Session.getScriptTimeZone()) to see what timezone Google thinks you’re using - sometimes it’s not what you’d expect even when your browser and system are set correctly. Then try new Date().getTimezoneOffset() to check for offset issues during conversion.

4

Yeah, this is super common with Google Apps Script and calendar syncing. Google Calendar stores everything in UTC internally, but Apps Script gets confused with timezone conversions during DST changes. I hit this exact issue last year.

First thing - check your script’s timezone settings. Go to your project, click the gear icon, and make sure it’s set to “America/Chicago” instead of using the system default. Also throw Session.getScriptTimeZone() into your script to see what timezone Google actually thinks you’re using. There’s often a mismatch there.

If that doesn’t fully fix it, you’ll probably need to use Utilities.formatDate() to manually format dates with your timezone before writing to the sheet. Don’t rely on the automatic conversion from getStartTime() and getEndTime() - it’s not reliable during DST transitions.