Nathan Nathan - 1 month ago 21
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)

Code:

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

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: https://developers.google.com/google-apps/calendar/quickstart/ruby

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

require 'openssl'
OpenSSL::SSL::VERIFY_PEER = OpenSSL::SSL::VERIFY_NONE

Ruby method I used for inserting events:

def insert_event(summary, description, start_time, end_time, location, cal_id='primary')
    event = Google::Apis::CalendarV3::Event.new({ 
                  summary:summary,
           #color_id:5,
                location:location,
           description:description,
             start:{date_time:start_time.to_datetime.rfc3339},
               end:{date_time:end_time.to_datetime.rfc3339},
         #reminders:{use_default: true} 
    })
  #puts event.to_json
  result = $service.insert_event(cal_id, event)
  #puts result
  puts "Event created: #{result.html_link}"
end
Comments