Email confirmations from Google Forms

Update: This doesn’t work anymore. Instead, make a copy of this spreadsheet and then look at the associated script to see how it needs to be updated.

 

Our junior school is using Google forms as a sign-up system for our parents for co-curricular activities. This way we can automate the sign-up process, and use the data to generate our lists of students who are going to be in each club. Apparently quite a number of parents were not sure they had actually submitted their requests, and so our administrative staff asked if there was any way to send parents an email confirmation with the co-curricular choices they had made. Using this tutorial, I created an email confirmation script.

First step, make the form, something like below.

Google form example

 

Next, navigate to the spreadsheet form, and under the tools menu choose the Script Manager, as shown below.

Script manager

 

A dialog box will appear, and you will want to click on the New button at the bottom of the dialog bow, which will then lead to the following screen.

New script dialog box

From here you will want to select "Blank project" although you can see that you have a number of other options to explore later, should you decide to write other Google Apps scripts.

I wrote this script, which you are welcome to use as the basis for your own email confirmation script.

/* START COPYING HERE */
function emailConfirmation(e) {
  var userEmail = e.values[2];
  var pickUp = e.values[5];
  var otherNotes = e.values[4];
  var class = e.values[6];
  var firstChoice = e.values[7];
  var secondChoiceOptions = e.values[8];
  var secondChoice = e.values[9];
  
  /**
   * Un-comment this to use it for debugging
   */
  //for (var i in e.values) {
  //  Logger.log("" + i + ":" + e.values[i]);
  //}
  
  
  MailApp.sendEmail(userEmail, 
                    "Clubs sign-up confirmation", 
                    "Thanks for signing up your child for a club. You have indicated the following:\n\n" +
                    "Pick up:\n" + pickUp + 
                    "\n\nOther notes:\n" + otherNotes + 
                    "\n\nClass:\n" + class +
                    "\n\nFirst choice:\n" + firstChoice + 
                    "\n\nSecond choice:\n" + secondChoice + 
                    "\n\nSecond choice option:\n" + secondChoiceOptions,
                    {name:"Stratford Hall clubs"});
}
/* STOP COPYING HERE */

 

