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:

Looking for a Google Calendar method that would accept local time (aka send 6:55PM and Google Calendar API convert it to UTC)
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?

score 0 · Answer 1 · edited May 23 '17 at 11:48

0

You can try to check the Google Calendar API documentation, it mentions here that you can use a separate timezone for start and end of an event.

Now, for the method that convert timezone, I think this tutorial can help you. It explains here step by step the things that you need to do in order to achieve this conversion.

For more information, check this related SO question even though this code is in JAVA.

edited May 23 '17 at 11:48

Community

1
1

answered Aug 16 '16 at 08:40

KENdi

7,576
2
16
31

My problem is with Daylight Saving Time not Timezones (all events are in the same timezone)... I tried specifying the start/end.timeZone in the Google Calendar API and it had no effect. – Nathan Aug 16 '16 at 16:18

score 0 · Accepted Answer · answered Oct 06 '16 at 02:41

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

How to create a google calendar event with local time?

2 Answers2