How to send a confirmation email with Google Forms

In this short tech tip, we are going to look at how to make a Google Forms send a confirmation email to the user each time they submit a form. This is just a basic template for how to make this happen, so it is worth mentioning that there are plenty of other ways to accomplish this one task and equally as many different things we could have this form do.

In the following steps, I am just going to give a brief overview of the different steps, so make sure to watch the video if you want to see HOW to complete this task.




  1. Create a Google Form that accepts some form of user input and create an attached Google Sheet to store the form submissions. Your input fields can really be any of the options that Google Forms gives you. My advice is to think about how best to eliminate confusion for your user. This will ensure that they don’t send you bogus data, or something other than what you asked for.

  2. Once you have created the form, open up the corresponding spreadsheet. Google Sheets use zero indexing, meaning that the leftmost column is column ‘0’ and the column directly to the right of column ‘0’ is column ‘1’ and so on. It isn’t a particularly difficult concept to grasp, but it will be important when we start to pull data from the spreadsheet.

  3. Go to the ‘Tools’ menu in the spreadsheet and open up the ‘script editor.’ Once the script editor window opens, create a blank project. You should see the beginnings of a function that looks something like this: function myFunction (){}

    1. This first thing we are going to do is pass this function an event parameter by placing a lowercase ‘e’ in between the parentheses: (e); this event holds all of the spreadsheet data that we are going to pull from.

    2. Next, in between the curly braces we are going to define some variables to store some of the specific values we need from the spreadsheet. We are going to use ‘e.values[1]’ to pull in the data that the user submitted to the first column, which in the example video would correspond to ‘Name’ (remember zero indexing here). We will assign ‘e.values[1]’ to a variable name appropriately ‘Name’: var name = e.values[1];

    3. Complete this same step to pull in the other data you want and also create variables for the body of the message and the subject line of the email the script will send.

    4. Once you have defined all of the variable you want to be included in the email, you are going to call the Mail.App method and pass it three arguments, recipient, subject, and body.

    5. The last step is setting a trigger to run the script you just created. In the ‘Resources’ menu, select the ‘current project’s triggers.’ You should see a menu that says there are no triggers for this project, so create a new trigger. We are going to want to create a trigger that runs ‘myFunction’ to process the spreadsheet event ‘on form submit,’ which is the far right drop down menu. Now, any time a user submits a form , this script should run. You will have to authorize Google to access the spreadsheets and you Gmail account to make this work.

  4. The last step involves submitting some data to the form to perform a quick test.

If you have any questions about how do some of the programming needed for this example, take a look at some of the Javascript tutorials located here at CodeAcademy or some of the tutorials on Google App Script located here.

Here is the code for the script, but note that this will change based on your spreadsheet:


function myFunction(e){
var userName = e.values[1];
var userEmail = e.values[2];
var date = e.values[3];
var subject = "Form Submitted";
var message = "Thank you, " + userName + " for choosing " +date;
MailApp.sendEmail (userEmail, subject, message);}

 

