Nathan Nathan - 11 months ago 56
Javascript Question

How to create a google calendar event with local time?

I am trying to create a Google Calendar events from a Google Spreadsheet:

Example Spreadsheet:

sample event spreadsheet

(the details and dates are irregular so simple reoccurring event doesn't quiet cut it)


function walk_sheet(){

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("CalendarGenerator");

for (var i = 2; i <= 39; i++) {
var date = sheet.getRange(i, 2).getValue();
var start = parseTime(sheet.getRange(i, 3).getDisplayValues().toString() ) ;
var end = parseTime(sheet.getRange(i, 4).getDisplayValues().toString() ) ;
var summary= sheet.getRange(i, 6).getValue();
var disc = sheet.getRange(i, 7).getValue();

add_bsf_event(date, start, end, summary, disc);

function add_bsf_event(dateIn, start, end, summary, disc){
start_date= new Date(dateIn.getYear(), dateIn.getMonth(), dateIn.getDate(), start.getHours(), start.getMinutes(),0,0);
end_date = new Date(dateIn.getYear(), dateIn.getMonth(), dateIn.getDate(), end.getHours(), end.getMinutes(),0,0);

CalendarApp.getCalendarById(calendarId).createEvent(summary, start_date, end_date);

And it works with the exception of Daylight Savings Time. Starting after 3/13/2017 events in Google Calendar are 1 hour later then requested (7:55pm instead of 6:55pm).

I've tried:

  1. Looking for a Google Calendar method that would accept local time (aka send 6:55PM and Google Calendar API convert it to UTC)

  2. Looking for a native JS method that convert the local time to UTC.

Haven't been able to find either solution. Is there one that I am not finding or is different/better approach to this problem?

Answer Source

My solution was to insert the events using the Google Calendar Ruby API. It was bit of pain to setup but once I got the connection established and boiler plate code down, Ruby handle the daylight saving time changes flawlessly.

Google quick start guide does a decent job:

However if your running on Windows chances are SSL certificate will fail to verify; you can disable it by:

require 'openssl'

Ruby method I used for inserting events:

def insert_event(summary, description, start_time, end_time, location, cal_id='primary')
    event ={ 
         #reminders:{use_default: true} 
  #puts event.to_json
  result = $service.insert_event(cal_id, event)
  #puts result
  puts "Event created: #{result.html_link}"