Create and Update Google Calendar Event using Google Form
These are the google form fields:-
1. Timestamp 2. Title 3. Venue 4. Date 5. Start Time 6. End Time 7. Participants 8. Note for this meeting 9. EventId
Reference:
https://code.google.com/p/google-apps-script-issues/issues/detail?id=574
http://blog.ouseful.info/2010/03/04/maintaining-google-calendars-from-a-google-spreadsheet/
In Script Editor:
//this is the ID of the calendar to add the event to, this is found on the calendar settings page of the calendar in question
var calendarId = "[email protected]"; var formUrl = "https://docs.google.com/forms/d/xxxxx-yyyyy/edit"; //below are the column ids of that represents the values used in the spreadsheet (these are non zero indexed) var formTimeStampId = 1; var titleId = 2; var locId = 3; var dateId = 4; var startTimeId = 5; var endTimeId = 6; var descId = 7; var calId = 9;
function getLatestAndSubmitToCalendar(e) { Â Logger.log("[METHOD] onFormSubmit" + e);
 // get the correct data set  var sheet = SpreadsheetApp.getActiveSheet();  var rows = sheet.getDataRange();  var numRows = rows.getNumRows();  var values = rows.getValues();
 // get last inserted row  var lastrow = rows.getLastRow();
 Logger.log("responses: " + responses);  createCalendarEventForRow(sheet, lastRow); }
function updateAllRow() {
 // get the correct data set  var sheet = SpreadsheetApp.getActiveSheet();  var rows = sheet.getDataRange();  var numRows = rows.getNumRows();
 for (var row = 2; row < numRows; row++) {   createCalendarEventForRow(sheet, row);  }
}
function createCalendarEventForRow(sheet, row) {  var lastrow = row + "";  // get properties of events  var m_date = sheet.getRange(lastrow,dateId,1,1).getValue();  var _range = sheet.getRange(lastrow,startTimeId,1,1);  var m_starttime =  sheet.getRange(lastrow,startTimeId,1,1).getValue().toLocaleTimeString().split(" ")[0];  var m_endtime = sheet.getRange(lastrow,endTimeId,1,1).getValue().toLocaleTimeString().split(" ")[0];;
 // Get the date value in the spreadsheet's timezone.  var spreadsheetTimezone = sheet.getParent().getSpreadsheetTimeZone();  Logger.log("Script Timezone: " + Session.getScriptTimeZone());  Logger.log("SpreadSheet Timezone: " + spreadsheetTimezone);
 // get response link to include in the event description  var formTimeStamp = new Date(sheet.getRange(lastrow,formTimeStampId,1,1).getValue());  var responseUrl = getEditResponseUrl(formTimeStamp);
 var startTime  = joinDateAndTime_(m_date, m_starttime, spreadsheetTimezone);  var endTime = joinDateAndTime_(m_date, m_endtime, spreadsheetTimezone);  var location =  sheet.getRange(lastrow,locId,1,1).getValue();  var subOn = "Submitted on :"+sheet.getRange(lastrow,formTimeStampId,1,1).getValue();  var desc = "Added by :"+sheet.getRange(lastrow,descId,1,1).getValue()+"\n"+subOn + "\nEdit meeting: <a href=\"" + responseUrl + "\">"+responseUrl+"</a>";  var title = sheet.getRange(lastrow,titleId,1,1).getValue();
 // check if update  var eventId = sheet.getRange(lastrow,calId,1,1).getValue().split("@");
 if (eventId != "") {   updateEvent(calendarId,eventId[0], title, startTime, endTime, desc, location);  } else {   // create or update event   eventId = createEvent(calendarId,title, startTime, endTime, desc, location);   Logger.log("[METHOD] event created " + eventId);   sheet.getRange(lastrow,calId,1,1).setValue(eventId);  }  }
function updateEvent(calendarId, eventId, title,startTime,endTime,desc, loc) { Â Â var existingEvent = Calendar.Events.get(calendarId, eventId); Â Â Logger.log("[Object] eventId " + eventId); Â Â Logger.log("[Object] event " + Â existingEvent); Â Â existingEvent.setSummary(title); Â Â existingEvent.setLocation(loc); Â Â existingEvent.start.dateTime = startTime.toISOString(); Â Â existingEvent.end.dateTime= endTime.toISOString(); Â Â existingEvent.description = desc; Â Â try { Â Â Â Calendar.Events.update(existingEvent, calendarId, eventId, existingEvent); Â Â } catch (e) { Â Â Â Logger.log(e); Â Â } Â Â Logger.log("[METHOD] event updated " + existingEvent.getId()); Â Â return existingEvent.getId(); }
function createEvent(calendarId,title,startTime,endTime,desc, loc) { var cal = CalendarApp.getCalendarById(calendarId); var start = new Date(startTime); var end = new Date(endTime); var event = cal.createEvent(title, start, end, {description : desc, location : loc}); Â return event.getId(); };
function getEditResponseUrl(formTimeStamp) { Â var form = FormApp.openByUrl(formUrl); Â var responses = form.getResponses(formTimeStamp); Â Logger.log("Response length " + responses.length); // Â var response = responses[responses.length-1]; Â var response = responses[0]; Â var lasturl = Â response.getEditResponseUrl(); Â Logger.log("lasturl " + lasturl); Â return lasturl; }
/** * Creates a single Date object from separate date and time cells. * * @param {Date} date A Date object from which to extract the date. * @param {Date} time A Date object from which to extract the time. * @return {Date} A Date object representing the combined date and time. */ function joinDateAndTime_(date, time, spreadsheetTimezone) {  var _time  = time.split(":"); // in HH:mm:ss format  var _date = new Date(date);  //_date.setHours(getValueAsHours(time, spreadsheetTimezone));  //_date.setMinutes(getValueAsMinutes(time, spreadsheetTimezone));  _date.setHours(_time[0]);  _date.setMinutes(_time[1]);  return _date; }
function onEdit(e){ Â // Set a comment on the edited cell to indicate when it was changed. Â var range = e.range; Â range.setNote('Last modified: ' + new Date()); }
function getValueAsSeconds(date,spreadsheetTimezone) { // Â var dateString = Utilities.formatDate(value, spreadsheetTimezone, // Â Â Â 'EEE, d MMM yyyy HH:mm:ss'); // Â var date = new Date(dateString);
 // Initialize the date of the epoch.  var epoch = new Date('Dec 30, 1899 00:00:00');
 // Calculate the number of milliseconds between the epoch and the value.  var diff = date.getTime() - epoch.getTime();
 // Convert the milliseconds to seconds and return.  return Math.round(diff / 1000); }
function getValueAsMinutes(range) { Â return getValueAsSeconds(range) / 60; }
function getValueAsHours(range) { Â return getValueAsMinutes(range) / 60; }
















