different time in google calendar when adding events from spreadsheet

Question

time changes when added to google calender by google appscript and spreedsheet: code for app script (only time portion):

function setUpCalendar_(values, range) {
var cal = CalendarApp.createCalendar('Conference Calendar');

Browser.msgBox('Calender time zone: ' + cal.getTimeZone()); 
//to check timezone for debugging.

for (var i = 1; i < values.length; i++) {
var session = values[i];
var title = session[0];
var start = joinDateAndTime_(session[1], session[2]);
var end = joinDateAndTime_(session[1], session[3]);
var options = {location: session[4], sendInvites: true};
var event = cal.createEvent(title, start, end, options)
                .setGuestsCanSeeGuests(false);
//session[5] = event.getId();
   ///====:::: test :::: replacingsession[5] with values[i][5]
   }

range.setValues(values);

// Store the ID for the Calendar, which is needed to retrieve events by ID.
ScriptProperties.setProperty('calId', cal.getId());
}

function joinDateAndTime_(date, time) {
  date = new Date(date);
  date.setHours(time.getHours());
  date.setMinutes(time.getMinutes());
  Browser.msgBox(date); // to check time for debuging.
  return date;
  }

Link to spreadsheet with script:

https://docs.google.com/spreadsheet/ccc?key=0AorrYgKfVJpQdEdBY2lYdEpIUUc0TlRRUkNlNmRPTWc&usp=sharing

steps taken so far: 1. changed timezone of spreadsheet to locale. 2. changed timezone of calender to locale.

result: different time in spreadsheet and calender.

issue: must be same time in spreadsheet and calender.

references:

calendarApp createEvent added event on wrong day

How to use timeZone of calendar to set timeZone for date object

both minutes and hours. see spreadsheet provided in link. @Sergeinsas — devprashant

Serge insas Serge insas · Accepted Answer · 2014-09-06T19:17:37

I already had that kind of issue when using your method to join date and time from spreadsheet to JavaScript date object (some random values added to hours and/or minutes) so I found another approach that works more reliably...(while - I admit - less academic...)

I tested in a copy of your spreadsheet and when I setiup a conference the events are created as they should (in Calcutta Time, which is not perfect for me but I "moved" temporarily to check the results ;-)

here is the code, just copy and paste in your script to replace your function.

function joinDateAndTime_(date, time){
  var hrs = Number(Utilities.formatDate(time,Session.getScriptTimeZone(),'HH'));
  var min = Number(Utilities.formatDate(time,Session.getScriptTimeZone(),'mm'));
  var sec = Number(Utilities.formatDate(time,Session.getScriptTimeZone(),'ss'));
  Logger.log('date = '+Utilities.formatDate(date, Session.getScriptTimeZone() ,'dd-MMM-yyyy  HH:mm:ss'));
  Logger.log('time = '+hrs+':'+min+':'+sec);
  var dateAndTime = new Date(date).setHours(hrs,min,sec,0);
  Logger.log('full date object = '+Utilities.formatDate(new Date(dateAndTime), Session.getScriptTimeZone() ,'dd-MMM-yyyy  HH:mm:ss'))
  return new Date(dateAndTime);
}

Don't forget to check the timezones of the Sheet and script... (since you mentioned you tried different setups) They must of course match your area and calendars.

different time in google calendar when adding events from spreadsheet

1 Answers