Use a Custom Script to Automatically Email the Submission Contents of a Google Form

My company is in the awkward phase of small-transitioning-to-medium-sized. This means we’re small enough to not need (or want to spend money on) huge enterprise-class systems, but big enough to need to automate some stuff.

I’m working on my own small/med biz IT management system, but that’s not quite done yet. In the meantime, we use Google Doc forms to offset some of those needs. Using Google Doc forms, I can set up the form, enforce required fields (this part is the real key), and have the data submitted to a spreadsheet which I can then do all kinds of things with.

I have a New Hire form, for example. This form simply consists of about 10 fields that my team needs in order to set up a new user. Basic stuff like the new hire’s name, desired corporate email address, where they’re going to sit, who they report to, whether we’re ordering them a new laptop or they get an older one, and any special software that my team needs to install on their machine (Photoshop for designers, Axure for UX, etc).

This form doesn’t contain sensitive personal or corporate information, but moving this to Google Docs from the previous “format” of a freeform email has improved efficiency tenfold, since people are human, and they would forget to include information in a freeform email. Using Google Doc forms, they can’t actually submit the form unless they have all of the information I require.

So, great, HR fills in the form, I get notified via email that the form has been updated — and then what? I’d have to copy+paste the new entry into a helpdesk ticket. Ugh. This is slow, clunky, error-prone, and hardly the best use of my time.

What I wanted was to be able to generate a helpdesk ticket using the newly entered content itself, not just the notification that the form was updated. Thanks to a great post by Amit Agarwal, I had a really good start.

Create a new form in Google Drive (or use any of your existing forms). Open the Spreadsheet that stores the results for your form submissions.

Go to Tools – > Script Editor and choose “Blank Project.” Paste the code in the gist below over the starter code they provide, and save the project.

Replace “you@example.com” in the code with the email address you want to send the new form submission content to. For me, this is usually my helpdesk address so that it will automatically create a ticket using the new form’s contents.

You can change the subject in the sample code to match whatever you want the subject of your mail to be

From the Resources menu in the Script Editor, choose “Current Script’s Triggers” and set up a new trigger. Replace “On Open” with “On Form Submit” and save the trigger.

The script will require you to authorize Google Docs to access your Gmail account (for sending the email). Authorize it, and you should be all set.

Now just go to your live form and test it out. I’ve included some screenshots below to show how I’ve got it set up. Below is the majority of my new hire form:

When HR fills in this form, it adds the record to the spreadsheet, but now also emails a ticket to the helpdesk, so my IT guys know they have a new hire coming, and can make sure we have the hardware and time resources allocated to get a new workstation deployed in time. The ticket looks something like this in our helpdesk:

While I wouldn’t recommend using Google Docs/Forms to store any sensitive information, a little creativity can make them really useful for streamlining some tedious and error-prone operational tasks without spending a ton on some horrible, unwieldy enterprise system.

I’ve set up forms that generate emails and/or tickets for new hires, employee terminations (voluntary or otherwise), software license requests, and so on.

It’s far from perfect, but it’s vastly better than a free-form email process that ends up in a lot of back and forth because the data being sent through isn’t normalized or validated.

About the author

snipe

I’m a tech geek/dev/infosec-nerd/scuba diver/blacksmith/sword-fighter/crime fighter/ENTP/warcrafter/activist. I run Grokability, Inc, and run several open source projects, including Snipe-IT Asset Management. Tweet at me @snipeyhead or read more…

Why can’t you google for the images? Or, just go to the fitbit website, where they have lots of info on both.

Valérie

The answers to the form that I get by email show up in a random order. How can I fix this?

Brook

Thank you! This helped so much and works perfectly!

ungullible

This script is great – Thanks!! However, I am experiencing one occasional problem. About 10% of the time, the email never gets sent. Is anyone else experiencing this? Is it a timing issue where the script fires before the form data is available to it (due to internet latency)?

ungullible

So yes, I think this is a latency issue. I added a sleep command to the beginning of the script, and this problem has not happened since.

I imagine you’d just need to make an array (instead of a single variable) for email addresses and then loop through them, calling `MailApp.sendEmail(email[$i], subject, message);` or something similar inside the loop.

Edo Plantinga

Alternatively, you can just use a comma seperated string: MailApp.sendEmail(“mail1@example.com,mail2@example.com”, subject, message)

Matt Purvis

This is literally my first time using a script, and first time since ’96 or so using HTML. It works great for me. The only thing I’m not sure of: I would like to place breaks between the different information(like it displays on your ticket) right now the message looks like this:

Search

About Me

I’m a tech geek/dev/infosec-nerd/scuba diver/blacksmith/sword-fighter/crime fighter/ENTP/warcrafter/activist. I run Grokability, Inc, and run several open source projects, including Snipe-IT Asset Management. Tweet at me @snipeyhead or read more…