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