Home » Web Development » Devops » Use a Custom Script to Automatically Email the Submission Contents of a Google Form
Use a Custom Script to Automatically Email the Submission Contents of a Google Form

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.

  1. 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.
  2. 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.

  1. Replace “[email protected]” 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.
  2. You can change the subject in the sample code to match whatever you want the subject of your mail to be
  3. 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.
  4. 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:

blog-form1
blog-form2

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:

blog-ticket

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.

Advertisement

Net Tuts

Advertisement

468x60_makemoney

flattr this!

About snipe

I’m a tech geek/dev/infosec-nerd/scuba diver/blacksmith/sword-fighter/crime fighter/ENTP/warcrafter/activist, and the former CTO and CSO at a business innovation agency in New York City. Tweet at me @snipeyhead or read more...
  • WebWoman

    Thank you for this! It works great and doesn’t require a boat load of permissions to run. Great Job!

  • Anyoms

    Am having problems with d script

    ReferenceError: “getRange” is not defined. (line 7, file “Code”

  • Irfan

    Same here, please advise OP

    • gz

      Same here :/

  • Kyle

    function sendFormByEmail(e)

    {

    // 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 = “New Hire Form Submitted”;

    // 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 = ” your message here “;

    // Credit to Henrique Abreu for fixing the sort order

    for(var i in headers)

    message += headers[i] + ‘ = ‘+ e.namedValues[headers[i]].toString() + “nn”;

    // 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 – http://www.labnol.org

    }

    This is the code I used. No errors.

  • Rashesh Kanbi

    I’m using this and it works great, BUT, the email goes into All Mail rather than Inbox. Sometimes it gets missed.

    Anyone kind enough to let me know a fix for this?

  • Derek Dawes

    Its because your getting a non defined error. Its because your running the script in the editor and “e” hasn’t been defined yet. Ignore it and there is no reason to run the script in the editor fyi.

    • http://www.snipe.net snipe

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