How to reply to Google Forms survey responses quickly with Sheets & Apps Script

Google Forms survey email tool system
1. User submits Google Forms survey
2. Response logged in Google Sheet
3. Google Apps Script parses responses and sends emails
4. Original user receives reply!

You’re happy!

You sent out a Google Forms survey to your customers and received hundreds of responses.

They’re sitting pretty in a Google Sheet but now you’re wondering how you can possibly reply to all those people to say thank you.

Manually composing a new email for each person in turn will take forever. It’s not an efficient use of your time.

You could use an ESP like Mailchimp to send a bulk “Thank You” message, but it won’t be personal. It’ll be a generic, bland email and nobody likes that. It won’t engage your customers and you’ll be missing an opportunity to start a genuine conversation and reply to any feedback from the survey.

Thankfully, there is another way.

Of course there is, otherwise why would I be writing this tutorial! 😉

By adding a reply column to your Google Sheet, next to the Google Forms survey responses, you can efficiently compose a personal response to every single survey respondent.

Then, using Google Apps Script (a Javascript-based language to extend Google Apps, start here if you’re new), you can construct an email programmatically for each person, and send out the responses in bulk directly from your Google Sheet.

Example Use Case

I recently sent a short questionnaire to my list, asking for volunteers to test out my new course, Data Cleaning and Pivot Tables in Google Sheets, which is launching this summer.

I was blown away by the interest level, with over 270 responses. I wanted to email everyone individually to say thank you for expressing their interest and sharing their feedback.

I needed a smart way to do that and the end result was this tool, which I wanted to share with y’all.

Here’s an animated GIF of a split screen, showing the Google Forms survey being submitted and the response Sheet receiving the data:

Google Forms Survey signup

And here’s an animated GIF of a split screen, showing the email tool automatically sending 3 emails from my Google Sheet to my Outlook email address:

Send Email Survey Sheet Tool

As you can see in the GIF, the tool adds a date to the final “Status” column of my Google Sheet, indicating that the reply email has been sent.

So how do we build this?

Step 1: Create your Google Forms survey

This step is pretty straightforward.

Create your survey with Google Forms (here’s the quick demo form I built for this tutorial).

Be sure to ask for a name and to toggle the “Collect Emails” option in the Settings menu:

Google Form collect email

Here’s a shot of the finished demo form:

Finished Google Form

Once you’ve created your Survey form, submit it at least once so you have a response showing.

Step 2: Set up the responses Sheet

Click the Responses tab (1) and then click Create Spreadsheet (2):

Google Form responses

Next, click create a new spreadsheet, which will be created in the same Drive folder as your Form:

Google Forms create spreadsheet

Find this new Sheet and open it. It’ll look something like this:

Google Form Sheet

Insert 2 columns to the right of your last Form Response data column.

In this example I want to add two new, blank columns to the right of Column D. Do this by highlighting column D (click the D column heading), right clicking, and choosing Insert 1 Right twice.

The first new column, column E, I call “Custom Reply” and the second, column F, I call “Status”, so that my Sheet now looks like this:Responses Sheet

I colored the two new columns as well, to distinguish them from the Form response data.

That’s it with this Sheet. Open the Script Editor (under Tools menu) ready to add the code…

Step 3: Add the Apps Script code

Before committing pen to paper, or lines of code to git, let’s think through what this program needs to do:

  1. Identify the data in our Google Forms survey responses Sheet
  2. Parse the data to extract the response and respondents name
  3. Parse the Sheet to extract all my custom replies for each respondent
  4. Decide if an email should be sent (has a response already been sent?)
  5. Create an email containing respondent’s name, their feedback and my custom reply
  6. Send the emails

The code:

Here’s the code to paste into the Script Editor of your Sheet (Tools > Script editor…):

The code is here on GitHub.

This code is specific to my example, so you’ll need to adjust the row[headerIndexes[…]] references:

row[headerIndexes["Choose a number between 1 and 5?"]]

to match the column headings in your responses Sheet.

Briefly, this is how the program works:

Lines 2 – 8 add the custom menu to our Google Sheet, so we can run the program from there.

Lines 15 – 28 get all the response data out of the sheet, called Form Responses 1, and create an index of the column headers.

Lines 30 – 48 are the meat of the program, where the script loops over each row of data with the forEach method, checks whether an email has been sent (line 31) and if not, creates an email (lines 32 – 40) with a fixed opening and closing lines and the custom message in between, sends it (line 42) and prints out a date in the status column (line 43).

Lines 57 – 77 is the function to create an index of headers from our data.

Lines 85 – 97 is the function we call to actually send the email using GmailApp service.

Note:

Apps Script quotas will permit you to send up 100 emails/day on the free consumer plan. If you have more respondents than that in your Sheet, the program will stop where it is, and give you the following error message:

email apps script quota limit

You’ll have to wait 24 hours before you can send your next batch of emails, the program picking up where you left off.

You can also upgrade to G Suite for Work and your email Apps Script quota rises to 1,500 emails/day.

In use

In your Script Editor window, select and run the onOpen() function:

Run > onOpen

This will add a menu to your Sheet tab, so that you can run your email script from your Sheet.

Next, write all your custom replies in the cells adjacent to the feedback for each row:

Custom replies in Sheet

When you’re ready to go, use the new custom menu to run the email function.

Boom!

If it works, it should insert a date stamp into that final status column to indicate when the email was sent.

Check your sent mail folder of your Gmail account and you should see a bunch of sent messages!

If you send an initial test email to yourself (highly recommended), then it should look something like this:

Email output

Questions? Comments? Improvements? Let me know in the comments below!

14 thoughts on “How to reply to Google Forms survey responses quickly with Sheets & Apps Script”

  1. Does this script work inside an EDU domain so emails can be sent to parents outside of the domain?

    1. I believe so. Provided each respondent submits a valid email address, then the script will just attempt to send the reply email to that address, regardless of it’s domain.

  2. There are lots sheet or form add-ons which send emails from form responses which are very simple to use. You can easily send different reply depending on form responses

    1. Yes, absolutely! However, there are lots of benefits to creating your own script, if you have the inclination, both from a learning standpoint and also because you have complete control to customize itt to be exactly the way you want and easily make changes, for example adding a Slack integration. The tool I used had different replies for different respondent groups, and this code is here on GitHub.

      Any particular add-on you recommend or use?

  3. Hi Ben,

    Whoa. I just stumbled upon your site into this article a couple days ago. This is AMAZING. I’ve just applied this to automate notification emails referencing lookups for a certain status. You are a magician, thanks so much!!

  4. Hi, he will good to do it.. But on the smartphone.. SMS. Twillio site can do that but from my country it is expensive (0.20€) each message… If someone Knows a better way…

  5. Hi Ben,
    I love this coding project :-). I am new to coding. Is there a quick way to change the code so it goes out as soon as the Google Form “Submit” button is clicked? If yes, can you please show me the code so I could copy / paste it?
    Thanks,
    Don Yerks
    dyerks@mersnj.us

  6. Is there a way that the email can be obtained if the user is logged in to gmail account?
    Say only few known users are to fill the survey by calling others.
    What I need to find out is : who filled each record.

  7. Ben, your script is nearly perfect for what I need! I’m new at coding so I could really use your help. I need for the code to send out emails only to the people who have comments (replys) added into the reply column instead of the whole list of submitters (more of a student scenario)…can you assist?

Leave a Reply

Your email address will not be published. Required fields are marked *