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
- Send Confirmation Emails upon submission of Google Form…. » Open School Sites - [...] http://alamoxie.com/blog/tech-tips/sending-confirmation-emails-google-docs-form/ [...]
- Google app script – how to count number of google form response | Stackforum.com - [...] copied code from a tutorial. But need help to get the count of form responses. Here is how it…
- google docs horizontal line - Search Yours - […] Sending confirmation emails with a google docs form | alamoxie […]
- google docs horizontal line - Search Yours - […] Sending confirmation emails with a google docs form | alamoxie […]
- google docs order form - Search Yours - […] Sending confirmation emails with a google docs form | alamoxie […]
Thank you for the good writeup. It in fact was a amusement
account it. Look advanced to far added agreeable from you!
By the way, how could we communicate?
If you’re getting the Cannot read property “0″ from undefined error, be sure you put the script on the spreadsheet, not the form. That took me a long time to figure out that I was doing wrong.
i was wondering if i can send the email confirmation with an independent email account (not from google)
Thanks for the script
Thank you! Very helpful and easy solution.
Hi,
This is a great post. Thanks.
While the MailApp.sendEmail works great as described, I am finding that its advanced arguments do not allow for the use of an authorized alias as a from address, instead of the gmail address of the user owning the script.
The GMailApp.send email method allows for this, but when I try to use the GmailApp instead of the MailApp class, I get the error – ReferenceError: “GMailApp” is not defined.
Can you suggest what I can do here?
Thanks a ton!
I have set up triggers to send a confirmation email onForm submit…they are their, I save them, I do a test and they work, but then they stop working, if I go back in my triggers are either greyed out or gone. I add them again, save everything, exit, reopen immediately, they are still good. I go to my next form do the same thing, but when I return to the previous form my triggers are off again??? Why is this happening?
Thanks for your help!
Kristen
I have set up triggers to send a confirmation email onForm submit…they are their, I save them, I do a test and they work, but then they stop working, if I go back in my triggers are either greyed out or gone. I add them again, save everything, exit, reopen immediately, they are still good. I go to my next form do the same thing, but when I return to the previous form my triggers are off again??? Why is this happening? Any ideas? Very frustrating, as they work but then stop working for some unknown reason..
Thanks for your help!
Kristen
This will not work if I have set trigger “From form”
Problem is, I dont have option to set trigger from spreadsheet! Only from form.
How do you add more than one email address?
Hi
Thanks for this, I’ve set up the script as you described and it works perfectly, I do have one small query though that I hope you can help with. I wanted to send a new form but having the responses for the 2nd form going to a new sheet on the original google doc. I unlinked the first form and went to create a new form from scratch.
The script seems to have run itself again and produced a second responses sheet duplicating the data from the first form and has also sent out email confirmations for a second time.
Have I set something up wrong here? Can you not create new forms for the same google doc but just have the responses on different sheets?
I understand that you can only have one form linked at any one time to a doc but I was hoping to unlink and create new forms as I went along, is that possible?
Thanks!
Brilliant! Thanks, this provided just the solution that I was looking for.
While I’d love to add more “bells and whistles”, this certainly solves my immediately challenges with Google forms.
THANKS
Hi, this is a great script and it works perfectly. But can anyone explain how to modify it to include inline images? Or do I have to rewrite the script entirely using ‘function inlineImage’?
I like the valuable info you provide in your articles.
I will bookmark your blog and check again here frequently.
I’m quite certain I’ll learn a lot of new stuff right here!
Good luck for the next!
Is there a way to do this to include a unique confirmation number? That would also be recorded into the excel sheet that you have the form designated to go to…. I need to be able to tie a unique confirmation number with the exact information they input with the form… and then email them or simply display on the submission page the confirmation number… HELP!
Thank you! This was most useful and very easy to use.
Hello,
I keep getting a formemailer error: Your actual quota is: 80
It is below the warning threshold: 150
I do not know how to fix … can someone please, please help? I have tried deleting the form information (I would uninstall the formemailer but the directions posted on Henri’s page do not work as no delete option shows in the file menu>
Thanks!
Hello
Thanks for the tutorial
You say ““e.values[1]” will be the first field in your form, and so on.”
Actually, e.values[1] is the first non-blank field of the form. Which can cause problem if some fields remain unfilled. Is there a way to fix this ?
Thanks
Hello,
The instruction seems straightforward enough, but when troubleshooting the script I get the following error message: “TypeError: Unable to read the property “values” from undefined. (Line 2, file: Code)”.
What does this mean and how can this error be fixed?
Emma–make sure that you are testing the script by setting up the trigger and submitting the form, so the form row data (“e”) is passed to the function. Do not run it directly from the code view; it will have no input, no “e” to use.
I have the same problem as Emma and I can’t solve it with your explination. The form is saved.
The current project’s triggers are set as followed:
Run: onFormSubmit
Events: fromSpreadsheet on Form Submit
Notifications: via e-mail at ********@kvk.be immediately
When I click on run I have the same message as Emma and I receive following mail:
Your script, Bevestigingsmail, has recently failed to finish successfully. A summary of the failure(s) is shown below. To configure the triggers for this script, or change your setting for receiving future failure notifications, click here.
The script is used by the document KVK Ind**********tage (krok*****ie) (Reacties).
Details:
Start Function Error Message Trigger End
12/18/14 3:58 PM onFormSubmit Invalid email: f (line 21, file “Code”) formSubmit 12/18/14 3:58 PM
Sincerely,
Google Apps Script
Need help? Visit the Google Apps Script documentation. Please do not reply to this message. (c) 2014 Google
Pieter, take note of the error message ” Invalid email: f (line 21″
I can’t say without seeing your code, but it sounds like there’s an error on the MailApp.sendEmail line because the “toAddress” is invalid. In my code sample that address is the second form field, but it may be different for you depending on what you changed. Can you make sure that a good email address is being entered, and that it is taking the correct value for the email address?
That was a mistake… I realised it after posting the problem.
Now I solved that. I have reduced to code to the basic information in order to test if everything works:
function myFunction(e) {
var toAddress = e.values[9];
var subject = “CAP Confirmation”
var message = “Thank you”
MailApp.sendEmail(toAddress, subject, message)}
– No longer errormails from google so that’s already ok
– The confirmation mail doesn’t arrive at the ‘toAddress’ in cells J2, J3, J4 (so the ‘9’ is correct I think). So there is still something wrong…
Could it be problematic that I don’t use a gmail adress? I’m registred with an @kvk.be adress…
Since I don’t have your exact script, it’s hard to say what exactly could go wrong.
Although you need to use a Gmail account to be able to make the form and edit the script in Google Docs, it doesn’t matter what email address you are sending notification emails to.
Here are some possible debugging steps:
1. Try sending to a different email address you can check.
2. Check the spam folder; make sure the notification email didn’t end up in there for some reason.
3. Start with the exact script in the tutorial. Make sure that works. Then change it, little by little, testing each time. If it stops working, you know it was the most recent change that broke it.
4. Simplify your script even further. In your example, you are using the 9th form field as the email address. If you are asking each user to input 9 values, it’s already far more complex than my example. Try to make the script very simple, make sure it works, then add extra features, testing each time.
5. Try a static email address. Instead of var toAddress = e.values[9]; try var toAddress = “example@example.com” (substituting one of your email addresses) and see if it works–then try to make it work using a form field. Note: if you can get it to send ANY email, then you can insert different form fields in the email for testing purposes.
Hope this helps!
check this out.
first email work well, but i am struggling with the approval process.
https://www.youtube.com/watch?v=eduG0XZ9mDs
hope this helps.
The mail works! Thanks for the help!
Only one problem left: some variabels asked in the form are not requirend to enter. I’m looking for a code to detect if a variable was not entered, and if so, to give it the stringvalue “Blanco”.
So an example: A second phone number is not required in my form:
if phone2 = empty, then phone2 = “blank”. Or something like that.
I tried 4 different things but nothing works… Whats the correct way to do that?
var secmailadres = e.values[11];
var telefoonthuis = e.values[13];
var GSM2 = e.values[15];
var opmerkingen = e.values[34];
if (secmailadres == ”)
{
secmailadres = “blanco”
};
if (telefoonthuis == undefined)
{
telefoonthuis = “blanco”
};
if (GSM2 == “”)
{
GSM2 = “blanco”
};
if (opmerkingen == null)
{
opmerkingen = “blanco”
};
I don’t know offhand, but according to http://stackoverflow.com/questions/6003884/how-do-i-check-for-null-values-in-javascript you might try tests like
if (!secmailadres) {
secmailadres = “blanco”
};
Thank u sir. It is very much help full
Thank you for taking the time to post this. I would love to copy and use your script however… I don’t know which values to replace. Is it possible to show the script with the values a person would need to change in a different color like red? Also a little info on what the text represents… values 1-5… are those referring to row 1, 2, 3, 4 and 5? thank you.
Hi,
And first of all thank you very much for this! it’s awesome for non programmers to find these types of resources!
And now… my problem.. when I try to save the script I get a syntax error message for my line 35 :
var htmlBody = “Thank you for contacting the HPELC Parents Council on ” + timestamp +
Basically, nowhere on the code will the code “htmlBody” turn blue… none of the 3 occurences… and I can’t figure out why… any ideas?
Many thanks!
Hi,
And first of all thank you very much for this! it’s awesome for non programmers to find these types of resources!
And now… my problem.. when I try to save the script I get a syntax error message for my line 35 :
var htmlBody = “Thank you for contacting the Parents Council on ” + timestamp +
Basically, nowhere on the code will the code “htmlBody” turn blue… none of the 3 occurences… and I can’t figure out why… any ideas?
Many thanks!
I still haven’t figured out what happened, I rewrote the code and suddently it worked!
Thanks!
I need to have leading zeros included in some of my fields (if the user enters them, not by default) but it appears that they are automatically stripped.
Any ideas on how I can maintain the exact user input?
Thanks!
Thank you for posting this excellent article.
I was hoping you could help me with a slight challenge?
I have added several columns to my Forms response spreadsheet.
I use these to do some calculations, etc.
For example, if the user enters “8” as a response to the 1st form question and then enters “10” as a response to the 2nd form question, I have inserted a column AFTER the 3rd column that calculates this decimal value i.e., 8/10 or 0.8.
Here’s my problem…
I cannot access the column value that I inserted as the 4th column.
The tutorial you so kindly presented allows me to only access columns numbered in the order that the user responds to the questions.
So if I try to access the “0.8” in the 4th column using something like
var Decimal = e.values[3] (meaning the 4th column, since Timestamp = e.values[0])
I get the user’s response to the form’s third question, not the calculated value I am trying to pull from the spreadsheet.
I apologize in advance.
I’m just a dumb math teacher, trying to create a grade report email notification for my students. I’m not very familiar with Javascripting in Google. Perhaps it has something to do with the “getCell(row, column)” function? But I can’t begin to know how to use this in the response form.
If you can offer some suggestions for how I can do this or possible workarounds, I would greatly appreciate it.
Thank you kindly for your help.
Hi, I’m running in to a bit of a problem here. I’ve used much of the basic code, added my own form.
But now it gives me an error ” Can’t read ‘values’ from undefined, (line 2, file”) ”
I’ve added a screenshot (Dutch) in this link https://drive.google.com/file/d/0Bwv5jspNMv3UTU1wYXd3X05RTkE/view?usp=sharing
Can someone please give me a hand on this problem?
Also I have a hard time figuring out how to set an BCC for this mail..
Thank you
Thank You. Everything works fine!
With best regards, Alex.
How do I delay the mail by few minutes? I have five columns in my spreadsheet.
0) Timestamp
1) Name
2) Mobile number
3) Email
4) ID
ID column contains formula to generate ID based on the time stamp. Hence when the user submits a form, based on the timestamp registered in the spreadsheet, formula will generate the number. However, the mail that the user receives, does not contain that ID – it says “undefined”.
I use copydown add on to copy the formula down the row. I get every detail in the mail except ID. Any sugesstions?
For Kaj and others that had the problem with the values being undefined…I had this issue as well. The problem was that I created the script from the form instead of the spreadsheet. Choose Tools –> Script Editor…from the Spreadsheet (Responses) and you should be okay.
My apologies for the late replies.
John:
Have you considered calculating the value in the code? For example:
var correct = e.values[2];
var maxScore = e.values[3];
var grade = correct / maxScore;
Kaj:
make sure that you are testing the script by setting up the trigger and submitting the form, so the form row data (“e”) is passed to the function. Do not run it directly from the code view; it will have no input, no “e” to use.
For bcc, note what I’ve written in the post about optAdvancedArgs and the link to the MailApp script reference.
Piyush:
You could add a formula to your spreadsheet to calculate the ID based on the timestamp. However, that data would not be available when you send the email on form submission.
Have you considered calculating an ID in the script, getting the current time programmatically as the script runs?
Hi!
Is there a possibility to send the confirmation mail with another sender email-address than the one from main google account? I’ve attached several email addresses to my gmail account.
Thanks,
Markus
HI,
I’m searching to send by mail the link to modify the reponse … any idee ?
Markus, as far as I know there is no way to do that–the account containing the script is the one that sends the email.
BH, that’s much more complicated than it sounds. Google Forms are designed to anonymously add entries to a spreadsheet you control. You could share edit access to the entire spreadsheet with someone, but not for just a specific row of the spreadsheet.I mean, you could import just that row into a separate spreadsheet and share that spreadsheet with someone, but I’m sure that’s not what you want. I don’t think Google Forms has the capability to modify a response once submitted.
normally, you can … http://webapps.stackexchange.com/questions/89551/show-url-used-to-edit-responses-from-a-google-form-in-a-google-spreadsheet-by-us
I suggest ‘Form Confirmation Emails’ add-on (developed based on Form Notifications code) as the other solution for confirmation and notification emails. It supports Html formatting for email’s content. So, you can setup the professional email with the banner, link, and any submission’s answers as you want. You can choose send or not based on respondent’s multi-choices and select list answers. The add-on also let you and your form’s collaborator set up the sending configurations separately.
hi!
i keep getting the error message “cannot read property “values” from undefined”.
i know that i wrote the script properly. it is bound the the spreadsheet, not the form. my triggers are properly set up. i thought that maybe it just wasn’t running bc ‘e’ isn’t defined until the script is actually triggered, and can’t be run through script editor, but when i test the form the script doesn’t run and i do not get the emails it’s supposed to send me.
what’s happening? is there something super tiny i’m missing?