A few months back I pitched the idea of using Google forms for all the forms on the new website. Our current forms were created through a Joomla-specific add-on, and I'm not proficient enough in PHP or SQL to feel comfortable recreating them from scratch. Also, the forms on our current site turned into a pretty huge security risk as they aged, and I like the thought of using Google's servers to house the forms and resulting data. However, on their own, Google forms are pretty limited in their functionality, just dumping data into a Google spreadsheet document (which can be exported, but you still have to regularly log into your Google account to view the data.)
Forms do have the option of sending an email alert whenever someone fills out the form, but the alerting email doesn't include the actual form data, so you're still tied to constantly logging into that account to get the information. Enter:
Google Apps Script. With a little knowledge of JavaScript, you can use their library of classes and methods to add functionality to basic Google forms.
I started out just wanting to be able to receive an email when someone submitted a form, with all the responses included. For this I found a really nice tutorial from Amit Agarwal (
http://www.labnol.org/internet/google-docs-email-form/20884/). If that's all you need your form to do, great! You got it, dude.
I needed a few extras though. First, some of the forms need to go to multiple people. You can easily do this with a slightly more advanced version of the sendEmail method. (You can find documentation on the MailApp class and various iterations of the sendEmail method here:
https://developers.google.com/apps-script/reference/mail/mail-app. Also helpful, their Understanding Events cheat sheet:
https://developers.google.com/apps-script/understanding_events)
Essentially, you just need to find this line in the original code:
MailApp.sendEmail(email, subject, message);
and change it to:
MailApp.sendEmail(email, subject, message, {cc: email
of person you want to copy});
You can cc multiple people by just separating their email addresses with commas.
One of the librarians, however, wanted users to indicate what department they were affiliated with, and then have a copy of the form results go to the department liaison. This is where things start to get a little complicated, and it's helpful to know a little bit about programming languages. I wrote a simple switch statement (with some help from
Babs, of course, my go-to programming guru.)
var dept = e.values[array location
of dropdown].toString();
var contact = toString("xx");
switch (dept) {
case "dropdown value 1":
contact = "email address 1";
break;
case "dropdown value 2":
contact = "email address 2";
break;
default:
contact = "default email address";
}
The first line of code pulls whatever drop-down value the user selected (the associated Google spreadsheet stores these values as an array. 'e.values' accesses the values in this array. Position [0] of the array is the time-date stamp that gets put in automatically, so your array location is just the exact question number of the drop-down question.)
Your switch statement is then just comparing that value to values that you associate with email addresses, and then assigning the associated email address to the variable "contact", so now your method call looks like this:
MailApp.sendEmail(email, subject, message, {cc: contact});
Ok, if I haven't given you a headache yet, there's one more tweak you can do to increase the usability of the form submission email. Using 'e.values' again, you can pull the user's email address from their form submission, and set it as the reply-to on the resulting email. That way, if the person who gets the email has a question for the submitter, they can just hit reply (default reply-to is the gmail account that you're using to create the form.)
Again, since the time-date stamp is [0], you just need the question number where you ask for the user's email address, and now you've got:
var reply = e.values[array location of user’s
email].toString();
MailApp.sendEmail(email, subject, message, {cc:
contact, replyTo: reply});
You can check out the whole script, as I use it, here.
If you've done any Google form hacking, I'd love to hear about it in the comments. I've only just begun delving into the possibilities here!
5/23/14 - I just stumbled across
this post about using Google Forms for leaderboards/summer reading programs. It's also another good example of hacking Google Forms with formulas and scripts.
Academic Librarian. Author, website builder, lover of science, and superwholock fangirl. Cat mom, misanthropic humanist and inconsistent oxford comma user. Full of snark and boxed wine. More @
Posts
