Utilising Google Docs to email results

Within our Trust we can take bookings for courses from our staff, from agency staff who are working within the Trust as well as staff from external organisations (such as other Trusts, Partner organisations etc). For our own staff, we have a process for booking courses which is driven by Self-Service functionality. This allows our staff to book their own courses, receive instant confirmation of bookings, and control their own education bookings. However, as this system is driven by our HR system, it doesn’t work for anyone who doesn’t work directly for us (agency staff, external staff etc.)

In the past (before we had the VLE) – all course bookings were made via a booking form. It was clear we would need to continue an element of this functionality for people who did not have self-service functionality. As we were moving away from our previous website, the forms we had previously used within the content management system were no longer a viable option (it seems pointless to maintain a presence in a CMS purely for a form).

Looking into options, one which quickly came to light was to utilise the functionality of Google Docs. Google Docs allows you to easily create a form to collect data, which in turn is stored nicely in a spreadsheet within your Google account. Whilst the form design process was a breeze, I was then left with the issue about accessing the results. I didn’t want to introduce a process where our staff would have to actively go and look for new bookings on the spreadsheet (it doesn’t make sense to do this – if I was doing this, they may as well be checking a postbox to see if any snail-mail has arrived!) – so we needed an automated solution.

Resorting again to use of Google (this time in it’s search engine form) – I was led to a number of forum and blog posts which gave me the following code:

// Remember to replace XYZ with your own email address
var email = “XYZ”;
// Optional but change the following variable
// to have a custom subject for Google Docs emails
var subject = “Email Subject”;
// The variable e holds all the form values in an array.
// Loop through the array and append values to the body.
var s = SpreadsheetApp.getActiveSheet();
var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
var message = “”;
// Credit to Henrique Abreu for fixing the sort order
for(var i in headers)
message += headers[i] + ‘ = ‘+ e.namedValues[headers[i]].toString() + “\n\n”;
// This is the MailApp service of Google Apps Script
// that sends the email. You can also use GmailApp here.
MailApp.sendEmail(email, subject, message);
// Watch the following video for details
// http://youtu.be/z6klwUxRwQI
// By Amit Agarwal – www.labnol.org

Adding this code into the events handler means that every time a form is submitted, it now sends an email (using the GoogleMail account) to our email address. This solution seemed very good, until I realised that the completed information was all being stored on the server in the spreadsheet. Uncomfortable with the prospect of a spreadsheet being in existence which contained the details of all our course bookings, I returned to Google, this time ending up on the official help pages, where I found information of all the tasks I could undertake using the event handler. This revealed the following code, which I was able to add to my original code:

// The code below will delete rows 2 and 3 in sheet 0
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
sheet.deleteRows(2, 2);

Now, when a form is submitted, it is emailed through to our account, before being deleted from the spreadsheet ensuring I don’t have a spreadsheet of data building up and laying around!