196 thoughts on “How to send a confirmation email with Google Forms”

  1. Stacey says:

    Hi Jeff

    The code for the script and all of the information has been incredibly helpful. Thank you. Is there any way to format the text in the message so it does not all run together?

    Thanks
    Stacey

    1. admin says:

      Hi Stacey,

      There is a new line character (n) that you can use to separate lines of the email message. You can look at some examples here: http://stackoverflow.com/questions/16208927/in-google-apps-script-how-can-i-preserve-newlines-from-user-input

      JE

      1. Giovannino says:

        Hi,
        thanks for code’s lines.
        I implemented the code but all e-mail notifications instead to go to user that have sended the Subscription form arrive to me that I’m the creator.
        How can I fix this problem ?
        Bye

        1. admin says:

          You probably just got the send to and send from email mixed up in the code. Can you post your code here so that I can take a look?

          1. Rick Reiman says:

            I have tried the script as follows but the debugger says that the second line is not defined. I wonder what is wrong. The script does not send the email out.

            function myFunction(e){
            var userName = e.values[1];
            var userEmail = e.values[6];
            var CreditHoursNow = e.values[3];
            var QualityPointSum = e.values[4];
            var AverageCreditHours = e.values[5];
            var TotalQualityPointsTimesCreditHours = e.values[7];
            var EstimatedGPA = e.values[8];
            var subject = “Form Submitted” ;
            var message = “Thank you, ” + userName + “for requesting this valuable information ” ;

            MailApp.sendEmail(userName, userEmail, CreditHoursNow, QualityPointSum, AverageCreditHours, TotalQualityPointsTimesCreditHours, EstimatedGPA, subject, message);

            }

            It gets hung up in line 2. Thanks for any feedback that you wish to give.

          2. admin says:

            This is a pretty common error, so I’m sure it is addressed 10+ time in the comments on this post. I’ve been meaning to publish a post dedicated to this error, so take a look at that and comment back if you have questions: http://www.jeffreyeverhart.com/2016/08/06/fix-typeerror-cannot-read-property-values-undefined/

            There are some other things in here that you need to look at though. When you call sendEmail, it is only expecting three arguments (userEmail, subject, message), so you will need to template out the message for all of the other variables the same way you do with userName. See an example here: https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String).

            Other than that, it looks good. Let me know if you need me to take another look.

  2. Armaan says:

    Thanks for the script. Its really very useful.

    Can i format the content of the mail to apply colors, bold etc? Ex I would like the score to be in bold and red. Is this possible?

    Also is there any way that we can insert an image in the mail?

    Thanks!

    1. admin says:

      Yes, but you will need to format the message as HTML. Check out this link here and look for the example that contains a parameter in the MailApp called htmlBody: https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(Object)

  3. JP says:

    Great tutorial! I was looking for something like this. One thing I still can’t figure out though. Is there a way to include data in the email message that comes from cells outside the range where the submission data is stored?

    You use columns A through D in your example. What if you had an array formula in E1 that calculated a value based on the submitted values (like grading a quiz that the form was used for). I’ve tried adding a new variable using-
    var score = e.values[4];
    but the result in the email says undefined. It seems like the script is executing before the formula can update. I also tried adding a pause using Utilities.sleep(3000) but that didn’t help.

    1. admin says:

      Yea this is tricky since the e.values[4] is actually taking those values from the form as it submits to the spreadsheet since we have the trigger set up to be on form submit.

      One possible solution would be programming a new variable in the script itself that would calculate what you are doing in the spreadsheet and print it in the email.

      The other option would be to use the getRange() method and set up a different trigger, although this might get a bit more complicated. Here is a link to more info on how to use getRange() or getDataRange(): https://developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange()

      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheets()[0];
      // Returns the first cell
      var cell = sheet.getRange("A1");

      1. Jean says:

        I have the same issue here:
        Here is my code
        ss = SpreadsheetApp.getActiveSheet();
        columns = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];

        Not sure how to edit it in order for the confirmation email to catch my array formula and showing the total numbers on the email.
        Please help out. Thanks in advance.

        1. admin says:

          Ok, so first you will need to figure out what type of data is being stored in the var columns array. To do that, you could run console.log(columns); and see what kind of data is returned. Then there are a couple of methods you could run, like columns[index of item].toString() to print it to a string you could use elsewhere.

  4. Booth says:

    Hi, I tried your script, and it did not work. When I clicked the “run” button, it said

    TypeError: Cannot read property “values” from undefined. Line 2.

    This refers to the “Var Username” line, but I can’t figure out what is wrong with it.

    HERE is the script I used:

    function myFunction(e) {
    var Username = e.values[1];
    var Useremail = e.values[2];
    var Date = e.values[3];
    var Subject = “Thank you for your MEAC Request Form Submission”;
    var Message = “Thank you” + Username + “for submitting an event request to the MEAC. The General Manager will be in touch with you via email to confirm details and dates”;

    MailApp.sendEmail(Useremail, subject, Message)

    Also, once this works, is there a way to set it up so that I get a copy of the confirmation email too?

    1. Booth says:

      Okay, so I did get the script to work with one of my regular Gmail addresses, but it will not give me a response email if I use my schoolsystem email account, which is run by google but ends in .net instead of gmail.com. Is there something about the school network Google accounts that is keeping this script from working with those maybe? Maybe there is some kind of permission or something that needs to be bypassed? Any Ideas?

      1. admin says:

        Yes, sometimes these emails can get caught in SPAM folders. Since they are automatically generated, they can look like phishing scams to a mail server. You could get in touch with you IT department to add the address sending the notifications is added to the safe senders list.

    2. admin says:

      Please see my response to NS’s question in the comments thread.

    3. vino says:

      Hi. I had similar problem. I deleted my script and created a new script and this worked. Thanks for the script! This was my first!

  5. NS says:

    Hi, thanks for this helpful tutorial. I’m continuing to get this message when I go to Run the script: TypeError: Cannot read property “values” from undefined. (line 2, file “Code”)

    Here’s what I have in the code:

    function myFunction(e){
    var Event = e.values[1];
    var userName = e.values[3];
    var userEmail = e.values[4];
    var subject = “Campus Connections Reservation Confirmation”;
    var message = “Thank you,” + userName + “, for reserving a resource table for the following upcoming Campus Connections events:” + Event;

    MailApp.sendEmail(userEmail, subject, message);
    }

    What do I need to edit?
    Thanks!

    1. admin says:

      If you are running the script in the script editor, this error is normal since the function does not have an event (form data) to process into the variables. If that is your only error, try saving it and actually submitting a test form and see what happens. It should work based on your code, so let me know if you get another error.

      1. Anne says:

        I did what NS did and it didn’t work for me when I tried to email it to myself. I tried another email but it did not work either? Why is that?

        1. admin says:

          Did you put a trigger on the script to run on form submit?

          I’ll need to see the script to offer more assistance.

  6. Missy says:

    Thanks for this.. it was very clear. But I have tested mine and no emails are being sent. I check the logs on the script and they are empty. Any ideas?

  7. Missy says:

    Sorry here is my current script
    function myFunction(e){
    var userfName = e.values[1];
    var userlName = e.values[2];
    var userEmail = e.values[12];
    var abTitle = e.values[11];
    var abType = e.values[13];
    var ab = e.values[14];
    var subject = “Thank you for your abstract for AMBBS 2014”;
    var message = “Thank you, ” + userfName + ” ” + userlName + “n for your abstract submission of: n” +abTitle;
    MailApp.sendEmail (userEmail, subject, message);}

    1. admin says:

      Your script looks good. Two questions: did you set the trigger to run the function ‘on form submit’? and, did you add this script to the form or the spreadsheet?

  8. Leslie says:

    Any idea why I am still getting the error message? “TypeError: Cannot read property “values” from undefined. (line 2, file “Code”)”

    function myFunction(e) {
    var Username = e.values[2];
    var Useremail = e.values[3];
    var Subject = “Thank you for your response”;
    var Message = “Thank you” + Username + “, for volunteering and/or joining the PTA.”;

    MailApp.sendEmail(Useremail, subject, Message)}

    1. admin says:

      You can’t run the script in the editor since it needs event (form submission) data to place in the variable you’ve created. Have you tried setting the trigger and submitting a test form with your email as a way to test the script?

      1. Leslie says:

        I have tried setting the trigger and submitting a test form. Unfortunately, it still does not work. I am getting notifications that say Line 7 Subject not defined, but as you can see in the code above, I did define a subject. Any other ideas? Thank you!

  9. Nae says:

    Hi
    I was able to get the original script to work two times before I tried to customize it. I am trying to send random confirmation codes to respondents by loading an unused column with random numbers then using those numbers (column) in the email response. This would be simple if Google forms didn’t insert new lines for each response instead of pasting them. To compensate for that I move that column to a separate sheet, then linked the sheets using a importrange formula. Then I tried to run these two scripts together and that’s when I started getting that error everyone is talking about.
    After thinking about it, I either need (1)to find a script that will let me place data from two sheets into the automated email
    OR (2)I have to find a way to recalculate the imported range before sending the email.
    I don’t have much experience in script, but this is my attempt at option 2 far after lots of research. They work separately but not together.

    function onEdit() {
    // get active spreadsheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    }

    function myFunction(e){
    var userName = e.values[3];
    var userEmail = e.values[4];
    var code = e.values[5];
    var subject = “Revision Submission Confirmation Code”;
    var message = “Thanks ” + userName + ” for submitting your proof of revision. Your confirmation number is ” +code;

    MailApp.sendEmail (userEmail, subject, message);

    }
    Can you tell me what I am doing wrong? When I put them together only the first one (recalculator) works.

    Thanks

    1. admin says:

      Ok, so this is a pretty complex issue you’re describing, one that is also beyond the scope of this tutorial. I’ll try my best to help.

      First, the var code=e.values[5] is still taking the event data from the form submission, not a pre-existing column as you describe, so this is an issue.

      Second, I’m not sure what the first function is doing. You define a variable that opens the active sheet, meaning the sheet to which this script is attached. If you want to pull data in from another sheet you should use the SpreadsheetApp.GetSheetbyID(), which will let you pull in data from another specific sheet, although you will still need to specify the range.

      However, a simpler solution, if you are just sending them random codes and you don’t need to cross reference the random codes, would be to create a variable that just generated a random number between 1-100, which would look like this: var code= Math.floor((Math.random() * 100)+ 1); You can find more about that here: http://www.w3schools.com/jsref/jsref_random.asp

      1. Nae says:

        Thanks so much for the random number generator. This is what I wanted to begin with. However, when I add it to the script, the email won’t send, and I am still getting: TypeError: Cannot read property “values” from undefined. (line 2, file “Code”

        Here is what I have done:

        function myFunction(e) {
        var firstName = e.values[3];
        var lastName = e.values[2];
        var userEmail = e.values[4];
        var code = Math.floor((Math.random() * 9999) + 1000);
        var subject = “Revision Submission Confirmation Code”;
        var message = “Thanks ” + userName + ” for submitting your proof of revision. Your confirmation number is ” +code;

        MailApp.sendEmail (userEmail, subject, message);
        }

        Once again
        Thanks so much

        1. Nae says:

          I got it to work. I saw my mistake with the firstName lastName.
          Thanks so much
          It works perfectly now

          1. admin says:

            Awesome! Very cool extension of the initial code. Thanks for the comments!

          2. danny says:

            instead of random can there be a sequence like 1,2,3… and not picking a random #?

          3. admin says:

            That is tough because any counter variable you create will be reset each time the script is run. If you use the get last row method, you should be able to add a unique number that corresponds to the row being edited. Take a look at the docs here: https://developers.google.com/apps-script/reference/spreadsheet/sheet#getLastRow()

      2. Kristof says:

        Hi, I get the random code in my email, works perfect! Is it also possible to get this random code on my spreadsheet (same code as in email…)?

        BTW, Great site, very helpfull!
        Thanks

        1. admin says:

          Hey Kristof,

          I’m not sure what you are asking here. What do you mean by random code? You can just use the example linked here to set a cell or range to a particular value: https://developers.google.com/apps-script/reference/spreadsheet/range#setValue(Object)

          Let me know if that link helps, or provide me with a more specific example of what you want to do so that I can help.
          Cheers,
          JE

  10. Karen says:

    Help. I can not get this script to work!

    Here’s my script

    function EmailConfirm(e){
    var userFirstName = e.values[1];
    var userlastName = e.values[2];
    var userGrade = e.values[3];
    var Email = e.values[11];
    var dayMon = e.values[4];
    var dayTues = e.values[5];
    var dayWed = e.values[6];
    var dayThurs = e.values[7];
    var dayFri = e.values[8];
    var subject = “GOA Lunch Order Confirmation”;
    var message = “Your lunch order for, ” + userFirstName + userlastName + ” has been received. Your order details:” + dayMon + dayTues + dayWed + dayThurs + dayFri;

    MailApp.sendEmail (Email, subject, message);
    }

    Email Address is in column L so [11]
    Trigger is set for form submit. This is the failure notification I get.

    Start Function Error Message Trigger End
    9/17/14 11:48 AM EmailConfirm Invalid email: undefined (line 14, file “Code”) formSubmit 9/17/14 11:48 AM

    I can’t figure out where I am going wrong.

    I am sending from a Google Apps account, I don’t know if that makes a difference.

    Thank you for your help!

    1. admin says:

      So, I’ve had an issue like this recently. I think there is a small bug when using the (e) event parameter. Since you have email so high up in the list of variables, it may be pulling an undefined value from another cell of e.values[4-10]. Id try creating a new form with the email as e.values[4] and all of the form fields that correspond to variables you are using to populate the three MailApp arguments (Email, subject, message) set to required, with “no choice” or something like that being an option.

      I hope that makes sense; let me know if you need additional info.

  11. Kathy says:

    This is the code that I have used on two other forms (text slightly edited) but otherwise the same:

    function myFunction(e) {
    var username = e.values[1];
    var userEmail = e.values[5];
    var subject = “Strathcona Ladies Business Showcase on November 15th”;
    var message = “Dear ” + username + “, thank you for registering to participate in the Strathcona Ladies Business Showcase taking place on November 15th, 2014. If you have any questions or concerns, please email Kathy at ***. ”

    MailApp.sendEmail(userEmail, subject, message)

    }

    I then receive the following error email:

    myFunction Invalid email: No (line 7, file “Code”) formSubmit

    I can’t figure out what I have done wrong. Sorry I am not overly competent with this ‘script’ business.

  12. Kathy says:

    Details:
    Start Function Error Message Trigger End
    9/23/14 5:31 PM myFunction Invalid email: undefined (line 8, file “Code”) formSubmit 9/23/14 5:31 PM

    I get the above in an email I receive after submitting a form. I just cannot figure out what I’m wrong. In fact I’m getting the same message on all three spreadsheets that I”m using. The code is:

    function myFunction(e) {
    var userName = e.values[1];
    var userEmail = e.values[5];
    var subject = “Strathcona Ladies Business Showcase on November 15th”;
    var message = “Dear ” + userName + “, thank you for registering to participate in the Strathcona Ladies Business Showcase taking place on November 15th, 2014. If you have any questions or concerns, please email Kathy at ***. “;

    MailApp.sendEmail(userEmail, subject, message);

    }

    1. admin says:

      Hi Kathy,

      I’m going to reply to both comments in this single thread. The error you are getting means that when your code is running and gets to the MailApp section, the email address is not valid, and therefore it cannot send the email.

      This can be caused by a few things: 1. the email address could in fact be invalid; you need to include the @validdomain for this script to run successfully. There is actually a data validation to the Google form that will check for a valid email address, and I’d recommend turning this on.

      2. Something could be funky with the script itself, so I’d recommend commenting out the MailApp line so that it doesn’t run and then write over again and test one more time. Not sure why this works, but I had to do it the other day even though nothing was wrong with my code. Google updated forms recently and lots of things have been buggy. Here is a link to info on creating comments: http://www.w3schools.com/js/js_comments.asp

      3. This is the most complicated, so let’s hope this isn’t the issue. I can see that your variables skip from e.values[1] to e.values[5]. If any of the values in between are optional, the script could be pulling that ‘undefined’ data into the userEmail variable by mistake. To fix this, you’d have to completely remake the forms, but put userName and userEmail as the first two form fields.

      So, I’d recommend troubleshooting in this order (1,2,3) and let me know if you have other questions or issues!

      Regards,
      JE

  13. Iván says:

    Hi, I have this error
    TypeError: Cannot read property “values” from undefined. (line 4, file “Código”)

    This is my code:
    function emailOnFormSubmit(e) {

    var nombre = e.values[1];
    var mail = e.values[2];
    var participante = e.values[7];

    // The subject of the email
    var asunto = “Seminário Filosofia na Escola – Confirmação da inscrição para ” + name;

    // emailBody
    var emailBody = “Este é um e-mail confirma que ” + nombre +
    “nse inscreveu exitosamente no ‘IX Seminário Filosofia na Escola – V Seminário PIBID – Filosofia’, ” +
    “nsendo o tipo de inscrição ” + participante +
    “nnMuito obrigado e boa participação!”;

    // html
    var htmlBody = “Este é um e-mail confirma que ” + nombre + “” +
    “se inscreveu exitosamente no ‘IX Seminário Filosofia na Escola – V Seminário PIBID – Filosofia’, ” +
    “sendo o tipo de inscrição ” + participante +
    “Muito obrigado e boa participação!” +
    “Att.”
    “Comissão Organizadora”;

    // Advanced Options Parameters

    var advancedOpts = { name: “Comissão Organizadora do Seminário Filosofia na Escola”, htmlBody: htmlBody };

    MailApp.sendEmail(mail, asunto, emailBody, advancedOpts);
    }

    I see that others have the same error, but I didn´t understand how to fixe it. Can you help me?
    Thanks.

    1. admin says:

      Typically this happens when you try to run in the editor. You can’t run the script in the editor since it needs event (form submission) data to place in the variable you’ve created. Have you tried setting the trigger and submitting a test form with your email as a way to test the script?

      1. Leslie says:

        I am having the same issue. I did submit a response in the form, but it is still not working. Any suggestions?

      2. Iván says:

        The same with me 🙁
        I did submit a response in the form, but it is still not working.
        Thanks for the help 🙂

  14. Edgar says:

    I’m trying your code but I get this error TypeError: Cannot read property “value” from undefined. (line 4, file “Code”)Dismiss

    I understand is because I’m debugging in the editor. However when I run the form, it adds another row to the spreadsheet but ignores my code. I have set the trigger. Any suggestions?

    function onSubmitForm(e) {
    var toAddress = “edgardoimar@gmail.com”;

    var timestamp = e.value[0];

    MailApp.sendEmail(toAddress, “test”, timestamp);
    }

    1. admin says:

      A couple of things; first, it should be e.values not e.value for your timestamp variable. Second, instead of using the string ‘test’ in the MailApp method, create another variable called subject and set that equal to ‘test’. Third, I’d set error reports to send immediately so that we can get some more info when you submit the form and it fails to send.

  15. Chris Plesco says:

    first, this is a wonderfully concise script, thanks.

    I am attempting to modify the script to include an already present value in a field. When mapped to the field that includes the current value in the spreadsheet, after the form is submitted, the pre inserted value moves down to the next row.

    Might you know how to include this value in your script? I’ve included the code below.

    Thanks again!

    Chris

    function myFunction(e){
    var email = e.values[1];
    var condition = e.values[2];
    var ac = e.values[3];
    var wo = e.values[4];
    var subject = “Form Submitted”;
    var message = ” Home Condition,” + condition + ” AC Condition” + ac + “Work Order Number” +wo;
    MailApp.sendEmail (email, subject, message);}

  16. Chris Plesco says:

    found the answer if anyone was interested – used the row count instead:D

    function FindRows() {
    range = SpreadsheetApp.getActiveSheet().getLastRow();
    return range;
    }

    1. admin says:

      Awesome, Chris. Glad you found the answer, and thanks for sharing with everyone!

  17. Mark Holstein says:

    Good Afternoon… Here is my code I created from watching your tutorial. IT works great! I was wondering how to make a line break in the “message” part of my email?? the n and others wont work…

    Thank YOu

    function myFunction(e) {
    var staffnameFirst = e.values[4];
    var staffnameLast = e.values[18];
    var staffuseremail = e.values[26];
    var date = e.values[5];
    var studentinfraction = e.values[8];
    var behaviordescription = e.values[10];
    var studentnameFirst = e.values[1];
    var studentnameLast = e.values[17];
    var subject = “Referral Form Submitted”;
    var message = ” Thank you ” + staffnameFirst + staffnameLast + ” for submitting your refferal for ” + studentnameFirst + studentnameLast + ” on ” + date +
    ” for the infraction ” + studentinfraction + ” described as ” + behaviordescription + ” Thank You ” + ” Mr Reineck ”

    MailApp.sendEmail(staffuseremail, subject, message);

    }

    1. admin says:

      The n character should work, as it is the default new line character in JS. Check out this article and make sure you are implementing them the same way: http://stackoverflow.com/questions/5758161/how-do-i-create-a-new-line-in-javascript

      If it still doesn’t work, let me know and I’ll find another way.

      JE

  18. Sam Eifert says:

    Hi Jeff,

    This was an amazing tool and helped me out with a few of my docs so far. I’m running into an issue when I have multiple sheets in one response doc. I have one sheet/tab for one type of vendor, and multiple others for other vendors, and one script won’t work for them all. Is there a way to change the script to correspond with each individual sheet, or would I need to set up separate sheets to be able to do this? Any help would be appreciated!

    Thank you!

    1. admin says:

      Hey Sam,

      That is good questions, and there are a lot of ways to grab data from other sheets. I’d check out the getActiveSheet and openByID methods at the following link for some examples of how to get data from sheets other instead of event data.

      https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app

      Regards,
      JE

      1. Sam Eifert says:

        Thank you for the quick reply! I’ll give those a shot. I appreciate it!

      2. Sam Eifert says:

        Hi Jeff,

        Sorry to bug you again. I followed the link, but don’t think I understand this exactly as I should. I have a Spreadsheet with 4 sheets (Beer, Cheese, General & Food). I need auto reply emails to be sent out for each sheet, but the data isn’t arranged the same way on each sheet, so the basic code to send out an email won’t work. Here is what I have from before, and in addition to what I was hoping would separate them, but I’m clearly missing something.. Could you give me just a little more direction on this, or tell me if I’m way off?

        function myFunction(e) {
        var userName = e.values[3];
        var brewery = e.values[1];
        var email = e.values[9];
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var Beer = ss.getSheetByName(“Beer”);
        Beer.activate();
        var subject = “Registration Received”;
        var message = “Thank you, ” + userName + “for registering ” + brewery + “with Isthmus Beer and Cheese Fest 2015. We have received your registration and will contact you with more details as the event draws near. If you have any questions, please don’t hesitate to contact us. “;

        MailApp.sendEmail(email, subject, message);

        Thank yoU!!

  19. Syed says:

    Hello,
    Thanks for this post, it helped me. I can send submission email. However I have one question is there any way to debug this email confirmation code, since we can only check with form submission. I can send confirmation email at time of form submission but I tried to use this code in my function of Reminder Email, but it is not working fine.
    Here is my code, may be you can guide me: function firstAppointmentDeadLine(e){
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2;
    var lastRow = SpreadsheetApp.getActiveSheet().getLastRow();
    var dataRange = sheet.getRange(startRow,2,lastRow,14);
    var values = dataRange.getValues();
    var today = new Date();
    var lastCol = SpreadsheetApp.getActiveSheet().getLastColumn();

    for(i=0;i<values.length;++i){
    //[14] Appointment Date
    var firstAppoint = values[i][12] ; // 12 is the index of the column having appointment date converted from string format (column5) to date format.
    var timeDiff = Math.abs(firstAppoint.getTime() – today.getTime()); //calculating time diff
    var diffDays = Math.ceil(timeDiff / (1000 * 3600 * 24));
    // if two days are left in appointment then send automatic reminder email
    if (diffDays == 2) {
    var userFirstName = e.values[1];
    var userEmail = e.values[4];
    var FirstAppoint_date = e.values[5];
    var subject = "Reminder for First Appointment";
    var message = "Dear " + userFirstName +","+ "nnWe like to remind you about your First Appointment on " +FirstAppoint_date+ "nPlease be on time.";
    MailApp.sendEmail (userEmail, subject, message);

    } //if ends
    } //for loop ends
    } // function ends

    1. admin says:

      Interesting project. What is the script doing now when you run it, error message, sending something you didn’t expect, or just nothing?

      It looks like, in the for loop, you are comparing the date of the appointment to today’s date and if there are fewer than two days left to the appointment, the script will send an email. Since this function isn’t really tied to the event submission, you might try removing the (e) parameter and on form submit trigger and changing to a time driven trigger, maybe running this every day at the same time. I would also have the date/time evaluator cycle through each row of the spread sheet and pull out the data using something other than e.values, since that pulls from the submission event.

      1. Syed says:

        Thanks for your reply. Right now it is giving error message “TypeError: Cannot read property “values” from undefined” at line where it is reading User Name from e.values (var userFirstName = e.values[1];).
        I have set trigger for this function Time Based and getting this error. Can this be possible e.values be used as Time trigger as well or it can just be used on form submission ?

        Are you aware of any other method to pull data other than e.values on time trigger?

        Thanks in advance

        1. admin says:

          Yes, the e.values and time triggers don’t mix well since the event happens on form submission and the time trigger could run later.

          There are a couple of other methods that you could use, and here is a link to the Google Spreadsheet Documentation:https://developers.google.com/apps-script/reference/spreadsheet/range#getValue()

          getValue() or getValues() would likely work for what you’re trying to do, something like this example:

          var values = SpreadsheetApp.getActiveSheet().getRange(2, 3, 6, 4).getValues();

          There are also tons of other get methods you could string together to make sure you are getting the cells and values you need. Check out the Google Developers site for some examples and a list of methods.

          Cheers! Happy coding.

          1. Syed says:

            Thanks a lot, it worked for me 🙂

  20. Teresa says:

    I need to send emails out AFTER they submitted the form ;( We didn’t get this far when first trying this great google form generator.

    Can I send emails out from “edit” from the spreadsheet using this code? I so hope so. Thank you in advance for your quick reply!

    1. admin says:

      What do you mean by “from ‘edit’ from the spreadsheet”

  21. Zakir Hussain says:

    First of all thank you so much for this tutorial. I have one request, in similar lines i.e., through the script (script using if conditional cases) can we prevent multiple form submissions by same user(using same name, email id , and/or etc….)

    once again Thank You so much.

    1. admin says:

      Ok, so I’m a bit confused by this question. Are you asking if we can prevent the script from sending more than on email reply to users based on user name etc., or if we can prevent them from submitting more than one form?

      If you’re question is the second, I’m not sure that we can, since the form would need to process the data before you could fire any function contained in a conditional statement. However, you might be able to use a conditional statement to check to see if a user name, etc. already exists in the form, and if it does, delete that row.

      Here is a link to the Google Developers site on how to delete rows: https://developers.google.com/apps-script/reference/spreadsheet/sheet#deleteRow(Integer)

      Another thing to check would be whether or not there is a Google Forms setting that would only allow one submission, but that might not exist.

      1. Zakir Hussain says:

        Thank you so much for responding. My Question was, is there a way to stop multiple form submissions by the same user.

        —- Thanks in advance.

          1. admin says:

            Yes! This would work, but there are some limitations to this option, as the author notes. Great resource! Thanks, Syed.

          2. zakir hussain says:

            Thank you so much. I think this feature Google has added in recent past. Anyways thank you so much.

  22. admin says:

    So, my inkling is that url will be unique for every form submission; otherwise, how would Google know which submission needs to be edited. The problem is that I’m not sure the link will even be generated until the form is actually submitted.

    I’d dissect the url to see if there is some predictable pattern it follows, and then you could just include that in the message of the body and concatenate it with whatever other info, like spreadsheet id or submission number, that you would need to make it direct the user to the correct place.

    1. Eric Eldridge says:

      Google recently added GetEditResponseURL() which is documented at https://developers.google.com/apps-script/reference/forms/form-response. I’m struggling how to integrate this function into the send email sequence though.