Google Script to Submit Form to Calendar Event

Question

School teacher using my personal Google account to create forms and publish calendars to my class web site.

Trying to make it fast and easy as possible for kids (with no access to Google Apps) to schedule make up tests using this form.

A quick and dirty victory would be to have their form submission added to my calendar as an all-day event with the details in the description field. I could then review and edit the event to the specific time requested (I have only certain time options offered on the form as a drop-down selection item.)

A beautiful and elegant solution would be to have the script write the event on the day and time indicated on the drop-down, but I think that would require sorcery....

The form writes its responses to the spreadsheet fine and email notification works too, but can't get the calendar event creation to happen.

Will keep trying to find/learn the necessary code but would greatly appreciate a kick in the right direction.

Cheers,

Answer 1

Here is a "sorcery gift" since you're a teacher (so am I) and not a programmer (neither am I) that hopefully does exactly what you need.

EDIT : I added the submission date/time in description

function createCalEvent(e) {
  Logger.log(e);
  // this will return something like this :
  /*
  {values=[11/9/2014 22:30:00, serge, test descr, 11/7/2014, Before school | 7:30], 
  namedValues={Your Full Name=[serge], Work to Make Up=[test descr], Date You Will Make Up Assignment=[11/7/2014], 
  Makeup Time=[Before school | 7:30], Timestamp=[11/9/2014 22:30:00]}, range=Range, source=Spreadsheet, authMode=FULL}
  */
  var cal = CalendarApp.getCalendarById("[e-mail]");// replace with the right calendar ID, this one is for test (and is public)
  var name = e.namedValues["Your Full Name"][0];
  var descr = e.namedValues["Work to Make Up"][0];
  var submitTime = e.namedValues["Timestamp"][0];
  var date = e.namedValues["Date You Will Make Up Assignment"][0].split('/');
  var time = e.namedValues["Makeup Time"][0].split('|')[1].split(':');
  Logger.log(name+' '+descr+' '+date+' '+time); // this will return  serge test descr 11,7,2014  7,30
  var startTime = new Date(date[2],date[0]-1,date[1]);
  startTime.setHours(time[0],time[1],0,0);
  endTime = new Date(startTime.getTime()+3600000); //assuming event is 1 hour long
  Logger.log('start='+startTime+'  end='+endTime);
  cal.createEvent('name = '+name, startTime, endTime, {'description':descr+' (subimitted on '+submitTime+')'});
}

You will have to set up a trigger (on form submit) to trigger that function when a form is submitted.

IMPORTANT NOTE : don't try this code from the script editor without sending a form, it won't work !!! (e will be undefined, obviously)