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 Workspace), 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 a course.

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 Google Apps Script 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")
  .addToUi();
}

/**
 * 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?"]] +",<br><br>" +
          "Thanks for responding to my questionnaire!<br><br>" +
            "<em>Your choice:<br><br>" +
              row[headerIndexes["Choose a number between 1 and 5?"]] + "</em><br><br>" +
                row[headerIndexes["Custom Reply"]] + "<br><br>" + 
                  "Have a great day.<br><br>" +
                    "Thanks,<br>" +
                      "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[c] = 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.

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!

94 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,
        Daniel

  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?

      2. One of the Column Headers reads: State the Conflict in “The Boar Hunt.”

        It’s the question I asked students in the Google Form, before even thinking about getting into AppsScript. But the editor is giving me an error due to the quotation marks around the story title (The Boar Hunt) in this section of code:

        row[headerIndexes[“State the Conflict in “The Boar Hunt.””]] + “” +

        I get that the quotation marks within the quotation marks are the syntax issue, but how can I fix it if the quotation marks are part of the header already?

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

      1. Hi Ben, Need to add one more column header in the page. “Mobile number”. can u please help me in updating the code??

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

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

  8. 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"]] !== '')

      Ben

  9. Hi, I have try the script but end up I facing the error appear in line 87.
    Please guide.

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

    Thanks

  11. 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!

      Ben

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

      Ben

  15. Is there any way to be sent an email any time someone submits something into your google form?

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

    Advanced

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

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

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

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

  22. I keep getting this error. Any idea what might be happening?
    ReferenceError: “timestamp” is not defined

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

  25. 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 ****@kanma.com to reply responses to my form/survey using google sheets.

    1. Is there any way to send response on form while submitting data in real time?

  26. 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?
    Thanks,
    MJ Dehghan
    mjda1366@gmail.com

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

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

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

  32. 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: https://github.com/benlcollins/apps_script/blob/master/gmailScripts/surveyReplyEmail_template.gs

      Hope this helps!

      Ben

  33. 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)

  34. Pingback: How I Email: Ben Collins, Google Sheets Developer & Data Analytics Instructor - The Gmail Genius
  35. Pingback: Gmail Mail Merge For A Specific Label With Apps Script - Ben Collins

    1. Hi Alan, and thanks a lot to Ben for sharing his knowledge with us.
      Change this and it’ll work, I’ve get the same issue
      Line 42
      Before
      var timestamp = sendEmail(row[headerIndexes[“Email address”]],htmlBody);
      after
      var timestamp = sendEmail(row[headerIndexes[“Email Address”]],htmlBody);
      “Email address” to “Email Address” (make the letter “a” of “address” capital letter

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

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

  39. 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”]] + “” +

  42. Exception: The starting column of the range is too small.

    Hi! I keep seeing this message every time i tried to send an email.Please help. Thank you.

  43. Hi
    I’m using it for my work with custom domain Gmail address , i keep getting error shown below ,i checked all header names but still can’t get it to resolve

    SyntaxError: Invalid or unexpected token (line 35, file “Code.gs”)Dismis

    1. Hey I had the same error, and I know NOTHING about coding, so I went to Ben’s source code post:https://github.com/benlcollins/apps_script/blob/master/gmailScripts/surveyReplyEmail_template.gs

      This little script is AMAZING once you get the hang of it. Pro tip: either attach this script to a test Google Form -OR- put an “x” or something in the “status” column of EXISTING responses while you test out the script. If you don’t do that, you’ll sent test emails to everyone on the list. I may have made that mistake.

      Copying and pasting that code worked PERFECTLY. Be sure to change all the “column” references AND the subject line of the email that will be generated. Do lots of testing, but then it will work for you. It’s really simple. Also, if you delete one of the “Status” entries for a particular response, you can send another email the next time you run the script. Definitely worth the effort. Good luck!

  44. // add menu to Sheet
    function onOpen() {
    var ui = SpreadsheetApp.getUi();

    ui.createMenu(“Send Emails”)
    .addItem(“Send Email Batch”,”createEmail”)
    .addToUi();
    }

    /**
    * 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[“Requester First & Last Name “]] +”,

    ” +
    “Thanks for submitting your work request!

    ” +
    “Your work request:

    ” +
    row[headerIndexes[“Please Provide Brief Description Of Work Request”]] + ”

    ” +
    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 submitting your work request!”,
    “”,
    {
    htmlBody: body
    }
    );

    return new Date();
    }

    Keep getting
    SyntaxError: Invalid or unexpected token (line 33, file “Code.gs”)Dismiss

  45. Hi Ben,

    I was wondering if there was a way to flag certain responses?
    For example we are using google forms for a daily check in due to COVID, if someone responds yes to a question that will then ask them to exit and contact their supervisor, is there a way to be notified right away? Thank you

  46. Hi, I was wondering if there’s a way we can add a fixed email address in the cc field to the emails that are going out?!

    1. i figured this, haha. in 85 to 97 modify the HTML body a bit and add the following :

      htmlBody: body,
      cc: ’emailaddress@xxx.com’

  47. Hi Ben,
    Thank you for your very useful example script. I have apply to other document but, I am facing problem of sending mail.

    function onOpen(){
    var ui =SpreadsheetApp.getUi();
    ui.createMenu(“Send Email”)
    .addItem(“Send email”,”createEmail”)
    .addToUi();

    }

    //Code for Function createEmail

    function createEmail(){
    var thisWorkbook = SpreadsheetApp.getActiveSpreadsheet();
    var thisSheet = thisWorkbook.getSheetByName(“Form Responses 1”);

    //get data of the worksheet

    var allRange = thisSheet.getDataRange();

    //get data in this Range

    var allData = allRange.getValues();

    //get all header row

    var headers = allData.shift();

    // create headers index map

    var headerIndexes = indexifyHeaders(headers);

    allData.forEach (function (row,i) {
    if(!row[headerIndexes[“Approval”]] && row[headerIndexes[“Reply Date”]] !==”){

    var htmlbody =

    “We would like to inform you that your booking has been” + row[headerIndexes[“Approval”]]+ “” +
    “The driver name” + row[headerIndexes[“Pick up station /​ កន្លែងទទួល”]] + “” + row[headerIndexes[“Pick up time? / ម៉ោងទៅទទួល?”]];

    var timestamp = sendEmail(row[headerIndexes[“Email Address”]],htmlbody);

    thisSheet.getRange(i + 2,headerIndexes[“Reply Date”] + 1).setValue(timestamp);

    }
    else {
    Logger.log(“No email address found” + 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[c] = 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,
    “Transportation Booking”,
    “”,
    {
    htmlBody: body
    }
    );

    return new Date();
    }

    it’s not working because it could not find the recipient,

  48. Exception: The parameters (null,String) don’t match the method signature for GmailApp.sendEmail.Details

    The above error is getting triggered everytime i used to run.
    Column headers are Email, ACTION.
    Please help me to send emails..

  49. Hi Ben, Managed to do all of your instructions and tweaked the code to fit my data and column headers but when I try and follow the Custom Menu set up it just fails to work..? Asks me to log in and verify the app / script, warns me it’s not a verified app but you can force past that but when complete no menu or button appears in the spreadsheet..?? Help please.??

  50. Hi Ben,

    Is there a way to configure the code to accept duplicate column names ?

    My form has different sections based on answers to the initial question. There are bound to be duplicates.

  51. Getting this error:

    Exception: Failed to send email: no recipient (line 91, file “Code”)

    Please help.

  54. When I first started using the code it would only send my reply if there was a reply in the column. If I had more to reply to it wouldn’t send anything to them.

    Now, when I put a reply for one person, it sends an email even to those I haven’t put a reply into yet.

    How do I fix this so I can just send the email when I am done with the reply one at a time?

  57. I’ve created a survey form in Google Forms and I want to be able to selectively response to specific forms, not all of them. I’m a newbie to the Ben Collins site so I’m not sure if I need to get a customization of the published AppScript or if I can get advice on how to modify it myself.

  58. Hi Ben Are There any way to automatics publish google forms response to blog or any social media apps or a web site or any other options? Thanks

  59. Getting this error, that others have seen as well.
    Exception: Failed to send email: no recipient

    I have added log statements throughout the code to find where it is failing, but with no luck so far, hoping someone can help.

    allData and headers get populated with the google sheet data and the log statements shows this.

    But each time I get to the code to check “status” and populate the email, I get “undefined” for these statements – row[headerIndexes[“Status”]], row[headerIndexes[“Email”]], and my other columns.

    I even went as far as to run Ben’s original code with a simple form with just “Email” and “Name”, with the addition of “Status” in the google sheet. I get the same issue.

    Any help would be appreciated, happy to share code as well.

    1. I get the same error “Exception: Failed to send email: no recipient”. However, the email addresses are there and are automatically generated. What else could it be?

  60. Very informative post this is. I’m likewise searching for an approach to do something comparative identified with COVID-19 screening questions. Have you mastered anything yet?

  61. Hi Ben

    This is great, I want to use it to let my team know a task is completed – can the script work on a task by task basis – I.e when I click a tick box it send the mail to say the task is complete

  63. Hi Ben
    I’m not sure what I’m doing wrong. I copied and pasted your script, added my own info and when I go to send the email, I keep getting a message saying that it failed to send: no recipient. Can you help me?

    1. Hi Carmen,

      You have to have emails in a column with the title “Email Address”, otherwise it won’t find the emails in your Sheet. Does that help?

      Cheers,
      Ben

      1. I am having this exact same issue.

        5:01:12 PM Error
        Exception: Failed to send email: no recipient
        sendEmail @ 2.gs:87

        Please help me understand where recipient is set to the Email Address column?

        Is it var timestamp = sendEmail(row[headerIndexes[“Email Address”]],htmlBody); ?

    2. I think the line 70 should be p[c] = index;

      as written by Pheary

      otherwise indexifyHeaders doesn’t return an object it will just return the index of the last column in your data

  64. hello. I’m trying to use this and am getting the same “Failed to send email: no recipient” I have emails in the email address column, but nothing is sending. Thoughts?

  65. It works perfectly fine, thank you, much much handy and helpful. However, I have a question: the email is still being sent even when the “custom reply” column is left empty, using the default email from the code. Is there a way to modify the code so that the email is not sent to recipients whose “custom reply” column is empty?

    I’d be gratful if you could let me know at the earliest. URGENT!

    THANK YOU!

  66. Hi, Ben… I’m very late to the game (by years, let’s be honest), but I happened across your post…basically I’m using a form to get maintenance requests from users of our facility. They fill out the form, the spreadsheet populates, then I fix what they need fixing. I then log what I did and when I did it. I would like MY entry when I DO the work to generate an e-mail to the original submitter saying that they submitted blank request on blank day; it was completed by blank person on blank day, and here’s what I did (which comes from the comment I’ve entered on the spreadsheet once the work was done. How can I accomplish this?

Comments are closed.