#google-form

[full-stack-developer]

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; }