How to create a google calendar event with local time?

Question

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

Example 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 1

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