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.
I keep getting the following error message. Anyone know why I would be getting it? I have been unable to resolve it. My script was working great for a while. You can see it by scrolling up a bit.
I forgot to paste the error message. Here it is: Unknown macro onFormSubmit
Anj, I’m afraid I don’t have much of anything helpful to suggest. I think at one point I had managed a really awkward, complex workaround where I imported the data from the one spreadsheet into another spreadsheet, where some of the columns were prefilled with formulas to calculate data based on that incoming form data, but it was very fragile and buggy. I don’t recommend that.
You want to include this auto-incremented value in the email–why not combine the scripts? In one script, calculate the ID and add it to the email, then add it to the sheet–or to a separate sheet if necessary.
Thanks, LM! Appreciate it.
What I find strange, Donnie, is that your script would work at first and then stop working. Try completely deleting it (saving the text of the script somewhere safe) and then going through the blog post instructions again from the beginning, pasting in your old script. See if that works.
What if the user does not enter any email address? Is is possible to anyhow find out such persons email address (even manually) and send that person an email?
Nisarg, I’m afraid not… Google Forms were designed with anonymous surveys in mind. You don’t get to see the user’s name, email, IP address, nothing identifying–unless you add form fields and they fill them in. You can make the email field required, but that wouldn’t stop them from entering an invalid email address.
While I have been using a script like this for some of my clients, they often hit the ‘reply’ on their email – which sends me the message (having created the form) and not the person who submitted the form.
Are you able to share how to put the users email (they entered in the form) into some sort of ‘reply to’ so when a message comes they can hit ‘reply’?
Thanks!
I simply wanted to say thanks once more. I am not sure the things that I would have accomplished without the entire strategies documented by you directly on such situation. It was before a real alarming case in my opinion, however , spending time with your expert manner you managed it made me to weep with joy. I am just thankful for this advice and in addition sincerely hope you recognize what an amazing job you are always doing instructing others using a blog. Probably you haven’t got to know any of us.
| Reasonable Juicer Reviews Solutions – Some Basic Tips
I am wondering if it is possible to include within the confirmation email the link that a respondent can use to edit their responses. I have a long form that users may want to complete in two separate sessions. Expecting users to copy and save a link and then remember to use that link instead of the original email invitation to the form seems problematic. It would be fantastic if I could automatically send them an email with the edit link.
Ant insight would be appreciated.
Thanks,
Mark
Sure, Scott. See https://developers.google.com/apps-script/reference/mail/mail-app. You can just add “replyTo” as one of the arguments to optAdvancedArgs, in the same way that the tutorial shows how to add name and htmlBody.
Mark, the new feature showing people a link to edit their responses is handy–but you’re right, not very useful since there seems to be no way to find that link again once the window is closed. Unfortunately, I don’t know of a way to find that link in Google Apps Script so it can be emailed. If anyone else finds an answer, I hope they will add it here!
Thanks for the reply. It is unfortunate that link is only made available to Google Apps users. Apparently the confirmation email that they can receive through simple configuration settings can be set to include the response link for editing…so Google is aware that the link is valuable…
Thanks,
Mark
A point of clarification–if I recall correctly, Google Apps users can easily edit their response for forms used within their own domain, yes. So that’s useful for forms within a company. That feature has been around for a while.
However, just within the past few weeks Google Docs changed the UI for creating/editing forms–for everyone, not just Google Apps users. When you create a form, at the very bottom you can set a confirmation message and set several checkboxes. One of them says “Allow responders to edit responses after submitting”. If that box is checked, then users that submit a form will see a link to edit their form answers. They can manually save/copy/bookmark that link for later use, but not many users will do that. So the functionality is there, but it’s not as intuitive as convenient as it should be–and as I said, I unfortunately don’t know of a way to programmatically retrieve/generate that link in Google Apps Script. Hopefully this is a feature that will be improved upon soon!
Thanks for the reply but is still isn’t putting the Reply To address (the users entered email) in my ‘reply to’ on Gmail.
The email turns up with the users data fine but if I hit reply it is still sending the reply to my address, not the users entered address.
What’s strange is my gmail shows the following when you click the little down arrow next to the words ‘to me’ in the email header
from: me@gmail.com
reply-to: user@user.com.au
to: me@gmail.com
date: 24 April 2013 16:32
subject: Contact about: website
mailed-by: maestro.bounces.google.com
Signed by: gmail.com
yet, as I say, if I click ‘reply’ it creates a message for me@gamil.com not user@user.com
can you see anything wrong with this script?
function onFormSubmit(e) {
var timestamp = e.values[0];
var name = e.values[1];
var email = e.values[2];
var subject = e.values[3];
var question = e.values[4];
var to = “xxxx@gmail.com”;
var replyTo = email;
var subject = “Contact about: ” + subject;
var message = “\n\nName: ” + name +
“\n\nEmail: ” + email +
“\n\nSubject?: ” + subject +
“\n\nComment: ” + question;
MailApp.sendEmail(to, replyTo, subject, message);
}
Thanks!
Scott, I’m afraid I don’t see any obvious problem from what you’ve shown here. It looks like your script is correct. If the mail client is showing the correct reply-to address, then when you click reply it should be using the reply-to address. Try some different email accounts to experiment with, and make sure the email variable is correct–since you have it showing “Email: ” and the email whenever it sends out and email, that should be easy. I don’t see any obvious problem however. You can also check the mail headers, at least in Gmail, by clicking the drop-down arrow in the upper right corner and clicking “Show original”
I ran into this exact situation a couple of months back when creating an online order form (using the example from this site thank you very much). Google will no longer allow you to use a reply-to address that isn’t in your owned domain (EG: you can’t use gmail.com because you don’t own it). You can set your reply-to to anything you like, but it’s always going to actually reply to your address or an address you specify in your owned domain. If you’re creating your script for someone else, you need to create it using a reply-to address in their domain so you can hand it off to them when you’re finished (I’m speculating a bit, but that was the gist of my understanding as I actually haven’t done this yet).
HTH, and I’m very interested to learn how you eventually solve it.
ok, it’s not my script, great!
and this has provided some more to think about, Great!
I just tried using me@mydomain.com in the ‘to’ and now, even though that email is directed to a gmail address, when I hit reply to a received form submission it wants to send the reply to me@mydomain.com (even though, as before, replyTo in the message header says userentered@user.com).
So, its definitely adding the ‘to’ as the reply to, and the user entered email is definately regisering tin the replyTo field, though not working in practice, unfortunately.
It does not matter if I build this in the clients Google account as when they receive a form submission and then hit reply they will be sending the reply to them self, not the user.
Perhaps I’m at the point of needing a better form submission solution as Google just behind the times with this ‘simple’ feature.
Any alternatives? I am using Blogger as the websites CMS
Thanks for your replies people. Appreciate it.
Hi, Can anyone help me to create a script in google documents form for sending a confirmation email along with the Reference number for every submission of a form….
I am getting an error in line 17 and then in line 27
i don’t understand why those Vars aren’t working. any chance you could help?
thanks,
mark
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
function onFormSubmit(e) {
var timestamp = e.values[0];
var firstName = e.values[1];
var lastName = e.values[2];
var toAddress = e.values[3];
var Organization = e.values[4];
var phoneNumber = e.values[5];
var subject = “Marin EdCamp Confirmation – ” + firstName + ” ” + lastName +
var emailBody = “Thank you for your Marin EdCamp Registration submitted on ” + timestamp +
“\n\nThe details you entered were as follows: ” +
“\nYour Name: ” + firstName + ” ” + lastName +
“\nYour Email: ” + toAddress +
“\nOrganization: ” + Organization +
var htmlBody = “Thank you for your Marin EdCamp registration submitted on ” + timestamp +
“ The details you entered were as follows: ” +
“Your Name: ” + yourName + ” ” + lastName +
“Your Email: ” + toAddress;
” We wil look forward to seeing you at Ross School on August 1
“~EdCampCommittee
var optAdvancedArgs = {name: “Marin EdCamp”, htmlBody: htmlBody};
MailApp.sendEmail(toAddress, subject,
emailBody, optAdvancedArgs);
}
You’re trying to concatenate on line 15 & 25 instead of closing those vars out with semi-colons. This:
var subject = “Marin EdCamp Confirmation – ” + firstName + ” ” + lastName +
var emailBody = “Thank you for your Marin EdCamp Registration submitted on ” + timestamp +
“\n\nThe details you entered were as follows: ” +
“\nYour Name: ” + firstName + ” ” + lastName +
“\nYour Email: ” + toAddress +
“\nOrganization: ” + Organization +
var htmlBody = “Thank you for your Marin EdCamp registration submitted on ” + timestamp +
Should be changed to this:
var subject = “Marin EdCamp Confirmation – ” + firstName + ” ” + lastName; /*changed “+” to “;” */
var emailBody = “Thank you for your Marin EdCamp Registration submitted on ” + timestamp +
“\n\nThe details you entered were as follows: ” +
“\nYour Name: ” + firstName + ” ” + lastName +
“\nYour Email: ” + toAddress +
“\nOrganization: ” + Organization; /*changed “+” to “;” */
var htmlBody = “Thank you for your Marin EdCamp registration submitted on ” + timestamp +
got it, thanks for the script.
Once i started using the Hoodia Cactus Slimming a few weeks ago, I dropped over fifteen lbs . and can now fit back into my skinny clothes. It is easier to take than other drugs i I have attempted because I take it with food, not wait 30 minutes before I take in.
Newbie Q: Do I supply the argument ‘e’? If I run from Script Editor it complains of undefined. If I submit a form via View Live form I don’t get an email.
Newbie A: e is supplied when triggered by a real submit so it only works live, not in the script editor.
Hi Chad –
This is an awesome script. Thank you!
I have a similar question that “Jeff” had a while back. You answered on Dec 22, 2012 with an example syntax for an If – Else statement, but I cannot access the example script you linked to to order to adapt to for my situation. Could you make the script live again so that I can see?
Right now I have…
var Delivery = e.values[6];
var Avail = e.values[7];
var Notes = e.values[8];
var Pickup = e.values[9];
if (Delivery == undefined){
throw “\n\nFor Delivery:” +
“\nDelivery Address: ” +
“\n” + Delivery +
“\nGood times for delivery: ” +
“\n” + Avail ;}
else {
throw “\n\nFor Pickup:” +
“\nGood times to pick up programs at the xyx Museum (Museum address): ” +
“\n” + Pickup ;}
Where there’s more emailbody before and after these statements…
The first problem I see is that you are calling “throw” — I think you would probably want to follow along with the example in the blog post and create a screen that you pass to the call to send an email. Other than that, what is the exact error you are seeing?
Hi there ~
I have a problem running the code, every time I try to run it, Google Spreadsheet tells me that ‘Cannot read property “values” from undefined. Upon running debug in Script editor, all of the vars are ‘undefined’. Right now, I have:
function onFormSubmit(e) {
var timestamp = e.values[0];
var lastName = e.values[1];
var firstName = e.values[2];
var primaryCommittee = e.values[3];
var secondaryCommittee = e.values[4];
var mobile = e.values[5];
var email = e.values[6];
var address1 = e.values[7];
var address2= e.values[8];
var city = e.vaues[9];
var state = e.values [10];
var postcode= e.values [11];
var emergencyContactPerson = e.values[12];
var relationToEmergencyContactPerson = e.values[13];
var emergencyContactNumber = e.values[14];
var subject = “Volunteer Information Confirmation – ” + firstName;
var emailBody = “Thank you for your submitting your Volunteer Information form on ” + timestamp +
“\n\nThe details you entered were as follows: ” +
“\nYour Name: ” + firstName + lastName +
“\nYour Email: ” + email +
“\nCommittee(s): ” + primaryCommittee + secondaryCommittee +
“\nAddress ” + address1 + address2 +
“\n: ” + city + state + postcode +
“\n Emergency Contact Person: ” + emergencyContactPerson
“\n Emergency Contact Number: ” +emergencyContactNumber;
MailApp.sendEmail(email, subject,
emailBody);
}
Which places did I go wrong?
Thanks,
Jon
as commented above, e only becomes defined when the script is triggered for real. it doesn’t work in the script editor.
I have deployed the codes and its working fine. When the email is triggered, it reflects sender name as “xxx” of email xxx@xyz.com . I want to put custom sender name like <> .. pls suggest the codes.
Pankaj, as the blog post states, you can use something like var optAdvancedArgs = {name: “Club Ambassador Program”, htmlBody: htmlBody}; where the “name” property sets the name of the from email address. You cannot change the from email address; it must be the email address running the script. You can set the reply-to property, as discussed in earlier comments, but only to another email on the same domain.
Hi,
I love the script!
Wanted to make a nice HTML email to send on form submission but am having trouble.
If found “Yet Another Mail Merge” info here: http://googleappsdeveloper.blogspot.nl/2011/10/4-ways-to-do-mail-merge-using-google.html
And the merge sends my draft HTML email perfectly to people in the spreadsheet. But…it’s manual. Is there anyway to combine the onformsubmit functionality of your script with the functionality of this one do you think?
Hello,
I tried to use this script as well, but it does not work, I always receive the message:‘Cannot read property “values” from undefined’ when someones makes a new entry to my form… Any idea what goes wrong?
I followed the instructions but it does not seem to send the email back. The only thing I noticed different is when you stated “The first field should be “onFormSubmit,” the second “From Spreadsheet” and the third “On form submit.” “, the second field for me must be “From form”
When I submit in IE, I captured:
Message: Object doesn’t support this property or method
Line: 17
Char: 2862
Code: 0
URI: http://mail.yimg.com/zz/combo?nq/templates/js/minty/module/toolbar/inbox/message-fresh_50_8.js&nq/comms/shared/src/ui/menu/js/menu_50_8.js&nq/templates/js/_shared/module/offscreen-bin/menus/loading_50_8.js&nq/intl/js/postlaunch/lang_en-CA-x-rogers_50_8.js&nq/intl/js/subcompose/lang_en-CA-x-rogers_50_8.js&nq/intl/js/compose/lang_en-CA-x-rogers_50_8.js&nq/intl/js/contacts/lang_en-CA-x-rogers_50_8.js&nq/intl/js/postlaunch_jp/lang_en-CA-x-rogers_50_8.js&nq/common/ui/menuhelper/menuhelper_50_8.js&nq/mail/ui/messagepane/warnings_50_8.js&nq/mail/ui/mailpane/base/mailread_50_8.js&nq/mail/ui/common/fonts_50_8.js&nq/mail/ui/mailpane/message/messagepaneread_50_8.js&nq/templates/js/_shared/module/open-mail/application-messagepane_50_8.js&nq/templates/js/_shared/module/open-mail/application-simple_50_8.js&nq/om/ui/views/simple/simple_50_8.js&nq/templates/js/_shared/module/open-mail/application-hidden_50_8.js&nq/om/ui/views/hidden/hidden_50_8.js&nq/om/common/utils/apiutils_50_8.js&nq/om/api/appdata_50_8.js&nq/templates/js/_shared/module/offscreen-bin/menus/openmail-extension_50_8.js&nq/om/api/menu_50_8.js&nq/om/api/event_50_8.js&nq/intl/js/openmail/lang_en-CA-x-rogers_50_8.js&nq/om/api/message_50_8.js&nq/om/controller/internalapi_50_8.js&nq/om/ui/views/folderpane/folderpane_50_8.js&nq/7136/async-queue/async-queue-min.js&nq/mail/common/cachewarmer_50_8.js&nq/7136/classnamemanager/classnamemanager-min.js&nq/7136/widget-base/widget-base-min.js&nq/7136/widget-base-ie/widget-base-ie-min.js&nq/7136/widget-htmlparser/widget-htmlparser-min.js&nq/7136/widget-skin/widget-skin-min.js&nq/7136/widget-uievents/widget-uievents-min.js&nq/7136/widget-position/widget-position-min.js&nq/7136/widget-position-constrain/widget-position-constrain-min.js&nq/7136/widget-stack/widget-stack-min.js&nq/comms/shared/src/tooltips/js/tooltips-widget_50_8.js&nq/7136/anim-base/anim-base-min.js&nq/7136/widget-anim/widget-anim-min.js&nq/templates/js/_shared/module/offscreen-bin/tooltip-fresh_50_8.js
Here is my script which I basically stripped down but it still does not work for me:
function onFormSubmit(e) {
var timestamp = e.values[0];
var firstName= e.values[1];
var lastName = e.values[2];
var emailAddress = e.values[3];
var subject = “Subject Line”;
var emailBody = “Thank you for registration submitted on ” + timestamp;
MailApp.sendEmail(emailAddress,subject,emailBody);
}
I got GoogleNotifications on my form failure and it’s the same error as some above, though I’m not triggering it from the “Run”, I am actually submitting my form.
TypeError: Cannot read property “0” from undefined. (line 2, file “Code”)
Hi
Thanks for the post ! very useful.
When ‘triggering’, I don’t have the form spreadsheet option for the second field. Just “FromForm”.
Is it important ?
I’m asking because I can’t get any email confirmation
Here’s my code. can anybody detect an error ?
function onFormSubmit(e) {
var timestamp = e.values[0];
var yourName = e.values[1];
var yourFirstName = e.values[2];
var toAddress = e.values[8];
var subject = “Inscription “;
var emailBody = “Bonjour ” + yourFirstName + yourName +
“\n\nVotre pré-inscription au club a bien été enregistrée ” +
“\n\nAfin de finaliser cette inscription, merci de bien vouloir fournir les pices suivantes au secrtariat du club ” +
“\nUne photo didentit” +
“\nUn certificat médical d’aptitude à la pratique de lultimate en comptition” +
“\nUne attestation de votre tuteur lgal si vous tes mineur” +
“\nCes trois ou deux documents doivent impérativement tre remis en mme temps” +
“\nMerci”;
var htmlBody = “Bonjour ” + yourFirstName + yourName +
“Votre pré-inscription au club a bien été enregistrée ” +
“Afin de finaliser cette inscription, merci de bien vouloir fournir les pièces suivantes au secrétariat du club ” +
“Une photo d’identité” +
“Un certificat médical d’aptitude à la pratique de l’ultimate en compétition” +
“Une attestation de votre tuteur légal si vous êtes mineur” +
“Ces trois (ou deux) documents doivent impérativement être remis en même temps” +
“Merci”;
MailApp.sendEmail(toAddress, subject,emailBody);
MailApp.sendEmail(toAddress, subject,
emailBody, optAdvancedArgs);
}
Thanks a lot for the help !
Actually, the same thing as AnotherJohn has happened to me.
Same error notification…
If you are getting this error: TypeError: Cannot read property “0″ from undefined. (line 2, file “Code”), your problem may be that you are trying to put the script on the FORM instead of on the SPREADSHEET that collects the data from the form.
Any idea why this code might not be working? It seems to be fine but it doesn’t send any email on form submission…
Tean number is the 11th column in the spreadsheet (including timestamp), email is 5th, region is 12th
function onFormSubmit(e) {
var teamNumber = e.values[10];
var toAddress = e.values[4];
var region = e.values[11];
var subject = “Tournament Registration”;
var emailBody = “Thank you for registering for the competition.” +
“\n\n You have indicated that you would like to register” + teamNumber + “teams in the” + region + “region.” +
“\n\n If you have any questions, please email us at ***.”;
MailApp.sendEmail(toAddress, subject,
emailBody, optAdvancedArgs);
}
Hi,
I followed your instructions exactly and simply copy-pasted the script you offered as an example into my script editor in Google, added the trigger, saved the project, and then filled out the form myself to test it. It did not send me an email. Not sure what I’m doing wrong.
My form has a text box for the user to enter their email address, but I noticed in your instructions above that is not necessary. How does the script know where to send the email confirmation to otherwise?
Thank you!
Forgot to mention in my last comment: When I ran the “debug” feature, it gave me this error:
TypeError: Cannot read property “values” from undefined. (line 3, file “Code”)
Is that why it’s not working?
Thank you in advance!
Nice ! It’s working for me.
But i’d like to add something.
In my form, people submit the several expenses they’ve made for an event.
And in the confirmation email, i’d like to confirm the total expenses the person has made. So i added a column in the google spreadsheet with a simple sum formula to do the math and that is the result of that column I want to appear in my confirmation email.
But in the confirmation email, it says “undefined” instead of the results of the sum.
Any idea how i could fix this ?
Is there any way for the automated email to include form values of a certain type:
var Did You Pass = e.values[1];
If the response to this was NO then Include this in the email, if the answer was Yes don’t include.
Maybe something like this:
var yourName = e.values[1]; Where e.values [1]=NO
Let me know your thoughts.
Thanks
Randy
Hi
Thank you for the script.
Is it any method to create a unique code on form submit and include it in the Email?
I created a Unique Code in the speadsheet (based on timestamp) and i included the line
“\nYour Code: ” + uCode +
in the emailbody.
The results:
Your Code: undefined
Do you have a sugestion?
Thnaks
Thanks for sharing this. It works beautifully.
I’m wondering if you can help.
When “var answer1 = e.values[11];” refers to a calculated fields which contains “=CONTINUE(R2, 34, 1)”, the email displays “undefined” as the result. I’m assuming the email is generated before the spreadsheet performs all the calculation. Is there a way to fix that?
Thanks in advance for your help.
Hello,
I am working on a Google form using your wonderful script and I am getting an error message –
. . . .
Your script, email.gs, 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 TOTY Nomination Form.
Details:
Start Function Error Message Trigger End
9/27/13 8:33 PM onFormSubmit TypeError: Cannot read property “0” from undefined. (line 3, file “email”) formEdit 9/27/13 8:33 PM
Sincerely,
Google Apps Script
. . . .
The trigger is set up to onFormSubmit as is the function – but I can’t get it to work. Here is the script in my form – which I have called email.gs:
function onFormSubmit(e) {
var timestamp = e.values[0];
var yourName = e.values[1];
var emailAddress = e.values[2];
var teaRoom = e.values[3];
var blackTea = e.values[4];
var puerTea = e.values[5];
var oolongTea = e.values[6];
var whiteyellowTea = e.values[7];
var greenTea = e.values[8];
var subject = “Tea of the Year Confirmation”;
var emailBody = “Thank you for making your selections for Tea of the Year ” + timestamp +
“\n\nThe details you entered were as follows: ” +
“\nYour Name: ” + yourName +
“\nYour Email: ” + emailAddress +
“\nI sip at these tearooms: ” + teaRoom +
“\nTeas nominated: ” + blackTea + puerTea + oolongTea + whiteyellowTea + greenTea;
var htmlBody = “Thank you for your Tea of the Year nominations ”
“ The details you entered were as follows: ” +
“Your Name: ” + yourName +
“Your Email: ” + emailAddress;
var optAdvancedArgs = {name: “Tea of the Year”, htmlBody: htmlBody};
MailApp.sendEmail(emailAddress, subject,
emailBody, optAdvancedArgs);
}
It says there’s an issue with line 3, the timestamp. What am I doing wrong as it all seems correct?
The form is here; http://teaoftheyear.com/nominate/
Any help you can give me is greatly appreciated.
ellen
Excellent question, Ellen! It says the data isn’t valid, but the data should be getting passed to it when the onFormSubmit trigger fires. Are you sure you are testing it by submitting the form, instead of by trying to run the code directly?
Thanks for your reply. Yes, the tests are through the form on the actual website. I gave it an overnight to rest and am still getting the same error…
Sorry, Ellen–I’m unsure of what could be wrong with your script. Did you see t1dude’s comment on the post, that some people might be seeing the error if they put the script on the form instead of on the spreadsheet containing the data? Note the screenshot in the article–you should be seeing the spreadsheet when you go through the menus to reach the code editor. Please check that for me and ensure the code is in the proper place.
Awesome! that was it! THANK YOU SO MUCH for your help and explaining it. Attaching it to the spreadsheet instead of the form was the key.