S.O.S. Trying to create an event in google calendar via google (sheets + script), but failing. I think it has to do with the time?

Question

So I based my script on a lot of other scripts from wonderful humans around the internet. However, their script created All Day events and I only want an event to be created for an hour, so I tweaked it (unsuccessfully).

Here is my script

    /**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the exportEvents() function.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 2;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "shmoop.com_6bibh4i9sg0jp5qicbsl1n31ik@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calId);
  for (i=0; i<data.length; i++) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[0]);  // First column
    var project = row[1]+row[3]+row[4];           // Second column
    var dueDate = new Date(row[6])
    var tstart = new Date(dueDate.setTime(17,00,00,00));
    var tstop = new Date(dueDate.setTime(18,00,00,00));
    var id = row[15];              // Sixth column == eventId
      try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }
    if (!event) {
      //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
      var newEvent = cal.createEvent(project, tstart, tstop).getId();
      row[15] = newEvent;  // Update the data array with event ID
    }    else {
      event.setTitle(project);
      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we CAN set recurrence!
      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      event.setRecurrence(recurrence, tstart, tstop);
    }
    debugger;
  // Record all event IDs to spreadsheet
  range.setValues(data);
}

}

I know the script runs because the google spreadsheet does update with an event ID everytime I run exportEvents(). But I suspect because of the tsart, tstop variable, no event is actually pushed to the calendar. Oh, also, here is an example of a row with the relevant columns on the spreadsheet:

Any help would be welcome.

score 0 · Accepted Answer · answered Jul 16 '15 at 20:54

You need to change the setTime

var tstart = new Date(dueDate.setTime(17,00,00,00));
var tstop = new Date(dueDate.setTime(18,00,00,00));

to a setHours

var tstart = new Date(dueDate.setHours(17,00,00,00))
var tstop =  new Date(dueDate.setHours(18,00,00,00))

description of setTime:

Sets a date to a specified number of milliseconds after/before January 1, 1970

Example:

Add 1332403882588 milliseconds to January 1, 1970, and display the new date and time:

var d = new Date();
d.setTime(1332403882588);

The result of d will be:

Thu Mar 22 2012 04:11:22 GMT-0400 (Eastern Standard Time)

Description of setHours:

Sets the hour of a date object

Example

Set the hour to 15:

var d = new Date();
d.setHours(15);

The result of d will be:

Thu Jul 16 2015 15:53:22 GMT-0400 (Eastern Standard Time)

source : http://www.w3schools.com/jsref/jsref_obj_date.asp

Thank you so much! It works now. i just changed setTime to setHours and voila. — James, Jul 17 '15 at 04:11

score 0 · Answer 2 · answered Jul 16 '15 at 21:21

The setTime() method actually takes milliseconds as parameter.

You can check this page for reference.

So, for this event you have to do this way:

var tstart = new Date(dueDate.setTime(1437395333000));

so the event start date value will be:

Mon Jul 20 05:28:53 GMT-07:00 2015

The setTime() method sets the time from January 1, 1970. You can calculate the milliseconds and pass that value in the method. I referred to this page for calculating the milliseconds.

After making the changes above in your code, I was able to create an event.

Hope that helps!

Thanks for replying, I appreciate it. – James Jul 17 '15 at 04:12 — James, Jul 17 '15 at 04:12

S.O.S. Trying to create an event in google calendar via google (sheets + script), but failing. I think it has to do with the time?

2 Answers2