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.

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

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.

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.

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.