Learn to Magically Send Emails from your Google Form Responses

Max Brawer
Jun 20, 2018 · 5 min read

I am always looking to get more and more users to tap into the power behind Google Drive for Enterprise AND to learn to code at the same time.

Back in the old days, we used to try to automate business process with VBA + Excel, which is no fun because VBA is a dead language. But here, in Google land, you’ll be learning some JavaScript and telling your friends you can code in no time!

This example came from a friend who wants to send form submissions automatically to specific email addresses and possibly put them in their own doc. Let’s get into what we need to do, step by step:


Step 1: Create an Apps Script File from your Form Response Spreadsheet

Welcome to your blank slate! Time to code.

Step 2: Capture the new data as it comes in with onFormSubmit

function onFormSubmit(e) {

}

You don’t have to name it this, but it will help you remember what it’s for! Next, click the little ‘stopwatc h’ icon to set up a trigger so that this runs automatically. You’ll have to give permissions to install this one:

You’ve now got a function that is prepared to run whenever a user submits a form response. If you’re new to coding, thisIsCalledCamelCase and is the convention for a function name. The (e) in the circle is shorthand for ‘event’ and is going to come from Google loaded up with information from the form (you’ll see soon).

Step 3: Organize the right data from the “event object”

Cool data you can get from the event object

You can see the money view: namedValues, that has everything you need! The way to read this: calling for e.namedValues will give you back the data you need for this project AND the names/labels. Try this to capture the form data in your script:

function onFormSubmit(e) {
var values = e.namedValues;
Logger.log(values);
}

The Logger is a helpful tool to record output for you that you can check to make sure your script works. If I run this script (with a new form submission) and then click “Cmd + Enter” to open my logs, I see:

Congrats on what may be your first working code! You now are good to go and have data in your wizarding hands.

Step 4: Turn it into something readable with HTML

function onFormSubmit(e) {
var values = e.namedValues;
for (Key in values) {
var label = Key;
var data = values[Key];
Logger.log(label + ": " + data);
};
}

You’re saying that for each header/Key I got back, let’s call the Key name a label and let’s call the corresponding response ‘data’. If I log that, and add a little : in the middle as above, here’s what I get:

Your log of each key, a colon and a space, then the value!

There are lots of ways to make this presentable in an email, but here is one common format. We will make an unordered list, which is <ul> in HTML, and make a list item (<li>) for each item in the list!

To do this, make a variable called htmlBody and set it to the <ul> tag, in quotes. Below, you’ll see how we will update the loop to add a new <li> tag around every key and value:

function onFormSubmit(e) {
var values = e.namedValues;
var htmlBody = '<ul>';
for (Key in values) {
var label = Key;
var data = values[Key];
htmlBody += '<li>' + label + ": " + data + '</li>';
};
htmlBody += '</ul>';
}

The += is a code way of saying “add on to the existing thing”. So we are tacking on a new list item with a label and data for each item. Then, at that last line, we add one closing tag </ul> to indicate that we are all done.

Step 5: Use the magic spell to email it anywhere

Can you believe that this sci-fi like power to send an email from code is only one line long? Pretty neat. And we can see exactly what we need to fill that line with from the above screenshot. A recipient, a subject line, a body — BUT LEAVE THIS BLANK ‘’ — and then the special options at the end.

Why no body? We are putting an htmlBody in, which counts as a special object and so we don’t need the standard string body. This is the complete code:

11 lines is not a lot for an automation project that will make your coworkers treasure you

You may have to click run (the play button) once here just to add permissions for Gmail. Then you’re good to go: submit a response and guess what!

!!!

And you’re DONE. The best part is, you can recycle this code endlessly and only change it as needed. Congrats on your first automation project in Apps Script~ Make your manager think you’re a magician for me!

UPDATE: WANT MORE? I added a part 2 to this story.

Max is Head of People Analytics at BuzzFeed and loves to write, share and consult on analytics work. Reach out: inquiries@maxbrawer.com

Max Brawer

Written by

Head of People Analytics @BuzzFeed