March Madness Challenge - Day 15

This time tried to create Events from a Google Spreadsheet. Came close but ran out of time. The time parsing was messed up. I could not find clear docs about the format of the Spreadsheet date that would be compatible with the Javascript Date function. None of the options looked to be a visual match. Adding a menu item called "makeEvent" worked. Selecting info from the active row seemed to work well. Even though I really wanted getDataRange inside of a range. I new the number of columns so I could just grab what I needed. Lastly, the "options" part of createEvent(title, starttime, endtime, options) was a Javascript Object. This always ended up blank, probably an error on my part. But no multi value examples were available.

I did find this useful set of examples when I was flailing around Maintaing a Google Calendar from a Spreadsheet, Reprise.

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "MakeEvent", functionName: "getEventFromSpreadSheet"} ];
  sheet.addMenu("Events", menuEntries);
}

function getEventFromSpreadSheet () {
  
  var range = SpreadsheetApp.getActiveRange();  

  var vals = range.getValues();
  var Title = vals[0];
  var Description = vals[1];
  var Summary = vals[2];
  var StartTime = vals[3];
  var EndTime = vals[4];
  var Location = vals[5];
  var Guests = vals[6];
  var GuestsStatus = vals[7];
  var Invites = vals[8];
  var Options = {description: Description,
      guests: Guests,
      location: Location,
      sendInvites: Invites
      };
  makeEvent(Title, StartTime, EndTime, Options);
}

function makeEvent(Title, StartTime, EndTime, Options) {
  var cal = CalendarApp.getDefaultCalendar();
  cal.createEvent(Title, new Date(StartTime), new Date(EndTime), {location:'blah loc'}, {description: 'blah desc'});  
}

W00t the fixes just clicked. Given my comments below here is the fixed code:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "MakeEvent", functionName: "getEventFromSpreadSheet"} ];
  sheet.addMenu("Events", menuEntries);
}

function getEventFromSpreadSheet () {
  
  var range = SpreadsheetApp.getActiveRange();  

  var vals = range.getValues();
  var Title = vals[0][0];
  var Description = vals[0][1];
  var Summary = vals[0][2];
  var StartTime = vals[0][3];
  var EndTime = vals[0][4];
  var Location = vals[0][5];
  var Guests = vals[0][6];
  var GuestsStatus = vals[0][7];
  var Invites = vals[0][8];
  var Options = {description: Description,
      guests: Guests,
      location: Location,
      sendInvites: Invites
      };

  makeEvent(Title, StartTime, EndTime, Options);
}

function makeEvent(Title, StartTime, EndTime, Options) {
  var cal = CalendarApp.getDefaultCalendar();
  cal.createEvent(Title, new Date(StartTime), new Date(EndTime), Options); 
}

And here is the CSV of the spreadsheet I tested with:

Title,Description,Summary,StartTime,EndTime,Location,Guests,GuestsStatus
MyTest,MyTest Description,MyTest Summary,9/22/2010 10:00:00,9/22/2010 11:00:00,Home Test,0000@noemail.gmail.com,yes
MyTest1,MyTest Description,MyTest Summary,10/22/2010 0:00:00,10/22/2010 0:00:00,Home Test,0000@noemail.gmail.com,yes