By @mhawksey

Using Google Apps Script for a event booking system (Spreadsheet to Calendar & Site | Form to Spreadsheet, Email and possible Contacts)

Update: New version is posted here The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, ever
A while ago I was looking at Updating a Google Calendar and Google Site from a Google Spreadsheet (the beginnings of an event booking system). This idea was inspired by Tony Hirst’s work on updating a google calendar from a spreadsheet the endpoint being a way to manage a simple event booking system using Google Apps. This all started to unravel as I couldn’t find a way to create a custom booking form for each event. Tony suggested that I should look at just using a generic form which was manually updated with new events, the system handling the rest of the booking process. So with that little hurdle out of the way I revisted my script and as well as rewritting most of it I took the concecpt a little further.
So what do we have now? The video below walks through the workflow:

[You might want to enable full screen view to see what is happening]

The core code is at the end of this post and you can access the full spreadsheet and script here (if you want to copy this spreadsheet to your Google Apps domain account you’ll need to edit the link to http://spreadsheets.google.com/a/yourdomainname.com/ccc?key=0AtqJUiWU5zvNdGZCV3pmTDFWVlVXVDBodFZKR2czSXc <- thanks @Eion). Once you open it click File –> Make a copy to allow you to edit. The bulk of this code is actually a reworking of some existing Google Apps Script tutorials:

To use this code yourself some variables need defining (I could have just called these from a sheet but ran out of time). To do this click Tools –>  Scripts –> Script editor… and you’ll see the variables at the top. The first time you run the script a security dialog will popup. You will need to ‘grant access’ for it to work.

Instructions

Once you do this here are some instructions for use (in general yellow fields are for user input):

  1. In the ‘Events’ sheet enter title, description, dates etc. You can enter as many events as you like.
  2. To make an event public enter the text ‘Add’ in the action column, then click Booking System –> Process events. This will push it to calendar and site and create a unique sheet for the event.
  3. To allow bookings click on Form –> Edit form and add the event to the drop down using the format ‘ID#{theEventNumberUsedOnTheSheet}{theNameOfYourEvent}’ – !this format including whitespaces is really important
  4. A limitation of the Google Apps Script is it doesn’t yet handle onFormSubmit actions (Google are looking to add this), so for now to process bookings you need to click Booking System –> Process Bookings. This sends an email to your admin to notify them that there is a booking
  5. To approve a booking enter ‘Y’ in the Action column and again click Booking System –> Process Bookings. (You can approve as many booking as you like in one go). The script will then send a confirmation to the delegate and copy their details to the appropriate event sheet.
  6. When you are ready to send joining instructions you can edit the message in the EmailTemplates sheet (you can also edit the format of the other emails used). When you are ready to send go to the correct Event sheet then click Booking System –> Email joining instructions

Limitations/waiting for Google to fix

Deleting events from calendar and sites is still a manual process (I don’t recall anywhere in the API which allows you to do this). You will also see in the code I’ve commented out a section which would add all delegates to Google Contacts.

The code

// AppEventManger Script
// by mhawksey at http://bit.ly/mashe
// User defined variables
var BOOKING_FORM_URL = "http://bit.ly/bookingurl"; //your booking form url (I bit.ly'd mine to shorten
var SITE_DOMAIN = "Your domain"; // your apps domain name
var SITE_NAME = "Name of your site"; // your apps site name
var CALENDAR_EVENTS = "Name of your calendar"; // the name of the calendar to update
var STATE_MANAGER_EMAIL = "youremail@gmail.com"; //email address for booking notifications
// some additional variables to change at your peril
var COLUMN_ACTION = 8;
var COLUMN_STATE = 9;
var COLUMN_BOOKING_ID = 10;
var COLUMN_COMMENT = 11;
var COLUMN_EVENT_ID = 2;
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "Process Events", functionName: "processEvents"}, {name: "Process Bookings", functionName: "onFormSubmit"}, {name: "Email joining instructions", functionName: "sendEmails"} ];
ss.addMenu("Booking System", menuEntries);
}
function processEvents() {
//declare vars
var cal = CalendarApp.openByName(CALENDAR_EVENTS);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName("Events"); // ref sheet name (thought I might have ended up with multiple sheets)
var data = getRowsData(dataSheet);
var cal = CalendarApp.getDefaultCalendar();
var site = SitesApp.getSite(SITE_DOMAIN, SITE_NAME); // .getSite(domain, sitename)
var annPage = site.getAnnouncementsPages();
// pull data
for (var i = 0; i < data.length; ++i) {
var row = data[i];
row.rowNumber = i + 2;
if (row.action =="Add"){
var descText = row.description + "More info: " + BOOKING_FORM_URL;
cal.createEvent(row.title, row.start, row.stop, {location:row.location, description:descText}); // create calendar event
var message = "<strong>Start:</strong> " + Utilities.formatDate(row.start, "GMT", "dd/MM/yy HH:mm")
+ "<br/><strong>Finish:</strong> " + Utilities.formatDate(row.stop, "GMT", "dd/MM/yy HH:mm")
+ "<br/><strong>Location:</strong> " + row.location
+ "<br/><strong>Description:</strong> " + row.description
+ "<br/><a href='" + BOOKING_FORM_URL + "'>Click here to book a place</a>"; // prepare message
site.createAnnouncement(row.title, message, annPage[0]); // add announcement to site
var annList = site.getAnnouncements();
var eventID = annList.length; // get announcement ID
var eventSheetName = "Event#" + eventID;
ContactsApp.createContactGroup(eventSheetName); //create a contact group for the event
dataSheet.getRange(row.rowNumber, 2, 1, 1).setValue(new Date()); // add today's date/time to 'Added Date' column
// create a new event booking sheet from template
ss.setActiveSheet(ss.getSheetByName("EventTMP"));
var nuSheet = ss.duplicateActiveSheet();
ss.setActiveSheet(nuSheet);
ss.renameActiveSheet(eventSheetName); // rename using event Id
// insert data into sheet
var eventSheet = ss.getSheetByName(eventSheetName);
eventSheet.getRange(1, 2, 1, 1).setValue(row.numberOfPlaces);
eventSheet.getRange(1, 3, 1, 1).setValue(row.title);
eventSheet.getRange(2, 3, 1, 1).setValue(row.location);
eventSheet.getRange(3, 6, 1, 1).setValue(row.start);
eventSheet.getRange(3, 8, 1, 1).setValue(row.stop);
ss.setActiveSheet(ss.getSheetByName("Events")); // switch back to events sheet
dataSheet.getRange(row.rowNumber, 3, 1, 1).setValue(eventSheetName);
dataSheet.getRange(row.rowNumber, 1, 1, 1).setValue("Added by "+Session.getUser().getUserLoginId()); //set the fact that we have updated the calendars for this event
Browser.msgBox(row.title + " has been published to the calendar and website. IMPORTANT: Add this event to the Form to allow delegates to book in.");
}
}
}
function onFormSubmit() {
// This function has been designed for when App Scripts automatically runs when a Form is submitted.
// For now it has to be manually started. See http://code.google.com/p/google-apps-script-issues/issues/detail?id=4
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName("Bookings");
var templateSheet = ss.getSheetByName("EmailTemplates");
var emailTemplate = templateSheet.getRange("A7").getValue();
// Create one JavaScript object per row of data.
data = getRowsData(dataSheet);
for (var i = 0; i < data.length; ++i) {
// Get a row object
var row = data[i];
row.rowNumber = i + 2;
if (!row.state) { // if no state notify admin of booking
var emailTemplate = templateSheet.getRange("A7").getValue();
var emailText = fillInTemplateFromObject(emailTemplate, row);
var emailSubject = "Booking Approval Request ID: "+ row.rowNumber;
MailApp.sendEmail(STATE_MANAGER_EMAIL, emailSubject, emailText);
dataSheet.getRange(row.rowNumber, COLUMN_STATE).setValue("TBC");
} else if (row.action == "Y") { // if admin have approved send confirmation
var approvedOrRejected = (row.action == "Y") ? "confirmed" : "rejected";
// capture the sheet to copy booking to
var eventID = row.event;
eventID = eventID.substring(eventID.indexOf("#")+1,eventID.indexOf(" -"));
var eventSheet = ss.getSheetByName("Event#" + eventID);
// create a booking ID
var bookingID = "ID#"+eventID+"B"+row.rowNumber;
dataSheet.getRange(row.rowNumber, COLUMN_BOOKING_ID).setValue(bookingID);
// copy booking details to event sheet
var rowNum = eventSheet.getLastRow()+1;
eventSheet.getRange(rowNum, 3, 1, 1).setValue(bookingID);
eventSheet.getRange(rowNum, 4, 1, 1).setValue(row.timestamp);
eventSheet.getRange(rowNum, 5, 1, 1).setValue(row.firstName);
eventSheet.getRange(rowNum, 6, 1, 1).setValue(row.surname);
eventSheet.getRange(rowNum, 7, 1, 1).setValue(row.email);
eventSheet.getRange(rowNum, 8, 1, 1).setValue(row.organisation);
eventSheet.getRange(rowNum, 9, 1, 1).setValue(row.otherInfo);
eventSheet.getRange(rowNum, 10, 1, 1).setValue(row.comments);
//Add/edit details of new/existing delegate to Google Contacts
// This code generates a 'Service error: Contacts : POST method does not support
// concurrency' looks like an API bug.
// http://www.google.com/support/forum/p/apps-script/thread?tid=5f4a4b645490381e&hl=en
/*
var curDate = Utilities.formatDate(new Date(), "GMT", "dd/MM/yy HH:mm");
var c = ContactsApp.findByEmailAddress(row.email);
if (!c){
var c = ContactsApp.createContact(row.firstName, row.surname, row.email);
var prop = {};
prop.Organisation = row.organisation;
prop.Added = curDate;
c.setUserDefinedFields(prop);
var group = ContactsApp.findContactGroup(row.organisation);
if (!group){
var group = ContactsApp.createContactGroup(row.organisation);
}
c.addToGroup(group);
} else {
c.setUserDefinedField("Last activity", curDate);
}
//var group = ContactsApp.findContactGroup("EventID#"+eventID);
//c.addToGroup(group); // add contact to event group
*/
//prepare email
var emailTemplate = templateSheet.getRange("A4").getValue();
var emailText = fillInTemplateFromObject(emailTemplate, row);
var emailSubject = "Booking Approved (Booking ID: "+ bookingID + ")";
// fill in the template using stored variables
emailText = emailText.replace("STATE_MANAGER_EMAIL", STATE_MANAGER_EMAIL || "");
emailText = emailText.replace("APPROVED_OR_REJECTED", approvedOrRejected || "");
emailText = emailText.replace("BOOKING_ID", bookingID || "");
MailApp.sendEmail(row.email, emailSubject, emailText);
// Update the state of bookings
dataSheet.getRange(row.rowNumber, COLUMN_STATE).setValue(STATE_APPROVED+" by "+Session.getUser().getUserLoginId());
dataSheet.getRange(row.rowNumber, COLUMN_ACTION).setValue("");
}
}
}
// Code to send joining instructions - based on simple mail merge code from
// Tutorial: Simple Mail Merge
// Hugo Fierro, Google Spreadsheet Scripts Team
// March 2009
function sendEmails() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getActiveSheet();
var eventName = ss.getRange("C1").getValue();// pull event name from sheet
var dataRange = dataSheet.getRange(5, 3, dataSheet.getLastRow() - 3, 8);
var templateSheet = ss.getSheetByName("EmailTemplates");
var emailTemplate = templateSheet.getRange("A10").getValue();
// Create one JavaScript object per row of data.
objects = getRowsData(dataSheet,dataRange,4);
// For every row object, create a personalized email from a template and send
// it to the appropriate person.
for (var i = 0; i < objects.length; ++i) {
// Get a row object
var rowData = objects[i];
rowData.rowNumber = i + 5;
// Generate a personalized email.
// Given a template string, replace markers (for instance ${"First Name"}) with
// the corresponding value in a row object (for instance rowData.firstName).
if (!rowData.emailed) {
var emailText = fillInTemplateFromObject(emailTemplate, rowData);
emailText = emailText.replace("EVENT_NAME", eventName);
var emailSubject = "Joining Instrucations for " + eventName;
MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText);
dataSheet.getRange(rowData.rowNumber, 2).setValue(Utilities.formatDate(new Date(), "GMT", "dd/MM/yy HH:mm"));
}
}
}
// Replaces markers in a template string with values define in a JavaScript data object.
// Arguments:
//   - template: string containing markers, for instance ${"Column name"}
//   - data: JavaScript object with values to that will replace markers. For instance
//           data.columnName will replace marker ${"Column name"}
// Returns a string without markers. If no data is found to replace a marker, it is
// simply removed.
function fillInTemplateFromObject(template, data) {
var email = template;
// Search for all the variables to be replaced, for instance ${"Column name"}
var templateVars = template.match(/\$\{\"[^\"]+\"\}/g);
// Replace variables from the template with the actual values from the data object.
// If no value is available, replace with the empty string.
for (var i = 0; i < templateVars.length; ++i) {
// normalizeHeader ignores ${"} so we can call it directly here.
var variableData = data[normalizeHeader(templateVars[i])];
email = email.replace(templateVars[i], variableData || "");
}
return email;
}
// there are also some Google example functions used getRowsData, getObjects, normalizeHeaders, normalizeHeader, isCellEmpty, isAlnum and isDigit
Exit mobile version