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.
246 Comments
Trackbacks/Pingbacks
- Forms confirmation | Info007cleanin - [...] Sending Confirmation Emails with a Google Docs Form | AlamoxieJun 18, 2011 … One surprising lack is that of…
- Project 2, Day 1 – Codecademy and Javascript | An Hour Each Day - [...] no quick and easy way to do this in Google Docs forms, but there’s a great little tutorial here…
Everything is working great! Thanks so much for this. The only thing I am having an issue with is the \n command. When the email shows up, I don’t see the line breaks. It’s all just mashed in on one line. Any thoughts about what I may be doing wrong? Here’s what I have in the email.
function onFormSubmit(e) {
var timestamp = e.values[0];
var firstName = e.values[1];
var lastName = e.values[2];
var toaddress = e.values[3];
var position = e.values[4];
var subject = ” Application Confirmation – ” + position;
var emailBody = “Hi there,” + firstName + “! ” +
“\n Thank you for your interest in the “+ position + ” position! We will have someone review your application as soon as possible, and we’ll be in touch. ” +
“\n\n Sincerely, ” +
“\n Your Friendly Management Team!”;
var htmlBody = “Hi there,” + firstName + “! ” +
“\n Thank you for your interest in the “+ position + ” position! We will have someone review your application as soon as possible, and we’ll be in touch. ” +
“\n\n Sincerely, ” +
“\n Your Friendly Management Team!”;
var optAdvancedArgs = {name: “Application”, htmlBody: htmlBody};
MailApp.sendEmail (toaddress, subject,
emailBody, optAdvancedArgs);
I figured it out! Since the second part is HTML, I had to add to the code, which I somehow missed. I think I just accidentally overwrote it when I was editing the script. I knew it was my own mistake.
Thanks a ton for this!! I checked out a few other posts on how to accomplish this that were WAY more complicated than necessary. This worked great!!
Does anyone know if it’s possible to send a form to more than one email address? How would I go about doing this? I want to send the email to both myself plus three emails that will be inputted in the form. Thoughts?
Thanks!
Hi,
I just wanted to thank you for this fantastic script and fantastic tutorial. This was incredibly helpful!
-Ollie
To everyone who liked the tutorial–you’re very welcome! I’m quite pleased that it’s been helpful. Eli: yes, you can send a form to more than one email address. There is only one “to” address, but you can add as many cc and bcc addresses as you like. They’re part of the optAdvancedArgs. See this reference for more information on those and other parameters: https://developers.google.com/apps-script/class_mailapp
This is fantastic.
The function is very straightforward and the documentation you have provided is quite clear.
You might want to mention (for non google script users) that there are notifications that you can turn on to capture errors and forward them to an email address.
When viewing the triggers, select notifications. Select an email address to notify and select ‘immediately’ from the frequency box.
That helps debug these types of scripts that have no interface or require user input.
This is the best think I’ve read all week, THANK YOU.
Months I’ve been looking for this code, wishing I was clever enough to write it myself.
Just to add you can send To multiple people buy using comma delimited format within quotes such as:-
MailApp.sendEmail(“person1@gmail.com,person2@gmail.com”, subject, emailBody, optAdvancedArgs);
Thank you for a super easy to follow tutorial. I don’t know diddly squat about HTML or programing. And I was able to get it done on Attempt #1. Kudos!
good day, I am trying to get this script running, and when I click run, I get the error:
“TypeError: Cannot read property “values” from undefined. (line 3)”
Thanks Kunal, I appreciate the kind words!
John, are you trying to run the code directly? That won’t work. To run properly, the code needs a set of values passed to it… the values that were entered in the form fields. To test, follow the instructions and try submitting the form. Hope this helps!
Thank you, It works very well.
Hi
When I copy the and edit it into script editor and run it this problem is show:
“TypeError: Cannot read property “values” from undefined. (line 2)”
Why there is such a problem?
Sounds like the same problem John had a few days ago–you don’t run this code directly, you set it up as the instructions direct and then it runs when the form is submitted. You can’t run it directly because it needs the “values” values from the submitted form.
I just tested this by sending myself the email and it goes straight into the Spam folder…the code works wonderful for sending (thank you for posting/sharing this), BUT having a hard time figuring out how to avoid it going to the Spam folder. Any ideas to avoid this?
The subject has “[website name] Here’s Your FREE BOOK!” and the Body is only about 3 lines with a link for the recipient to download the free book.
I suppose I could redirect the submitter to a new Thank You page and warn/ask them to check their spam folder for the email, but I’m afraid this would not give a good impression to the submitter (my potential customer)… Any ideas are welcome!
Al, the problem with going into the spam folder isn’t due to the script. It COULD have to do with your mail settings–setting up DKIM authentication or SPF may help. It might be that other sites on the same IP for your shared hosting provider are such grievous offenders that emails from your domain are marked as spam. But most likely, it’s because the content of your email looks like spam, especially with “FREE.” First thing to try it sending basic test messages and see if they go through or go to spam.
Hey,
Thanks for this information. I have been trying to get this code to work for a while now but to no avail. Can someone please take a look and attempt to help me identify what I may be doing wrong here?
I appreciate all the help I can get.
function onFormSubmit(e) {
var timestamp = e.values[0];
var firstnamelastname = e.values[1];
var description = e.values[2];
var toaddress = e.values[3];
var urgency = e.values[4]
var subject = “ESC Helpdesk Confirmation ”
var emailBody = “Hello,” + ” firstnamelastname ” +
“\n Thank you for contacting the ESC Helpdesk. We are reviewing your ticket and will respond as soon as possible. A review of your submittion is below.” +
“\nYour Name: ” + firstnamelastname +
“\nYour Email: ” + toaddress +
“\nDescription: ” + description +
“\nUrgency ” + urgency +
“\n\n Sincerely, ” +
“\n ESC Technology Department!”;
This is wonderful, thank you! Quick question: do you know how I would add a hyperlink to the email confirmation body, which is displayed as text? I have been looking all over the web and haven’t found the answer.
Sure Rachel–text emails will not have clickable links, but you could always just write out the link for people to copy and paste into a browser. Or you can specify the optional HTML email body… then you would write out the HTML for the link as you normally would on the web page. 🙂 Hope that helps!
Scott, it’s hard to tell how to fix your problem unless you say what exactly the problem is–are you getting an error message of some kind? Does it appear to run successfully, but you never receive an email? In general, the most common problems and solutions are:
(1) make sure each line ends with a semicolon
(2) test it by submitting a form–you can’t run it directly
(3) follow the instructions exactly… make sure you set up the trigger
(4) make sure the email is not going to spam
Hope this helps!
Thanks for the response here.
I am getting a syntax error that will not allow me to save the form. It is appearing on line 8 that starts with var emailBody = “Hello,” + ” firstnamelastname ” +. I tried adding the semicolon to that line and it still gives me the error.
Thanks again for the help.
Did you add semicolons to the two lines before? Does the syntax error give you a specific message?
As always with debugging, try to narrow down the problem… experiment with that line or the lines before, seeing if it still gives you the error with very simple lines–by testing part of the line at a time you can find out exactly where the issue is.
I have tried to place the semi colons and different places and the syntax errors are gone but I am still getting an error that says Illegal Character on the “Thank you for” line. I cant debug the script because it wont let me save it…..
function onFormSubmit(e) {
var timestamp = e.values[0];
var firstnamelastname = e.values[1];
var description = e.values[2];
var toaddress = e.values[4];
var urgency = e.values[3];
var subject = “ESC Helpdesk Confirmation” ;
“\nThank you contacting the ESC Helpdesk. We are reviewing your ticket and will respond as soon as possible. A review of your submittion is below.” +
“\nYour Name: ” + firstnamelastname +
“\nYour Email: ” + toaddress +
“\nDescription: ” + description +
“\nUrgency ” + urgency +
“\n\n Sincerely, ” +
“\n ESC Technology Department!”;
Scott, you need to add “var emailBody =” to the beginning of the line with the error–you’re describing a string, but not assigning it to a variable
Thanks, I had that in there before but took it out to try and eliminate the illegal character message. I am still receiving that error and cannot save. I am sorry to bother but I really need to get this up and running.
Hello,
Was looking for something like this, have not tried it yet but I do not see how users can get a confirmation if they are not logged in to a Google account or is it they have to be logged in?
Google Apps has some automatically built in features for people who are logged into Google accounts for a specific domain (www.whatever.com) and are filling out a form for that domain–this has nothing to do with that. Go ahead and try out the code, I think you will be pleasantly surprised. 🙂 In the form, the user enters their email address; the code sends an email to that email address. So no, the user filling out the form does not need a Google account.
Well I set it up and am not getting any emails once the form is submit..any ideas where I went wrong?
https://docs.google.com/spreadsheet/viewform?formkey=dEtYRV9NQVY3TGdPRzUtOEtOS280OXc6MQ#gid=0
function onFormSubmit(e) {
var Timestamp = e.values[0];
var CallSign = e.values[1];
var EAddress = e.values[2];
var Cluster = e.values[3];
var Activity = e.values[4];
var Comments = e.values[5];
var subject = “WM Roll Call Confirmation ” ;
var emailBody = “Keep up the great work Warrior your entry has been confirmed! ” + Timestamp +
“\n\nThe details you entered were as follows: ” +
“\nYour CallSign: ” + Callsign +
“\nYour Email: ” + EAddress +
“\nAssigned Cluster: ” + Cluster +
“\nActivity: ” + Activity +
“\nComments: ” + Comments;
var htmlBody = “Thank you for reporting in Warrior ” + Timestamp +
“ The details you entered were as follows: ” +
“Your CallSign: ” + CallSign +
“Your Email: ” + EAddress +
“Your Cluster: ” + Cluster +
“Activity Level: ” + Activity +
“Your Comments: ” + Comments;
var optAdvancedArgs = {name: “WM Roll Call”, htmlBody: htmlBody};
MailApp.sendEmail(toAddress, subject,
emailBody, optAdvancedArgs);
}
I am no expert here but wouldn’t you want to refer to the Email address as “EAddress” as opposed to “toAddress” in your MailApp statement?
Thanks Scott, missed that one..but it did not fix. Still not getting a email once the form is submit.
I just wanted to say that this worked out perfectly after some adjustments! The original poster was very helpful!!!!
Thanks so much for this!
Hi and thanks for the info.
I have one problem though. I specify the advanced arguments “name” and “noReply”, but these seems to be ignored.
var additonalParams = {
name:”Trondhjems Kameraklubb”,
noReply:true,
htmlBody:htmlbody
};
The sender is always my own e-mail address. The script was originally created by me, but I’ve transferred the ownership of the form to another account, which I now use for editing. However, the originator of the e-mails are still my original address. So somehow, the script still seems to be running in the context of my private user. (I also see this by the update information on another document which is updated by the script).
So I guess what I need is a way to set the user running the script when initiated from the form.
Any help is appreciated. Thx.
Cheers – found this really useful for a beginner programmer!
Found this very helpful, thank you.
One question, I have created a form that on submit it emails various people the responses from the questions I have entered on the form.
The form is built based on rules, so if someone answers one way it will drive a different question than if they answered differently.
When the email is sent this means that there could be some blank cells in the email. Is there a way to bold each answer separately without it appearing on a sep line? As an example:
Code as follows:
“\nSegment: “+ Newsegment +””+
“\nBudget Owner: “+ BudgetOwner +””+
“\nBusiness Controller: “+ BC +””+
returns in the email:
Segment:
Entity
Budget Owner:
Business Controller:
Any help much appreciated.
Have solved this by putting b before and /b after.
thanks for the help anyhow.
jsetsaas, did you resolve your advanced arguments issue? Make sure you are using straight quotes around the “name” parameter, not curly quotes–other than that, it looks like it should work. The html body parameter works?
You may have been having this issue, which should be fixed in Google Apps now: http://code.google.com/p/google-apps-script-issues/issues/detail?id=2004
Also note that different email services and even different accounts in those email services may or may not show the name you set in the “from.” Test your own account versus a test account, Gmail versus Yahoo, etc.
Unfortunately, you might not always be able to count on the “name” part showing up correctly.
“noReply” should work, however (tip for everyone: make sure “true” is in all lowercase). The email should show as sent from “noreply@yourdomain.com” where it uses the domain where your Google Apps account is hosted.
As for the ownership problems, I don’t know for sure… but you may need to add the script and trigger under the other user account. You may have transferred ownership of the doc, but not the script. Hope this helps!
Thank you so much for sharing this – it will save so much hassle.
Not getting the email and can’t figure out why. Please help. Not getting any error messages. You can see the form in the link above. Thanks heaps for your help.
function onFormSubmit(e) {
var timestamp = e.values[0];
var category = e.values[1];
var Authorname = e.values[2];
var Website = e.values[3];
var Email = e.values[4];
var Storytitle = e.values[5];
var Enteryourstoryhere = e.values[6];
var Iamtheauthorofthisstory = e.values[7];
var Iconfirmthatthisstoryisnotcopyrighted = e.values[8];
var Mystorywillnowbecomeapartofthepublicdomain = e.values[9];
var subject = “Skinny Fiction Story Submission Confirmation – ” + yourauthorname + ” ” + enteryourstoryhere;
var emailBody = “Thank you for your entering your story into Skinny Fiction submitted on ” + timestamp +
“\n\nThe details you entered were as follows: ” +
“\nCategory: ” + category +
“\nAuthor Name: ” + Authorname +
“\nWebsite: ” + Website +
“\nEmail: ” + Email +
“\nStory title: ” + Storytitle +
“\nEnter your story here: ” + Enteryourstoryhere +
“\nI am the author of this story: ” + Iamtheauthorofthisstory;
“\nThis story is not copyrighted: ” + Thisstoryisnotcopyrighted;
“\nMy story will now become a part of the public domain: ” + Mystorywillnowbecomeapartofthepublicdomain;
var htmlBody = “Thank you for submitting your Skinny Fiction story on ” + timestamp + “ The details you entered were as follows: ” +
“Author Name: ” + authorname +
“Please confirm your submission by forwarding this email to gmt369@gmail.com”
“Your Email: ” + email;
MailApp.sendEmail(email, subject,
emailBody);
}
I have one question that has taken days of searching with no result.
PLEASE help me out and I will be forever grateful.
I have the email working and it looks perfect, thank you for taking the time to share. I just need to know how to send the email with the CC field filled out with the agencyEmail. Code is below, again I will be forever in your debt if someone can give me the code I need to get passed this.
function onFormSubmit(e) {
var timestamp = e.values[0];
var firstName = e.values[1];
var lastName = e.values[2];
var phoneNumber = e.values[3];
var email = e.values[4];
var classDate = e.values[7];
var agencyEmail = e.values[10];
var subject = “Class Registration Confirmation – ” + firstName + ” ” + lastName;
var emailBody = “Thank you for Registering with My Company for an education class on ” + classDate +
“\n\nStudent Name: ” + firstName + ” ” + lastName +
“\n\nIf you did not register for this class, please contact your Care Agency to verify if they registered on your behalf.” ;
MailApp.sendEmail(email, subject,
emailBody);
}
Never mind, I actually just figured it out. HURRAY!
Rachel – glad you figured it out! Would you mind sharing your solution in case others have the same problem?
Gary – there are a few things for you to check. First, make sure you have the trigger set up properly, and that it’s configured to inform you immediately of script failures. Then try sending it to several different emails and see if it ends up in your spam folder. Try those and let me know what you find out.
Oh my giddy god! I finally got it to work. The devil is definitely in the details, and there are so many of them it’s not funny. Anyway, thank you for your advice and great code. I’m off and running now. Phew.
Here was my solution to attached an email as the CC:
function onFormSubmit(e) {
var timestamp = e.values[0];
var firstName = e.values[1];
var lastName = e.values[2];
var phoneNumber = e.values[3];
var email = e.values[4];
var classDate = e.values[7];
var agencyEmail = e.values[8];
var replyTo = “replyto@ourdomain.com”;
var subject = “Class Registration Confirmation – ” + firstName + ” ” + lastName;
var emailBody = “Thank you for Registering with My Company for an education class on ” + classDate +
“\n\nStudent Name: ” + firstName + ” ” + lastName +
“\n\nIf you did not register for this class, please contact your Care Agency to verify if they registered on your behalf.” ;
MailApp.sendEmail(email, subject,
emailBody, {cc: agencyEmail, replyTo: replyTo});
}
This was a great article, very much appreciative and can’t wait to put it into application! However, I’m trying to figure out how I can possibly do this with multiple pages in the form.
Basically, we have it so our form redirects to pages based on the type of request we have. So what I’d like to create is an IF function in here that will email specific columns based on the output of the type of request (which is column 1).
So basically something like this in regards to logic:
IF user selects request 1
DISPLAY Columns 1 2 3
IF user selects request 2
DISPLAY Columns 4 5 6
Can anyone help me out with this? Really appreciate it in advance!
Just to follow up… requests would be drop downs in our form, so they are specific outputs of a form field.
First, you would use the “multiple choice” question option in the form. That allows you to specify, in the form, to show different pages based on what they answer. The drop-down list doesn’t sem to offer that functionality.
Then you would, in your code, use an IF statement based on the value of the multiple choice column to decide which column values to put into the email.
Does that make sense? Perhaps I’m misunderstanding your question.
-Chad