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…):

// add menu to Sheet
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Send Emails")
  .addItem("Send Email Batch","createEmail")

 * take the range of data in sheet
 * use it to build an HTML email body
function createEmail() {
  var thisWorkbook = SpreadsheetApp.getActiveSpreadsheet();
  var thisSheet = thisWorkbook.getSheetByName('Form Responses 1');

  // get the data range of the sheet
  var allRange = thisSheet.getDataRange();
  // get all the data in this range
  var allData = allRange.getValues();
  // get the header row
  var headers = allData.shift();
  // create header index map
  var headerIndexes = indexifyHeaders(headers);
  allData.forEach(function(row,i) {
    if (!row[headerIndexes["Status"]]) {
      var   htmlBody = 
        "Hi " + row[headerIndexes["What is your name?"]] +",

" + "Thanks for responding to my questionnaire!

" + "Your choice:

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

" + row[headerIndexes["Custom Reply"]] + "

" + "Have a great day.

" + "Thanks,
" + "Ben"; var timestamp = sendEmail(row[headerIndexes["Email Address"]],htmlBody); thisSheet.getRange(i + 2, headerIndexes["Status"] + 1).setValue(timestamp); } else { Logger.log("No email sent for this row: " + i + 1); } }); } /** * create index from column headings * @param {[object]} headers is an array of column headings * @return {{object}} object of column headings as key value pairs with index number */ function indexifyHeaders(headers) { var index = 0; return headers.reduce ( // callback function function(p,c) { //skip cols with blank headers if (c) { // can't have duplicate column names if (p.hasOwnProperty(c)) { throw new Error('duplicate column name: ' + c); } p = index; } index++; return p; }, {} // initial value for reduce function to use as first argument ); } /** * send email from GmailApp service * @param {string} recipient is the email address to send email to * @param {string} body is the html body of the email * @return {object} new date object to write into spreadsheet to confirm email sent */ function sendEmail(recipient,body) { GmailApp.sendEmail( recipient, "Thanks for responding to the questionnaire!", "", { htmlBody: body } ); return new Date(); }

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.


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.


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!

53 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.

      1. Hi Ben,
        Is there a way to change the email service to another app, say outlook?? I have a email account the at is not a gmail but does have the association with google apps so I a can use drive forms and sheets with this email but it does not have gmail services.

        Thanks for the help,

  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 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?
    Don Yerks

  5. 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.

  6. 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?

    1. Hey Paul,

      Somehow missed your comment! Try adding a check for a reply to line 31 and only send emails if there is one present:

      if (!row[headerIndexes["Status"]] && row[headerIndexes["Custom Reply"]] !== '')


  7. Hi, Nice script.
    Is it possible to send out the email automatically. At the moment the form is submitted and the field is updated in the Google sheet?
    Is there an add-on that can trigger it when it identifies an event?


  8. Thank you for the this script — I think I’ve got it set up correctly; however, I’ve just discovered that some students who submit comments don’t submit an email address (even though its collecting email addresses and is required, not sure how that’s happening). In which case, the script doesn’t run because there is no email address to use. Is there:
    1) A way to skip responding to students who didn’t include an email address and
    2) A way to simply use the active sheet rather than list a sheet by name (I’ll just keep adding tabs to a single spreadsheet and they will, therefore, have different names)?

    Many thanks for your help and for publishing this helpful script!

    1. Hi Sarah,

      Catching up on comments at last! For 1) you could try something like this, wrapping a check around the send email call. Try changing line 31:

      if (!row[headerIndexes["Status"]]) {

      to this:

      if (!row[headerIndexes["Status"]] && row[headerIndexes["Email Address"]] !== '') {

      This will only send emails to rows with an email address entered.

      For 2) change this:

      var thisSheet = thisWorkbook.getSheetByName('Form Responses 1');

      to this:

      var thisSheet = thisWorkbook.getActiveSheet();

      Hope that helps!


  9. Hi Ben

    Thank you for the great script.

    I have some hopefully simple questions.
    1) Is there possibly a way to leave out some of the headers in my final email if the individual did not select a response to that specific question (For the questions that is NOT required)? I do, however, want to include his response in my email if the individual selected one of the options. Currently, it leaves large unwanted spaces in my emails if there is no response to a question.

    2) Is there a way to change the style of some parts of the email to bold?

    Thank you once again.

    1. Hi Armand,

      1) You can create a variable outside of the HTML body to test if a header is present or not, and then only include if it’s there, e.g. something like this:

      if (!row[headerIndexes["Choose a number between 1 and 5?"]]) {
          var headerNumber = row[headerIndexes["Choose a number between 1 and 5?"]] + "< b r>< br>" ;
      } else {
          var headerNumber = "";

      and then inside the HTML email:

      "Your choice:

      " + headerNumber + row[headerIndexes["Custom Reply"]] + "

      " + "Have a great day.

      " +

      2) Use


      to add bold formatting. I think that should work.


  10. Hi Ben,
    Thanks for sharing the useful code.
    I got the following error, however, I gave it the permission. How to deal with it?

    This app isn’t verified
    This app hasn’t been verified by Google yet. Only proceed if you know and trust the developer.

    If you’re the developer, submit a verification request to remove this screen. Learn more


  11. hey Ben,
    i’m doing one project in that i need to send a email to each response personally from google sheet itself with button placing in each row. i’m unable to add button to each entry once they submit form. second, can i do same thing from my mail where i get response form mail and it’s content. such that from inbox i click on button and mail reply to go to participant about his/her submission. later i want to transfer google sheet data to new sheet at the same time to display it on web .

    can you suggest me script.

    thank you

  12. when i am trying to send the emails its showing error converting NaN to class, and instead of selecting onOpen when i selected create email it showed the same error: cannot convert NaN to class in line 43, what to do with this, plz help me

  13. This has been amazing to play with! Thank you!! Is there a way to have the custom reply sent to more than one email address? I have tried forwarding the sent mail, from the google account the custom reply comes from, but it doesn’t work. At least I haven’t figured out a way to make it work. Any suggestions or ideas?

  14. Hi Ben,
    This script is easy to follow. Thank you. But I am doing something wrong. I modified the script for my form, but, I keep getting undefined when I try to print my multiple choice response. I changed the header name to match. I can’t see what is wrong. Any ideas?

  15. Hi Ben,

    Thank you for this code.

    Upon running it, the following error popped up:

    “Cannot call SpreadsheetApp.getUi() from this context. (line 3, file “Code”)Dismiss”

    What do I need to adjust ?

  16. Hello, i want to set up an auto response for emails in my google sheets but i do not want to use my gmail account for it. i want to know if i can use my office mail **** to reply responses to my form/survey using google sheets.

  17. Hi Ben,
    My question is like this:
    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?
    MJ Dehghan

  18. Please I have a customized email I will like to send automatically to the response email, how do I key in the content

  19. Thanks for sharing. I tried this on my gmail account for work and it keeps showing an error on Line 21. It says “TypeError: Cannot call method “getDataRange” of null. (line 21, file “Code”)”
    Anyone know what to do?

  20. If I filter my sheet, will the script run on the data that is not shown, or will it continue to run on ALL data?

  21. I’m getting an error for line 34 that is an unterminated string, here is what I have exactly-
    “Hi ” + row[headerIndexes[“Name”]] + “,

    I have tried removing things or putting spaces in but get the error every time. Any help would be appreciated.

    1. Hi Tom,

      You need one more quotation mark on the end of your line, i.e.

      "Hi " + row[headerIndexes["Name"]] + "," +

      I’ve also added a last plus symbol, which assumes that your string continues on the following line.

      I think it’s been caused by the browser splitting the line in two in my blog post, which is causing the confusion. It might be easier to see and understand with the code on github here:

      Hope this helps!


  22. Thank you for your wonderful demonstrate on the captioned subject.
    I want to know whether there is any opportunity to send the e-mail with customized data automatically?? (e-mail will be sent whenever the response are completed by the participant without any clicking)

  23. Hello Ben,
    Thanks for the code. I am not familiar with scripts!
    I changed those name based on my data thought the code but I have a problem as there is not any email sent after running the code!
    I did not get any error as well.
    Do you have any idea what is that for?

  24. Hi Ben,
    I am looking to send personal/individual responses to students based on feedback they’ve provided me. I collect this feedback in google forms, so I would really love to respond in google sheets. I tried this script but it doesn’t work for what I’m trying to do. I’ve also tried getting my IT person in district to help, but she hasn’t yet been able to figure out what I’m asking for. I am not at all familiar with scripts… Can you help?

  25. Hi Ben,
    Your post has been a great help to me but i need some help regarding the coding.
    How do i combine some data and make it into a sentence?
    For example, i want to combine the following date and make it into one sentence only
    “Leave Starts from” +
    row[headerIndexes[“Start Date”]] + “” +
    “Leave Ends at” +
    row[headerIndexes[“End Date”]] + “” +

Leave a Reply

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