Google spreadsheet to Google Calendar format date

Question

I am using the createEvent in google scripting.

I'm unable to add the event because the formating of my date column in the spreadsheet is not being read by the script correctly.

I need a format that reads "August 10, 2012 08:00:00". But when I check what my variable for my date time is that is set by my spreadsheet the format includes day of the week and is completely wrong. I've used the format option in the spreedsheet but that just seems to be a view control, not affecting how it is really stored.

How do I format my spreadsheet to match the format the createEvent function is expecting?

Thanks

Mogsdad Mogsdad · Accepted Answer · 2013-01-05T05:08:36

CalendarApp.createEvent() expects a Date object for both the startTime and endTime parameters. Therefore, the text format of the date in a spreadsheet is immaterial -- as long as the referenced cell contains a date.

var s = SpreadsheetApp.getActiveSheet();
var eventDate = s.getRange("B17").getValue(); // August 10, 2012 08:00:00, a date

var eventTitle = "Example";
var eventDetails = "Details";

//Get the calendar
var cal = CalendarApp.getCalendarsByName('Calendar-Name')[0];//Change the calendar name
var eventStartTime = eventDate;
//End time is calculated by adding an hour in the event start time 
var eventEndTime = new Date(eventDate.valueOf()+60*60*1000);
//Create the events
cal.createEvent(eventTitle, eventStartTime,eventEndTime ,{description:eventDetails});

If the referenced cell contains plain text instead, then the format must be something that the javascript Date object constructor can understand. As it turns out, the format you've shown is acceptable, although since the time zone has not been explicitly stated, it will be defaulted to the locale of your spreadsheet. Here's one change you could make to the example above:

// You could optionally generate a date object from the string representation
// HOWEVER createEvent() is smart enough to handle the string itself
var eventStartTime = new Date(eventDate);

If you want to experiment, take a look at Waqar Ahmad's tutorial on the subject. I was able to create an event by pasting in your example date string (August 10, 2012 08:00:00). The app script code in the tutorial is blindly creating a date object from whatever date is either selected from the calendar pop-up, or text string typed in. [Note there's a typo in the tutorial code, evenDate should read eventDate.]

Google spreadsheet to Google Calendar format date

1 Answers