So now what this script does is takes specific values entered into the form by the parent, and turns these into an email confirmation. Each value in the form is an item in the e.values array. The biggest stumbling block I had was figuring out which form values corresponded to which items in the original form, because as I quickly discovered, the position of the form item in the e.values array does not necessarily correspond to it’s position in the form. In the code above, I discovered that I could use the log to determine what each form value actually corresponded to. If you need to do this trouble-shooting, uncomment (remove the // from the code above) the section of the code as per the instructions in the code above.

 

Log

You can then look at the logs (see above) after you have submitted a sample and use the output of the logs to determine which value of e.values corresponds to which form item.

The second part of the code that starts with MailApp.sendEmail actually sends the email to the parent. Hopefully you can see what I wrote above and adjust it to meet your school’s needs (helpful hint: \n corresponds to a new line in the email).

If you need more information on Google Scripting, I highly recommend the tutorials that Google publishes, but these are much more useful to you if you know the coding language they are based on, JavaScript. I learned JavaScript from a lot of experimentation, the W3Schools tutorials, and a very helpful and extremely dense book.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

12 Comments

  • Hi David,

    Thanks for sharing this tips on how to send confirmation email. I’ve been wondering the same for a while. Now I know!

    Steve Roy
    @tangomanfromqc

  • Just thank you for taking the time to publish these things, it’s helpful to everyone, not just teachers !! 🙂

    Regards

    Sam

  • Thanks for this little tidbit.

    For those who are looking to add more than just plain text, the SendMail App also supports HTML:

     MailApp.sendEmail({
        name:”Name of sender”,
        to: “email@address.com”,
        subject: “Subject goes here”,
        htmlBody: “<p>HTML can go here.</p>”
    })

     

  • How do I change the “from” email? I set up a confirmation email for a shared Google form but I have now left that organization. They want to keep using the form but I want the confirmation to come from another email, other than my own.

    This is the code I have:

    function FormConfirmationEmail() {
     
      var sheetname = “Sheet1”
      var columnnumber = 15
         
      var ss = SpreadsheetApp.getActiveSpreadsheet(); 
      var sheet = ss.getSheetByName(sheetname);
        
      if (sheet.getRange(sheet.getMaxRows(),1).getValue() != “”) {
         var lastrow = sheet.getMaxRows()    
      } else {
         var count = 0
         for (var i = 0; i < sheet.getMaxRows(); i++) {
            if (sheet.getRange(sheet.getMaxRows()-i,1).getValue() != “”) {
            var lastrow = sheet.getMaxRows()-i
            break;
            }  
         }
      }
      
      var email = sheet.getRange(lastrow,columnnumber).getValue();  
      var emailPattern = /^[a-z0-9!#$%&’*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&’*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|aero|asia|biz|com|coop|edu|gov|info|int|jobs|mil|mobi|name|museum|name|net|org|pro|tel|travel)\b/;
      var validEmailAddress = emailPattern.test(email); 
      
      if (validEmailAddress == true) {
        var message = “<HTML><BODY>”
            + “<P>Thank you for submitting your flight info to CeCI & SHH. If your itinerary changes please resubmit the new info using the same Flight Information Form (http://ceciskids.org/airportpickup). We look forward to seeing you soon!”
         + “</HTML></BODY>”;      
         MailApp.sendEmail(email, “Flight Info Confirmation”, “”, {htmlBody: message});      
      }
    }
  • David Wees wrote:

    Unfortunately the from address is fixed to whichever account installed and then authenticated the script. If you want a Google confirmation email to come from a different account, you’ll have to remove (delete) the script (after making a copy), login as the account you want the email to come from, re-setup the script and authenticate it to the new account.

    As far as I know, there is no other way to do this.

  • Simon Blackburn wrote:

    I have implimented this script but using my values from my spreadsheet. I have ran the script and set up triggers for instant email notification but I never ever get an email when the form is filled in and submitted despite the form details showing up in the spreadsheet.

    Has google changed the functionality of forms and scripts since this script was written?

  • David Wees wrote:

    It’s possible it has been changed. I know they did some work around form triggers when they upgraded the API. Something to check – you did authenticate the script so it has permission to send email as you?

  • Yes, I authenticated it, ran it, added triggers to send email on form submit, everything. Your’s isn’t the only script I have tried, I have tried many but all have failed. I’ll probably have to wait until someone figures it out (because I don’t posess the coding skills) or use some other form providor.

    Thanks for your reply anyway.

  • David Wees wrote:

    I should probably test this again to confirm that it works. Thanks for the heads up.

  • I’m really new to this and have worked for 2 days on this. After I fill out the form no confirmation email.
    Can somebody please enlighten me Thanks
    function myFunction() {
    var usrEmail = e.values[18];
    var usrChildFnm = e.values[1];
    var usrParentFnm = e.values[8];
    var usrParentLnm = e.values[9];
    // var subject = “2015 Summerbridge Registration”;
    // var message = “Dear Mr. or Ms. ” + usrParentFnm + ” ” + usrParentLnm + “: Thank you for the registration of ” + usrChildFnm ;

    MailApp.sendEmail(usrEmail,
    “Summer Bridge Registration confirmation”,
    “Dear Mr. or Ms.” + usrParentFnm + usrParentLnm +
    “Thank You for registering\n\n” +
    usrChildFnm +
    “\n\nin the Summer Bridge 2015.” +
    “\n\n\n\n\n\n\nSincerley Friendship Public Charter Schools”,
    {name:”Final Notice”});

    // MailApp.sendEmail(usrEmail, subject, message);
    }

  • Sorry I pasted some Garbage. Please help I am supposed to deliver by beginning of April
    function myFunction(e) {
    var usrEmail = e.values[13];
    var usrChildFnm = e.values[1];
    var usrParentFnm = e.values[8];
    var usrParentLnm = e.values[9];
    // Comment var subject = “2015 Summerbridge Registration”;
    // Comment var message = “Dear Mr. or Ms. ” + usrParentFnm + ” ” + usrParentLnm + “: Thank you for the registration of ” + usrChildFnm ;

    MailApp.sendEmail(usrEmail,
    “Summer Bridge Registration confirmation”,
    “Dear Mr. or Ms.” + usrParentFnm + usrParentLnm +
    “Thank You for registering\n\n” +
    usrChildFnm +
    “\n\nin the Summer Bridge 2015.” +
    “\n\n\n\n\n\n\nSincerley Friendship Public Charter Schools”,
    {name:”Final Notice”});

    // Comment MailApp.sendEmail(usrEmail, subject, message);
    }

  • davidwees wrote:

    The email functionality has been changed. The basic problem is this:

    You can’t send emails from a form trigger anymore. You have to change the trigger to a time-based trigger, and then somehow keep track of whether the email has been sent already or not before sending the confirmation.

    See this example (make a copy, and then edit the script).

Leave a Reply

Your email is never shared.Required fields are marked *