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 = "[email protected]"; //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

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

  1. Hi Martin,
    Your script is fantastic, but I have a a couple of requests.

    Do you have a generic spreadsheet/script that simply:
    - reads the data in various columns in the sheet.
    - then creates an Announcement Page post for your Google Site using the data from the columns as input into a html layout.

    It would basically be a very stripped-down version of your "processEvents()" function.

    The 2nd request is a generic spreadsheet/script that:
    - reads the data in various columns in the sheet.
    - then creates an Announcement Page post for your Google Site using the data from the columns as input into a html layout.
    - and adds an event to a Google Calendar.

    If you could share this stripped-down script, I'm sure lots of people would appreciate it.

    I also have some (newbie) questions:
    1. Does it matter if your site name or column names have spaces or special character in them?
    2. What if your site has more than one Announcements Page?

    thanks again,
    Marcello

    1. Post author

      @Marcello - Less is always easy than more. This spreadsheet I think does what you are looking for (if you don't want the calendar update just comment out the line which begins 'cal.createEvent'

      >Does it matter if your site name or column names have spaces or special character in them?

      Columns - no. Site names maybe special characters, space usually get replace with a hyphen

      >What if your site has more than one Announcements Page?

      Good point. The new spreadsheet lets you define which announcement page to update. For the page name convert to lowercase and hyphenate spaces i.e. enter 'Events Page' as 'event-page'

      Enjoy!
      Martin

  2. Pingback:

  3. @Martin
    I tried clicking on the "AppEventManager Lite" link, but looks like it hasn't been shared.

    "We're sorry, [email protected] does not have permission to access this spreadsheet."

    I can't wait to see it.

  4. Sweeet! I got it working, but I had to comment out this line

    //var cal = CalendarApp.getDefaultCalendar();

    to stop it updating events to my default calendar and instead update the calendar specified in

    var CALENDAR_EVENTS = "events";

    Thank You Martin!
    Sending a (virtual) beer your way.

  5. I can not get the Process Events script to run.

    TypeError: Cannot call method "getAnnouncementsPages" of null.

    My User defined variables are:-
    var BOOKING_FORM_URL = "http://spreadsheets.google.com/viewform?hl=en_GB&formkey=dGhmdC1xVXBZaXZlR3hXV3BwMjk0VlE6MA";
    var SITE_DOMAIN = "gorgiecityfarm.org.uk"; // your apps domain name
    var SITE_NAME = "wertwert"; // your apps site name
    var CALENDAR_EVENTS = "GCF IT Technician"; // the name of the calendar
    var STATE_MANAGER_EMAIL = "[email protected]"; //email address
    var ANN_PAGE_NAME = "twt"; //name of your announcements page in the site

    Do the URL variables have to be absolute? Many thanks for your tutorial and advice.

  6. @Eoin

    Martin will be able to give you the definitive answer, but looks like the script can't find your announcements page.

    Double check SITE_DOMAIN, SITE_NAME, and ANN_NAME_PAGE.

    If this is your default/root site, then SITE_NAME should be "www"

  7. Post author

    @Eoin Marcello's suggestion is good as any I could give ;-) One thing to aware of is my variable names are a bit misleading, so while it says ANN_PAGE_NAME it actually refers to the page url. So if this was your announcement page ANN_PAGE_NAME = "events-another-example" i.e. the end part of the url. Below are the setting I used on my own domain (minus email address):

    var BOOKING_FORM_URL = "http://bit.ly/bookingurl"; //your booking form url (I bit.ly'd mine to shorten
    var SITE_DOMAIN = "sub-tweeter.net"; // your apps domain name
    var SITE_NAME = "mashe-events-from-calendar"; // your apps site name
    var CALENDAR_EVENTS = "Events"; // the name of the calendar to update
    var STATE_MANAGER_EMAIL = "[email protected]"; //email address for booking notifications
    var ANN_PAGE_NAME = "events"; //name of your announcements page in the site

  8. Thanks for the confimation that the script variables are not absolute.
    The details I posted do seem correct. There is no default site on Google Sites for that user, it is explicity named 'wertwert'.
    Also I initially created the announcement page from the default template, but then realised it had to be in the 'Announcement' format.

    I am still unclear why the script can not connect. I will try again. Are there any tips to aid debugging. The console variables seemed a bit sparse.

    1. Post author

      @eion Its a bit messy but I use Browser.msgBox(); to debug. So after the var declares in processEvents() you could insert Browser.msgBox(site.getSiteName()); which should return the site name when run.

  9. I've been working on my site for a few days now, and have made a lot of progress thanks to your great script.

    It's all working just the way it should.

    There was one error that was driving my crazy!

    "Cannot find method createEvent(string,string,string,object). (#39)"

    I eventually realized it was because the data/date in my "Start" & "Stop" columns were not formatted correctly.

    ie. If your Timestamp column reads "2010/03/30 0:11:54" then the "Start" & "Stop" columns should follow exactly the same date format.

    Beware!

  10. Got it working at last.

    I think there were two issues:

    1) The link above to 'full spreadsheet and script' opens in Google Docs not in my domain. That is it opens in spreadsheets.google.com not in spreadsheets.google.com/a/gorgiecityfarm.org.uk Google treats them as separate. I was able to register separately in both, and the docs in each different and unconnected.

    2) I was entering 'add' (in lower case) in the Events sheet 'Action' column. It must be 'Add'.

    Thanks for bringing together this great resource. Are you aware of any other work in a similar vain? Have you any plans to extend the above?

    1. Post author

      @eoin - thanks for highlighting these points!

      Other than Tony Hirst's work I'm not aware of anyone else looking at this area, but saying that I haven't really looked that hard. Within a spreadsheet if you click Tools -> Scripts -> Insert ... you'll see the start of a script library. There might be other functionality in here you could use.

      Whilst I realise there are a number of areas I could improve my own code (error catching etc) I'm going to put it aside for now until onFormSubmit and contact groups have been fixed

      Thanks,
      Martin

  11. Thanks, this blog aided me somewhat in narrowing down some problems with the latest release candidate, Why do they always leave out vital documentation when they release a new version? It may be minor to them but not to me. I'm sure i'm not alone either.

  12. Sharon Whitfield

    I would like to use this script to allow people to reserve available equipment. Basically what I want to do is that each time a student Books the equipment, the booking gets added to the Calendar. I was thinking that I could add the Calendar code to the OnFormSubmit function, but I keep getting an error. Also, I tried to use the caltest1 function, but then it completely throws off my code. Does anyone have any thoughts on how I could do this?
    I love the approval and email portion of this script so I wanted to use it.
    Thanks

  13. Sharon Whitfield

    I figured it out and thought I would share:

    // AppEventManger Script
    // by mhawksey at http://bit.ly/mashe

    // User defined variables
    var BOOKING_FORM_URL = "https://spreadsheets.google.com/a/cnu.edu/viewform?hl=en&formkey=dEZYNVBxcG96WXBvb0lWT0dxZGtVVFE6MA#gid=11"; //your booking form url (I bit.ly'd mine to shorten
    var SITE_DOMAIN = "cnu.edu"; // your apps domain name
    var SITE_NAME = "media-center-scheduled-resources-events"; // your apps site name
    var CALENDAR_EVENTS = "Resources"; // the name of the calendar to update
    var STATE_MANAGER_EMAIL = "[email protected]"; //email address for booking notifications
    var ANN_PAGE_NAME = "resources-2"; //name of your announcements page in the site

    // some additional variables to change at your peril
    var COLUMN_ACTION = 8;
    var COLUMN_STATE = 9;
    var COLUMN_RESERVATION_ID = 10;
    var COLUMN_COMMENT = 11;
    var COLUMN_RESOURCE_ID = 2;

    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [ {name: "Process Resources", functionName: "processResources"}, {name: "Process Reservations", functionName: "onFormSubmit"} ];
    ss.addMenu("Reservation System", menuEntries);
    }

    function processResources() {
    //declare vars
    //var cal = CalendarApp.openByName(CALENDAR_EVENTS);
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var dataSheet = ss.getSheetByName("Resources"); // 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();
    for (var j = 0; j < annPage.length; ++j){
    if (annPage[j].getPageName() == ANN_PAGE_NAME){
    var annIndex = j;
    }
    }
    // 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 = "Typical Circulation Duration (in hours): " + row.circulationDuration
    + "Quantity of Resource: " + row.quantity
    + "Location: " + row.location
    + "Description: " + row.description
    + "Click here to reserve resource"; // prepare message
    site.createAnnouncement(row.title, message, annPage[annIndex]); // add announcement to site
    var annList = site.getAnnouncements();
    var resourceID = annList.length; // get announcement ID
    var resourceSheetName = "Resource#" + resourceID;
    ContactsApp.createContactGroup(resourceSheetName); //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(resourceSheetName); // rename using event Id
    // insert data into sheet
    var resourceSheet = ss.getSheetByName(resourceSheetName);
    resourceSheet.getRange(1, 2, 1, 1).setValue(row.numberOfPlaces);
    resourceSheet.getRange(1, 3, 1, 1).setValue(row.title);
    resourceSheet.getRange(2, 3, 1, 1).setValue(row.location);
    resourceSheet.getRange(3, 6, 1, 1).setValue(row.circulationDuration);
    resourceSheet.getRange(3, 8, 1, 1).setValue(row.quantity);
    ss.setActiveSheet(ss.getSheetByName("Resources")); // switch back to events sheet
    dataSheet.getRange(row.rowNumber, 3, 1, 1).setValue(resourceSheetName);
    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 website. IMPORTANT: Add this Resources to the Form to allow patrons to reserve the resource.");
    }
    }
    }

    function onFormSubmit() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var dataSheet = ss.getSheetByName("Reservations");
    var templateSheet = ss.getSheetByName("EmailTemplates");
    var emailTemplate = templateSheet.getRange("A7").getValue();
    var cal = CalendarApp.openByName("Resources");

    // 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 = "Reservation 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 ="confirmed";
    var resourceID = row.resource;
    resourceID = resourceID.substring(resourceID.indexOf("#")+1,resourceID.indexOf(" -"));
    var resourceSheet = ss.getSheetByName("Resource#" + resourceID);

    // create a booking ID
    var reservationID = "ID#"+resourceID+"B"+row.rowNumber;
    dataSheet.getRange(row.rowNumber, COLUMN_RESERVATION_ID).setValue(reservationID);

    //copy booking details to event sheet
    var rowNum = resourceSheet.getLastRow()+1;
    resourceSheet.getRange(rowNum, 3, 1, 1).setValue(reservationID);
    resourceSheet.getRange(rowNum, 4, 1, 1).setValue(row.timestamp);
    resourceSheet.getRange(rowNum, 5, 1, 1).setValue(row.firstName);
    resourceSheet.getRange(rowNum, 6, 1, 1).setValue(row.lastName);
    resourceSheet.getRange(rowNum, 7, 1, 1).setValue(row.email);
    resourceSheet.getRange(rowNum, 8, 1, 1).setValue(row.startDate);
    resourceSheet.getRange(rowNum, 9, 1, 1).setValue(row.endDate);
    resourceSheet.getRange(rowNum, 10, 1, 1).setValue(row.comment);
    resourceSheet.getRange(rowNum, 11, 1, 1).setValue(row.patronComments);

    //prepare email
    var emailTemplate = templateSheet.getRange("A4").getValue();
    var emailText = fillInTemplateFromObject(emailTemplate, row);
    var emailSubject = "Reservation Approved (Reservation ID: "+ reservationID + ")";

    // 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("RESERVATION_ID", reservationID || "");
    MailApp.sendEmail(row.email, emailSubject, emailText);

    // Update the state of bookings

    dataSheet.getRange(row.rowNumber, COLUMN_STATE).setValue(approvedOrRejected+" by "+Session.getUser().getUserLoginId());
    dataSheet.getRange(row.rowNumber, COLUMN_ACTION).setValue("");

    //Create Event
    var descText = "Patron Name: " + row.firstName+ " " + row.lastName + " Email: "+ row.email;
    cal.createEvent(row.resource, row.startDate, row.endDate, {description:descText});
    }
    else if (row.action== "N") {
    var approvedOrRejected="rejected";
    //prepare email
    var emailTemplate = templateSheet.getRange("A4").getValue();
    var emailText = fillInTemplateFromObject(emailTemplate, row);
    var emailSubject = "Reservation Rejected";

    // fill in the template using stored variables
    emailText = emailText.replace("STATE_MANAGER_EMAIL", STATE_MANAGER_EMAIL || "");
    emailText = emailText.replace("APPROVED_OR_REJECTED", approvedOrRejected || "");
    MailApp.sendEmail(row.email, emailSubject, emailText);

    dataSheet.getRange(row.rowNumber, COLUMN_STATE).setValue(approvedOrRejected+" 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 resourceName = 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("RESOURCE_NAME", resourceName);
    var emailSubject = "Pick up Equipment Instructions for " + resourceName;

    MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText);
    dataSheet.getRange(rowData.rowNumber, 2).setValue(Utilities.formatDate(new Date(), "EST", "MM/dd/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;
    }

    /////////////////////////////////////////////////////////////////////////////////
    // Code reused from Reading Spreadsheet Data using JavaScript Objects tutorial //
    /////////////////////////////////////////////////////////////////////////////////

    // getRowsData iterates row by row in the input range and returns an array of objects.
    // Each object contains all the data for a given row, indexed by its normalized column name.
    // Arguments:
    // - sheet: the sheet object that contains the data to be processed
    // - range: the exact range of cells where the data is stored
    // This argument is optional and it defaults to all the cells except those in the first row
    // or all the cells below columnHeadersRowIndex (if defined).
    // - columnHeadersRowIndex: specifies the row number where the column names are stored.
    // This argument is optional and it defaults to the row immediately above range;
    // Returns an Array of objects.
    function getRowsData(sheet, range, columnHeadersRowIndex) {
    var headersIndex = columnHeadersRowIndex || range ? range.getRowIndex() - 1 : 1;
    var dataRange = range ||
    sheet.getRange(headersIndex + 1, 1, sheet.getMaxRows() - headersIndex, sheet.getMaxColumns());
    var numColumns = dataRange.getEndColumn() - dataRange.getColumn() + 1;
    var headersRange = sheet.getRange(headersIndex, dataRange.getColumn(), 1, numColumns);
    var headers = headersRange.getValues()[0];
    return getObjects(dataRange.getValues(), normalizeHeaders(headers));
    }

    // For every row of data in data, generates an object that contains the data. Names of
    // object fields are defined in keys.
    // Arguments:
    // - data: JavaScript 2d array
    // - keys: Array of Strings that define the property names for the objects to create
    function getObjects(data, keys) {
    var objects = [];
    for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
    var cellData = data[i][j];
    if (isCellEmpty(cellData)) {
    continue;
    }
    object[keys[j]] = cellData;
    hasData = true;
    }
    if (hasData) {
    objects.push(object);
    }
    }
    return objects;
    }

    // Returns an Array of normalized Strings.
    // Empty Strings are returned for all Strings that could not be successfully normalized.
    // Arguments:
    // - headers: Array of Strings to normalize
    function normalizeHeaders(headers) {
    var keys = [];
    for (var i = 0; i "firstName"
    // "Market Cap (millions) -> "marketCapMillions
    // "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
    function normalizeHeader(header) {
    var key = "";
    var upperCase = false;
    for (var i = 0; i 0) {
    upperCase = true;
    continue;
    }
    if (!isAlnum(letter)) {
    continue;
    }
    if (key.length == 0 && isDigit(letter)) {
    continue; // first character must be a letter
    }
    if (upperCase) {
    upperCase = false;
    key += letter.toUpperCase();
    } else {
    key += letter.toLowerCase();
    }
    }
    return key;
    }

    // Returns true if the cell where cellData was read from is empty.
    // Arguments:
    // - cellData: string
    function isCellEmpty(cellData) {
    return typeof(cellData) == "string" && cellData == "";
    }

    // Returns true if the character char is alphabetical, false otherwise.
    function isAlnum(char) {
    return char >= 'A' && char = 'a' && char = '0' && char <= '9';
    }

    I love this. I will be using this for my University's resource scheduling.

    1. Post author

      @sharon - Thanks for sharing and I'm glad some of my code was reusable. Do you think you could make a copy of your spreadsheet available?

      With Google announcement of scripted 'on form submit' I'll be revisiting my code very soon ... stay tuned
      Martin

  14. Pingback:

  15. Pingback:

Comments are closed.