Sending Confirmation Emails with a Google Docs Form
Posted on Jun 18, 2011 in Tech Tips & Tricks | 246 comments
I love Google Apps. Gmail, Google Docs, Google Voice–all brilliant, indispensable products in my opinion. Being able to create forms in Google Docs for quick polls and surveys is also very handy, but the extremely limited feature set has been frustrating to me. One surprising lack is that of being able to send a confirmation email to the user. Granted, not all forms necessarily even have a field for the user to enter their email.
[Original post 6/18/11. Edited 10/29/11 to add HTML email to the example. Edited 4/21/12 to add more screenshots and correct “Triggers menu” to “Resources menu”.]
With a quick script, you can rectify this and customize an email that will be sent to the user. Don’t be intimidated by the code–if you can handle writing raw HTML, you will be able to modify this script to suit your needs.
However, if you’ve never used a Google Form before, you probably want to play around with that and get comfortable with that first. The rest of this assumes that you have created a form and are viewing the attached spreadsheet.
The first step, in Google Docs, is to click on “Tools” and then “Script Editor.” A new window will open, with an empty function called “myFunction.” Overwrite the contents of this window with the code you want to use. Below I’ve added a complete example that you can rework to your needs.
function onFormSubmit(e) {
var timestamp = e.values[0];
var yourName = e.values[1];
var toAddress = e.values[2];
var eventName = e.values[3];
var visitDate = e.values[4];
var visitComments = e.values[5];
var subject = "CAP Confirmation - " + eventName + " " + visitDate;
var emailBody = "Thank you for your Club Ambassador Program report submitted on " + timestamp +
"\n\nThe details you entered were as follows: " +
"\nYour Name: " + yourName +
"\nYour Email: " + toAddress +
"\nClub or Event Name: " + eventName +
"\nVisit Date: " + visitDate +
"\nVisit Comments: " + visitComments;
var htmlBody = "Thank you for your <b>Club Ambassador Program</b> report submitted on <i>" + timestamp +
"</i><br/> <br/>The details you entered were as follows: " +
"<br/><font color=\"red\">Your Name:</font> " + yourName +
"<br/>Your Email: " + toAddress;
var optAdvancedArgs = {name: "Club Ambassador Program", htmlBody: htmlBody};
MailApp.sendEmail(toAddress, subject,
emailBody, optAdvancedArgs);
}
See the first section, with “e.values”? Those refer to the form fields. “e.values[0]” will always be the timestamp. “e.values[1]” will be the first field in your form, and so on.
Feel free to change the variable names. If the first form field isn’t the respondent’s name, but is actually their favorite color instead, you can certainly change it from yourName to faveColor. Just make sure to change all occurrences of the identifier.
If you have fewer form fields, remove the lines you don’t need. If you have more, copy and paste the existing lines, making sure to increment the numbers… so “e.values[27]” would be the 27th form field, and so on. You do not need to reference every form field in your script–just have lines for the ones you want to include in the email.
Next, we put together some strings (a string of words) to be put into the email. If the text is in quotation marks, it will appear just like you entered it. If the text is not in quotation marks, then it refers to one of the variables we just set up. Plus signs concatenate those strings, and every line needs to end in a semicolon.
“\n” is special. In quotation marks, this refers to a newline character… a line break. Add that whenever you would press “enter” in the email if you were writing it by hand.
Modify the “subject” line as necessary. Yours might be simpler or more complex.
Modify the body section as necessary. This was broken up into multiple lines just for readability. It could all be on one line. Just make sure to have plus signs to connects different strings of text, and only to have one semicolon (at the end).
The “optAdvancedArgs” section is special. It refers to special settings (anything beyond the basic to, subject and body settings) for an email. You don’t have to use it. You can simply remove that, changing that line to:
MailApp.sendEmail(toAddress, subject,
emailBody);
Want to set other advanced arguments, like a cc line, an HTML body for the email and more? View the MailApp Script Reference and scroll to the bottom of the page for a list of what you can set and examples of how to use them.
- If you provide an HTML version of the email, also provide plain text for those email clients that can’t see the HTML version.
- Emails don’t work like web pages. Writing HTML for an email is like making a website in the 90’s. Read the linked article or do some Googling to find out what tags you should and shouldn’t use.
- Email clients tend to be narrow. Don’t count on having much horizontal space.
- When you are writing a Google Docs script and need to refer to a quote character, such as when writing attributes in an HTML tag, use a backslash before the single or double quote, or else Google Docs will think you’re ending the string. Check the example for when a font color is added to “your name”
Modify the script, and then save it. However, there is one more important step to take to get this to work!
In the script editing window (not the normal Google Docs window), click on the “Resources” menu. Click “Current Script’s Triggers…” and then “Add a new trigger.”
The first field should be “onFormSubmit,” the second “From Spreadsheet” and the third “On form submit.” Change these if necessary and add the trigger. You will need to give your approval for Google to allow the form to send emails.
There! Now try submitting a form and make sure the email looks the way you want.
Hi there, thanks for doing this. Been a while since I scripted and this will be great help. I tried to implement it on my document but the script gives an error on line 3 “can not read properties “values” from undefined”. I would assume that this refers to the definition of the var but how is that linked to the doc?
Sorry – beginner’s question!
Questions are great, Dietrich–I’m sure that if you have questions, others will as well, especially since I just quickly whipped up this little tutorial.
It sounds like you’re trying to run the script directly, and that doesn’t work, because you need to pass an argument along to it.
There are multiple ways to test this, but probably the easiest is to follow the instructions I’ve specified exactly–that is, set up the trigger, then actually try submitting the form and see if it works.
Alternatively, you can create another function with no arguments that creates the “e” object with the array of text values and then calls onFormSubmit, but I think it would be easier just to get the form working and test the completed version 🙂
Let me know if this helps!
Works perfectly well ! Thanks for this more than useful script. I will use it a lot.
Thanks for the kind words, Philippe! I hope it’s useful to many people.
Very helpful, thx
current version of the site use previous old fashioned php form
new version of the site will soon use google form using your trick to send from responses by email.
This is exactly what I’ve been looking for. I’ve added it to my form, but I keep getting an error saying that my email address is invalid, which I know it isn’t? Any suggestions
Julie, I’d love to help out, but I’ll need more information–especially since I’m sure you’re customizing the form in some ways. I’ll email you and see if you can provide some more information so we can find a solution and I can improve the post. 🙂
Thank you very much!! It works very well for me. Simple to install; the explanation is simple to understand and helpful to modify to fit my need.
Thank you!
Last year I used the FormEmailer script (http://sites.google.com/site/formemailer/) to achieve the email response function for event registration and got it working fine… but yours is MUCH simpler to use.
Very nice and much appreciated!
Neil
Hi I´used your script and its works well. I have one problem, I´m not som familiar with the script yet… I just want to make a hyperlink of a mailaddress in the emailBody-part. I have try and try but I can’t fix it. I think its easy but I can’t figure it out…
Anci, the problem is that the email is a plain text email. You need to use an HTML email. I’ve updated the example to demonstrate how to send an HTML email. I hope that works for you! 🙂
I tried using this today – works great! Is there a way to set up different confirmation emails for different forms? I can’t find a place to specify a certain script to run for each form.
Thanks again!
Kevin
I haven’t tested it, Kevin, but try this: in your Google Doc, go to Tools > Script Editor. In the Script Editor window, go to Triggers > Current Script’s Triggers. Then there are three drop-down menus. The last two should always be “From spreadsheet” and “on form submit” for your purposes, but the first field says what specific script to run. That way you could have multiple scripts, and then you just set the trigger for each individual script for each form as described in the above post. Try this out and let me know how it works–hope that helps!
Is there a way to change the FROM email address that sends the message?
Unfortunately not that I know of, JC. That is the downside. However, if you’re using Google Apps already, then you can have those emails coming from an email in your domain–but yes, I have found that it’s less than ideal, since you can’t change the from address. If anyone finds of a way to do that, I’d love to hear about it!
found this very useful 🙂
Very cool script. I think it will be very useful for me. Thank you for sharing. I do have one question though….
I am responsible for sending out forms on a regular basis, however the questions being asked are always changing. I would like to build a script once that I can copy and paste into each of my forms that treats the question asked in the google form as a variable so that I don’t have to type it in the response section each time. For example…
Currently in the confirmation email you have “\nYour Name: ” + yourName +
What I would like is: “For the following question:” + variable-that-repeats-the-question + “you replied: ” + yourName +
Is this something that can be done easily?
thanks for your help! Keep up the great work.
Thank you for this!
Great script and very clear documentation.
Thanks Matt! I really appreciate the kind words.
Thanks for the details on this. I found out about this when I got an email confirmation for a google form I submitted. I immediately googled up your explanation of how to include such an automatic email response on our projects forms. Very easy to get it working, thanks so much.
It will hopefully eliminate the problem of multiple form submissions for those who were unsure if it went through.
Ideally we would like it if the email could also contain variables that are generated by looking up the form information in our database. For example, having a specific message depending on the answers they gave to the questions.
thanks
Matt
sweeet, exactly what I need,
thanks a lot
Many Thanks
it worked nice
🙂
Thanks for tutorial, works like a charm and saved me a lot of time!
This is brilliant! Love the scripting via JS that Google uses for Docs. I have been looking for a simple and clean solution for a gig that I have and this is by far the cleanest, simplest & most efficient auto emailer for forms. Thank you!!!!
Great tutorial! Got one set up for our organization and we now have Welcome letters automatically being sent to new members.
One small detail about the article regarding: In the script editing window (not the normal Google Docs window), click on the “Triggers” menu. Click “Current Script’s Triggers…” and then “Add a new trigger.”
There is no ‘Triggers’ menu. The Triggers items are on the Resources Menu in the script editor. That took me a minute to find because I wasn’t sure what you were talking about since there’s no Triggers menu.
Thanks to everyone for your gracious comments! I’m glad people find this helpful.
Chris, thanks for the note–apparently that menu either got renamed at some point, or I wrote down the wrong name. This has now been corrected.
Any idea how to get this to work with a form data email? I placed the scrip above just after the script from this page: http://www.labnol.org/internet/google-docs-email-form/20884/.
Neither works when I put both n the same code. Any ideas?
This is a beautiful script. Thanks for sharing!
This is brilliant, thanks for sharing.
One problem: I’ve tried submitting the form using my email address but do not receive any email. When I attempt to ‘Run’ or ‘Debug’ the script from the Script Editor I get the same error as Dietrich at the top of these comments: “TypeError: Cannot read property “values” from undefined. (line 3)”
I have set-up the Trigger as in the steps above and specified all variables based on my form columns, to no avail.
Do you have any suggestions?
is there a way to check the input form id? I want to check if i put the correct number for certain variable in value[]. However since my form is quite long and complicated i would like to check that.
I have also the same problem as Camden, for some reason values cannot be read.
Can you please give me a hint what might be wrong with the code?
Thank you!!
This is awesome! I am just wondering if there is a way to create something like an if/then statement that chooses the e-mail address based on a form response.
Example: let’s say the students fill in the form which I want sent to their teacher. They select the name of their teacher from a list of 6 teachers, is there a way that I could create an if/then statement or something of that nature that would determine which e-mail address to send the e-mail too?
Thanks!
OMG THANK YOU!!! I am on the board for a small non-profit, and we can’t afford the kind of program that would do this type of thing from our website…so having it through Google Docs and then this script totally made my day! Thank you, thank you, thank you!
I’ve set this up according to your instructions but no e-mail seems to be sent. I set up the trigger, here’s a screenshot of my trigger settings: http://i.imgur.com/lt8rb.png
What am I missing?
Nevermind, I fixed it! After removing some fields I of course had to remove all references to those variables in the script.
Glad you got it fixed, Lasse!
Hi,
I don’t get the confirmation email…
this is my script now:
function onFormSubmit(e) {
var timestamp = e.values[0];
var toAddress = e.values[4];
var subject = “CAP Confirmation – “;
var emailBody = “Thank you for your Club Ambassador Program report submitted on “;
var htmlBody = “Thank you for your Club Ambassador Program report submitted on “;
MailApp.sendEmail(toAddress, subject,emailBody);
}
I have created a trigger (using resources>current script triggers..)
What am I doing wrong? how do I check to see that the form IS connected to the script?
Thanks
Thank you for this tutorial! Very easy to follow and Amazing script.
I have a problem! I love your code and I am sure it works great! However I am just starting to learn programming. And to the best of my ability I have replaced your information with mine. I have set up the trigger. and I have built my form correctly. But it does not seem to send me an email when I have set this up! Could you please help?
Attached is the code and link
function onFormSubmit(e) {
var timestamp = e.values[0];
var studentid = e.values[1];
var testscore = e.values[2];
var email = e.values[3];
var subject = “5 minute Math Test ” + studentid;
var emailBody = “Thank you for completeing the 5 minute math test on ” + timestamp +
“\n\nTest Information: ” +
“\nYour StudentID: ” + studentid +
“\nTest Score: ” + testscore;
var htmlbody = “Thank you for completeing the 5 minute math test on ” + timestamp +
“ Test Information: ” +
“StudentID: ” + studentid +
“Test Score: ” + testscore;
var optAdvancedArgs = {name: “Expressways To Learning®”, htmlBody: htmlBody};
MailApp.sendEmail(email , subject,
emailBody, optAdvancedArgs);
}
https://docs.google.com/spreadsheet/viewform?formkey=dEhuekJxaFJ6TWZiaDJ2LUNaOVM4TUE6MQ#gid=0
Actually I got it working!
I’m trying to get this to work, but cannot get it to send an email. My spreadsheet has four pages, and the one that I need to draw the information from is the third sheet called “Composite.” I had to do it that way because I’m using vLookup to fill in information such as email address, date, etc. so the person filling out the form doesn’t have to do so much work. Anyway, my form is at http://www.sgvfoa.com/ratings.shtml, and here’s my script:
function onFormSubmit(e) {
var ss = SpreadsheetApp.getActive().getSheetByName(“Composite”);
var TimeStamp = e.values[0];
var GameDate = e.values[1];
var GameLevel = e.values[3];
var Home = e.values[4];
var Visitor = e.values[5];
var Site = e.values[6];
var EvaluatorEmail = e.values[8];
var Evaluatee = e.values[9];
var EvaluateeEmail = e.values[10];
var Position = e.values[11];
var Appearance = e.values[12];
var Attitude = e.values[13];
var Pregame = e.values[14];
var Rules = e.values[15];
var Mechanics = e.values[16];
var Communication = e.values[17];
var Hustle = e.values[18];
var Postgame = e.values[19];
var Comments = e.values[20];
var subject = “SGVFOA Evaluation for ” + Evaluatee + ” on ” + GameDate;
var emailBody = “Here is your evaluation for the game you worked at ” + Site + “as a ” + Position +
“\n\nEvaluations are on a scale from 1 (needs improvement) to 5 (exceeds expectations)” +
“\n\n” + Visitor + ” vs. ” + Home + “, ” + GameLevel +
“\n\nAppearance: ” + Appearance +
“\nAttitude: ” + Attitude +
“\nPregame: ” + Pregame +
“\nRules: ” + Rules +
“\nMechanics: ” + Mechanics +
“\nCommunication: ” + Communication +
“\nHustle: ” + Hustle +
“\nPostgame: ” + Postgame +
“\n\nComments: ” + Comments +
“\n\n\nPlease take this evaluation CONSTRUCTIVELY, and not personally. It’s purpose is to make” +
“\nus all better officials!” +
“\n\n\n\n\n” + TimeStamp;
MailApp.sendEmail(EvaluateeEmail, subject,
emailBody);
}
Here is a PDF showing the spreadsheet that the code above is supposed to draw from: http://db.tt/mzz7FQiX
@Jesse:
How did you get it working?
Hi
I am new to this, in running this script, i am facing same problem as was faced by Julie Swedberg – September 14, 2011, could anyone help me out on this.
SO AWESOME! Thank You a million times over!!
Thanks!! i am very need it, and its work!!
Well, I was not able to get this script to work, but I was able to use Henrique Abreu’s FormEmailer. I had been hesitant to use it at first because I couldn’t understand the code. I finally decided to give it a shot and found out that I didn’t need to understand the code. It was so easy to use! Once I installed it into my spreadsheet, a box opened up and I was able to make my email exactly the way I wanted it. Woot!
Great script!
Is there a way to manualy run the script? I have made a form for signing up for a children songgroup. I want to give a confirmation of the signup after I manually have checked the entered information.
I am new to scripting in Google Apps, so I hope that anybody can advise.
Hi anyone has a sample google form that we can test the email comfirmation thanks
Thanks! It really works
If you have everything set up but are simply not receiving the emails, you might double check the eValues(X) for your email address. I got off in my count and was referencing the wrong field for mine, once I realized that… all worked! Thanks so much for this, much much easier than anything else I looked at.