In an
earlier article we detailed how you can use Google Docs to create a submission form in your website. Step 5 of that article showed how you can be notified whenever a new submission is made; the only trouble with this is that it sends you a notice email that makes you then go back to the Google Docs spreadsheet to view the new submission... not the most user-friendly experience!
The script and instructions below advance the Google Docs submission form functionality so that it automatically emails you (or all of the form editors) the contents of the submission.
Step 1.
Using your existing Google Docs form spreadsheet, go to the spreadsheet view. Go to Tools –> Script Editor and copy-paste the following code in that code editor window:
function sendFormByEmail(e) {
var emailSubject = "Google Docs Form Submission";
// Set with your email address or a comma-separated list of email addresses.
var yourEmail = "YOUR EMAIL ADDRESS";
// Set with your spreadsheet's key, found in the URL when viewing your spreadsheet.
var docKey = "YOUR KEY";
// If you want the script to auto send to all of the spreadsheet's editors, set this value as 1.
// Otherwise set to 0 and it will send to the yourEmail values.
var useEditors = 1;
// Have you added columns that are not being used in your form? If so, set this value to
// the NUMBER of the last column that is used in your form.
// for example, Column C is the number 3
var extraColumns = 0;
if (useEditors) {
var editors = DocsList.getFileById(docKey).getEditors();
if (editors) {
var notify = editors.join(',');
} else var notify = yourEmail;
} else {
var notify = yourEmail;
}
// The variable e holds all the submission values in an array.
// Loop through the array and append values to the body.
var s = SpreadsheetApp.getActive().getSheetByName("Sheet1");
if (extraColumns){
var headers = s.getRange(1,1,1,extraColumns).getValues()[0];
} else var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
var message = "";
for(var i in headers) {
message += headers[i] + ' = '+ e.values[i].toString() + '\n\n';
}
MailApp.sendEmail(notify, emailSubject, message);
}
(a clean version of the code
is here).
Step 2.
Replace the value of variables in the script with your own variable values. Name the project (where it says "Untitled project" at the top of the page). Control-S to save (or click the disk icon).
Step 3.
Go to Resources –> Current Script’s Triggers. It will say "no triggers set up. click to add one now."; click that link to add a trigger. Click on the "notifications" link to associate the script to your email address, then choose "immediately" in the second drop down box. Hit ok to close that window, then hit the "Save" button.
Step 4.
A message will be shown to authorize the script; click on "Authorize". It will approve the authorization for the trigger, and when it brings you back to the screen hit the "Save" button.
You can now close the window and go back to your spreadsheet, you're all set!
Common issues:
1) Make sure that the name of your sheet in the spreadsheet is the same as the sheet name in this line:
var s = SpreadsheetApp.getActive().getSheetByName("Sheet1");
2) Make sure that your sheet name does not have any spaces; this can cause an error.
3) Do not have empty columns interspersed with the columns that you have content being submitted to. This will cause alignment issues and the script to fail.
4) Seeing an error like "cannot call method 'tostring' of undefined? Chances are this is because you added extra columns at the end of your spreadsheet that aren't being used by your form. The way to get around this error, while still having those columns, is to...
Change this line:
var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
Replace "s.getLastColumn()" with the column number of the last column used in your form.
(this update/issue is no longer valid! our revised script posted above fixes this error!)
5) Sometimes column names can cause issues. If you're having trouble try changing the column names to 1-word values without special characters.
Don't forget!
We have quite a few other articles on
creating and extending forms for use in your Blogger site, check them out!
Before you leave a comment...!
Before you leave a comment saying there is an error or it doesn't work, it does. We have this script running on dozens of client projects. So if there is a problem, it's something unique to your form, script, or variables. If you need help, we are available to help, but at our billable rates. Please use the contact form and we'll get back to you as soon as possible to work through the trouble, but understand that we have billable client work to be done.
Hi,
ReplyDeleteThank you for your script. Unfortunately, I'm getting the following error:
ReferenceError: "replyAddress" is not defined. (line 30)
Any idea what the problem might be?
Thanks
I think I figured it out. Line 30 should read:
ReplyDeleteMailApp.sendEmail(notify, emailSubject, message);
instead of
MailApp.sendEmail(notify, replyAddress, message);
You're entirely correct, thank you for correcting me! Not sure why that was there... but I've updated the code above and the link for the clean copy. Thank you for the correction :)
ReplyDeleteI have tried to use this and get the error:
ReplyDeleteTypeError: Cannot read property "namedValues" from undefined. (line 25)
Any idea what the issue is?
Thanks
Sorry I can't reply to you directly, but before you added the script, did you create the form? The form needs to have more than 1 column otherwise namedValues will return as undefined.
ReplyDelete-David
I am encountering the same issue as Anonymous on May 18. I've created the form and the spreadsheet. it is giving me the same issue
ReplyDeleteTypeError: Cannot read property "namedValues" from undefined. (line 25)
Hi Kris,
ReplyDeleteTo get that error, are you hitting the RUN button?
If so, there is no need to hit the run button, since that would execute it with an empty value for "e" which is your submitted values. Don't hit run, just save and perform the execute steps. It will automatically run/execute when someone submits a value, and in doing so with have a value for e (and hence the namedValues of e).
-David
This all worked great for me. One quick note: On step 3, I think you want to set a form submission trigger. At least, that's what I did, and it worked. Also on step 3, a confusing point is that the notifications seem to be for failures, yet the whole script works only when you succeed.
ReplyDeleteThis worked great for me, did ALMOST exactly what I wanted. Is there any way to control the order in which the form responses are loaded into the email? I assume they would load in the same order as on the spreadsheet but it seems entirely random.
ReplyDeleteWhat I want to see is the responses in the exact same order as the questions are on the form and on the spreadsheet.
Thanks for this!
The questions (I believe) go in the same order as the order you created them in. In the code for the form you should see that the name of the form elements has a number in it such as "entry.0.single" which corresponds to the column in the spreadsheet. It does not necessarily correlate to the order that you might have them in the form.
ReplyDeleteThis might be causing your problem and might necessitate re-creating the form with the form fields in the correct order.
David, they do not. I built my form exactly in the order of a Word form that we are dumping the data into to send back to the client, so I know it's in the correct order. There seems to be no rhyme or reason. My last question shows up first, the TimeStamp shows up last.
DeleteI can't see any pattern that would suggest there is a rational reason for the order.
I emailed you with a link to another very complex script that has a command to order the values as they are in the spreadsheet - I'd rather use your script since it's MUCH more user friendly. Less, is more..
http://stackoverflow.com/questions/9823491/google-docs-spreadsheet-send-email-and-order-values
Using e.values instead of e.namedValues you could create your own order/array of values and set them in the order that you want (makes the script more personalized and less generic, but would work).
DeleteCreate an array of your fields ("Username","Email address","Message") then for each value in that array, grab the corresponding value from the e.namedValues array. Once you have that, use the array you created to generate the email.
I am having the same problem as Sandy. Do you have example code of this ?
DeleteHaving the email send the fields in the form in an random order just makes extra work
I had a friend help me out with the code. Here it is - form now sends the fields in the same order as the spreadsheet.
Deletevar emailSubject = "Google Docs Form Submission";
// Set with your email address or a comma-separated list of email addresses.
var yourEmail = "YOUR EMAIL ADDRESS";
// Set with your spreadsheet's key, found in the URL when viewing your spreadsheet.
var docKey = "YOUR KEY";
// If you want the script to auto send to all of the spreadsheet's editors, set this value as 1.
// Otherwise set to 0 and it will send to the yourEmail values.
var useEditors = 0;
if (useEditors) {
var editors = DocsList.getFileById(docKey).getEditors();
if (editors) {
var notify = editors.join(',');
} else var notify = yourEmail;
} else {
var notify = yourEmail;
}
var s = SpreadsheetApp.getActive().getSheetByName("Sheet1");
var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
var message = "";
for(var i in headers) {
message += headers[i] + ' = '+ e.namedValues[headers[i]].toString() + '\n\n';
}
MailApp.sendEmail(notify, emailSubject, message);
Nice addition!
DeleteI've updated the code accordingly, thank you.
-David
That has worked for me, thanks
DeleteAnyone any ideas how I can make the headers appear in bold with the responses being left as normal ?
DeleteEvery time I try and use .bold(), I just get b tags /b appearing on the output email. Other javascript I have tried just fails, told that the argument to the left is not valid
Regarding bold headers in the email that is sent out, essentially in the script as it is written you can not. Bold would require that the email contents be HTML formatted, and then use a different mailapp.sendemail syntax. If you're interested in learning more about how to do that you can visit this page on Google's developer resources: https://developers.google.com/apps-script/class_mailapp
DeleteIs there any way that google doc submission form can add blog post title automatically when pressed "submit form" and goes to form submission page with the post title automatically appear in one of the form fields?
ReplyDeleteYou mean to have a form field in the submission form automatically populated with the page title of the page that you're on? And that value either in a displayed form element or in a hidden element?
Deleteyes exactly. In detail, i have "submit button" on the bottom of post. When reader clicks this button, it will take reader to "form submit" page, and i want the title of post automatically go to one of the fields of this form when reader submits form. So that reader does not have to refer the post title by himself.
ReplyDeleteI have the exact same problem where the emails I'm receiving after people submit the forms have responses showing in a different order than the original survey questions. Did anyone figure out how to fix this?
ReplyDeleteHello all, the code has been updated with the ordering fix thanks to a reader. Grab the clean version here: http://pastie.org/4281106
ReplyDeleteI found this really helpful. I inserted the updated code but I keep getting this error:
ReplyDeletesendFormByEmail TypeError: Cannot call method "toString" of undefined. (line 30) formSubmit
Any thoughts why. The coded that I used is below.
function sendFormByEmail(e) {
var emailSubject = "Google Docs Form Submission";
// Set with your email address or a comma-separated list of email addresses.
var yourEmail = "my email";
// Set with your spreadsheet's key, found in the URL when viewing your spreadsheet.
var docKey = "My Key";
// If you want the script to auto send to all of the spreadsheet's editors, set this value as 1.
// Otherwise set to 0 and it will send to the yourEmail values.
var useEditors = 1;
if (useEditors) {
var editors = DocsList.getFileById(docKey).getEditors();
if (editors) {
var notify = editors.join(',');
} else var notify = yourEmail;
} else {
var notify = yourEmail;
}
var s = SpreadsheetApp.getActive().getSheetByName("Sheet1");
var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
var message = "";
for(var i in headers) {
message += headers[i] + ' = '+ e.namedValues[headers[i]].toString() + '\n\n';
}
MailApp.sendEmail(notify, emailSubject, message);
}
Did you set variables for email address and docKey?
Deletehey, I am getting the same error and i have set email & docKey.
ReplyDeleteThe version that produced entries in the wrong/random order worked fine.
Did you re-authorize the script when you made the change? After updating the script, go to Resources, then Current Script's Triggers, then just hit Save and it should ask you to re-authorize.
DeleteGive it a try after doing that.
Yes I did. This was in fact the first version I tried, when it didn't work I went back to the first one, which worked fine except for the random order, then back to the new one - same error as the previous user. I also just tried it again from scratch.
ReplyDeleteHere's my code:
function sendFormByEmail(e) {
var emailSubject = "Marketing Checklist submitted";
// Set with your email address or a comma-separated list of email addresses.
var yourEmail = "email1@address.com, email2@address.com";
// Set with your spreadsheet's key, found in the URL when viewing your spreadsheet.
var docKey = "the key";
// If you want the script to auto send to all of the spreadsheet's editors, set this value as 1.
// Otherwise set to 0 and it will send to the yourEmail values.
var useEditors = 0;
if (useEditors) {
var editors = DocsList.getFileById(docKey).getEditors();
if (editors) {
var notify = editors.join(',');
} else var notify = yourEmail;
} else {
var notify = yourEmail;
}
// The variable e holds all the submission values in an array.
// Loop through the array and append values to the body.
var s = SpreadsheetApp.getActive().getSheetByName("Sheet");
var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
var message = "";
for(var i in headers) {
message += headers[i] + ' = '+ e.namedValues[headers[i]].toString() + '\n\n';
}
MailApp.sendEmail(notify, emailSubject, message);
}
This seems to be the line in question:
Deletevar s = SpreadsheetApp.getActive().getSheetByName("Sheet");
Is "Sheet" the name of your sheet? Or is it "Sheet1" like mine is by default? Once I fix that to be the correct sheet name the script works again.
Yet it is. I actually renamed it from the default "Sheet1" to "Sheet" to test whether this could be the isssue - just changed it back, still the same.
ReplyDelete2 things: I'm hoping that your "docKey" is not "the key" and instead the long string of letters and numbers that make up your doc's key, correct? And I'm also hoping that you're re-authorizing, agreeing, and saving, correct? The code that you pasted above worked perfectly for me once I filled in my docKey, changed Sheet to Sheet1, and set my email addresses.
ReplyDeleteyes to both.
ReplyDeletecould it have sth to do with the actual spreadhseet, there are some hidden colums in there (that should have been deleted but i wanted to avoid having to remake the whole thing)
If those hidden columns are interspersed with the submitted value columns that might cause the conflict; I recommend always keeping "non-submission columns" towards the end of your form columns. Beyond that, I'm sorry, I'm not sure what else to tell you as I can't duplicate the bug.
Deletethank you so much for your quick support, but it were indeed the hidden columns that were causing all this! Cannot believe how much time i have wasted :D.
DeleteSolved!
Hi Vika, how did you solve it? I have the same issue with the empty/hidden columns. I have put so much effort into the form, don't want to mess it up!
DeleteHi Vitalis, move those empty/hidden columns towards the end of the form and it should work.
DeleteI am trying to figure my way through this since I have never used or created a script. I think I have everything except I get this message: TypeError: Cannot read property "namedValues" from undefined. (line 30). And, this is highlighted in gray: message += headers[i] + ' = '+ e.namedValues[headers[i]].toString() + '\n\n';
ReplyDeleteCan you tell me what to do?
Hello, did you authorize the script and set the variables correctly?
DeleteYes. I enter the subject, my email, and key. Left the editor as 1. I'm not for sure the value of e at the end. The headers in my form are long questions instead of simple descriptors such as email. I really appreciate your help and patience!
DeleteI had this issue but fixed it. You know how when you are creating a Google form and you delete questions, the columns remain in the form? Well, those unused columns were causing the failure. Delete unused columns to solve the issue.
ReplyDeleteThanks for the script!
Great catch and fix!
DeleteI ran into this problem as well but found that my email script still wasn't working because it wouldn't prompt to reauthorize - I had to delete the script and resave it to get the authorization prompt again. The notification is working now but I am getting a simultaneous error email that says 'We're sorry, a server error occurred. Please wait a bit and try again.' I am hoping this is a temporary issue on Google's side. - BCS
DeleteHey David,
ReplyDeleteI really loved the way you utilized the blogger platform to create the awesome websites!!! Thanks for sharing the knowledge mate.
Regarding this post, I've already implemented the email notification for my blog's page (www.techbeats.co.in); which will send a confirmation email to the form submitter. Yet, I don't know the daily mail limit in gmail. Do you've any idea on this?
Thanks.
I'm sorry, I don't know this information. You might want to post the question on the product support forums for either Gmail or GDocs.
DeleteWhat is the doc key?
ReplyDeleteWhen you create a form in Google Docs, your URL for that form will look something like this:
Deletehttps://docs.google.com/spreadsheet/ccc?key=XXXXXXXXXXXXXXXXXXXXXXXXXX#gid=0
The value of "XXXXXXX" is your doc key.
Hey, I tried this, put the doc key and my e-mail address in, gave permissions to the script, and it doesn't do anything. I fill out a form as a test, and it just sits there. Even following all your steps I figure i'm missing something somewhere?
ReplyDeletethanks for writing this article.
Tara
Hi Tara, mind sharing the URL of the form with me so I can take a look? Even if you don't have the script working your form should still submit just fine.... seems like there's something wrong with your form.
DeleteHey David, I'm sorry, I didn't make myself clear. The form submits to the spreadsheet, but I don't get an e-mail. That's why I think I'm missing a link between the spreadsheet and the script.
ReplyDeleteWhen your authorized the script, did you authorize it to run on submission of data? The other options aren't what you want... but if you're seeing an error message, not really sure what you might have missed.
DeleteI have the same problem- no email gets sent. There were no options to authorize "on submission of data."
DeleteI got a failure e-mail! Yay! It says:
ReplyDeleteTypeError: Cannot call method "toString" of undefined. (line 32)
Tara
Hey David, I started all over and I got it to work.
ReplyDeleteI created a very small test form and associated spreadsheet, and then created the script again. THAT worked fine. The only thing I can think of with the first fail form sheet is that the headers are huge with lots of punctuation. I'll keep working with it. Thanks again for this fabulous article!
Fixed it! Thank you again!
ReplyDeleteThis is great stuff and I got it to work out just fine. I have a very long and complex form though. Is there anyway to just email the fields that were populated through each submission? The email I get is really long and alot of the fields are empty and this don't need. Thanks!
ReplyDeleteHi Matt, this is the line you want to look at:
Deletemessage += headers[i] + ' = '+ e.namedValues[headers[i]].toString() + '\n\n';
wrap it within an if to check e.namedValues[headers[i]] !=""
Awesome! Thanks so much! It work great when I tested it this morning!
Deletenoobish attack here
ReplyDeleteI did everything in my blog http://greekxfreelancer.blogspot.gr/p/about.html
and when i submit a form it emails me that a form was submitted with no info about the submit ion telling me to check in the spreadsheet view
Is that supposed to happen or i should take the original message to my email?
and is there a way for the user when he submits the form after the thank u page to auto return back to the blog?
Thx in advance
Hi Kostas, it looks like you didn't quite follow all of the instructions in our blog post about how to add a form to your Blogger site, specifically the redirects. If you follow the instructions in that first post, then add this script, you should be in good shape.
DeletemY BAD All Fixed thx U r the best
DeleteHi,
ReplyDeleteAnother question. Things are working for the most part. But now even for items I don't submit anything for, they show up with a bunch of commas in them. In fact, I get about 20 entries with commas in them on top of the items I actually put something in for. Any thoughts on this? Thanks!
Matt
Hi Matt, yes, seems you're still inserting blank entries, so change how your if statement is working so it doesn't add them to the message.
DeleteThank you for this wonderful code! It's worked for me. After fooling around with I noted that I need quotation marks on all of my email addresses and the 2nd time I saved changes to the code it didn't ask me to Authorize it again. Works perfectly!
ReplyDeleteI followed these instructions to the letter, but there's nothing sending to email. Any ideas? I'm using the correct doc key and email.
ReplyDeleteHi David!
ReplyDeleteYour script is marvelous! It is exactly what I needed. I have one question though, is it easy to add the answer to one of the form questions in the Subject of the Email that is sent? I am not exactly sure how to add that feature. If it is easy to do i would love a pointer int he right direction!
Thanks Again!
Hi Marcus,
DeleteTry grabbing the value of the column you want like so: var emailTitle = e.namedValues["ColumnName"].toString();
Then put that into the send function call in the subject field.
Hello,
ReplyDeleteI am using your script to auto-forward to a ticket tracking system, and it would be very helpful if the form submitter's email address was the replyto address so that if I replied from the tracking system then it will send an email to the form submitter.
Any idea how I could work this in? I don't really know anything about creating code.
Thanks,
Amanda
Hi Amanda, we have tried, tried, and tried again to get that to work. There is the coded ability, but regardless of how we format it, the submitters email just isn't there. We believe this is intentional on the side of Google so that a form can't be used as a spoof to send emails from someone other than the script-owner.
DeleteOn the other side, the submitters email address is being captured and sent to the spreadsheet, so your tracking system should have that information and be able to email the form submitter from there as opposed to the submission script handling it.
Thanks so much for the quick response!
DeleteHi David, Firs of all thanks a ton. This was really helpful. I am actually using a google for to submit observation feedback to teachers at my school, and I would love for it to automatically send the email to different people depending on who is being observed. The teacher being observed is one of the fields being submitted in the form. Is there a way to have the email address field be conditional on a field in the form.
ReplyDeleteThanks in advance. I'd be totally lost without this thread and your help.
Hi Keith, would love to learn more about how you're using it! please email me at david@blogxpertise.com
Deleteregarding your question, I think that should be quite possible. I need to know more about how you'd like it to work because that will dictate the code, but please contact me at the email address.
Hi there,
ReplyDeleteThis is just what I need - there are extra columns (that I input into the spreadsheet, not through the form) all the way on the right of the form-submitted columns. They're not interspersed,b ut they seem to be preventing the script from working. Basically, I want columns A through M emailed, but not column N - any ideas?
I am very new to this, so forgive my ignorance, but I can not get this form to work. I am getting the following message:
ReplyDeleteTypeError: Cannot call method "getRange" of null. (line 27)
This is what I have in line 27:
var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
Any idea what I am doing wrong?
Hi David,
ReplyDeleteI followed all the steps to a tee and I still get no email. When I try to debug the script I get:
TypeError: Cannot call method "getRange" of null. (line 29)
var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
I've been trying to get this working for 2 days and this is a last resort. PLEASE HELP ME! I MIGHT CRY
Hi David, thanks for this helpful script!
ReplyDeleteIs there a way that the email can be sent to an email address that the submitter enters in the form, instead of to me? Thanks!
Hi Ted, I've been trying for ages, and while it says it is possible, I don't believe it is permitted for spam purposes.
DeleteThanks for your reply David. I know almost nothing about programming, but I might try to have another look at this page some time soon where one of the functions it describes is sending the email to the form submitter:
ReplyDeletehttps://developers.google.com/apps-script/articles/mail_merge
Though I'm not sure how it happens. I'll let you know if anyone manages to help me with this.
You can send the form TO the form submitter, but not FROM the form submitter. (I think I misread your question)
DeleteSee a previous comment where I gave this solution:
var notify = e.namedValues["ColumnName"].toString();
(replace ColumnName with the name of the column of the email address)
Just a note: this is a great way to get flagged as spam. You're essentially setting up an automated spam script.
Phew, glad to know it's possible. Thanks so much! That's really helpful.
DeleteHello,
ReplyDeletefirstly thank you for putting this script together, but can you help with a problem i am having..
out of 28 form submissions, only 2 have been successful the rest give this error
TypeError: Cannot call method "toString" of undefined. (line 31)
all triggers, notifications and sheet details are correct.. but it just doesn't seem to want to call the "headers" and produce the email..
please can you help me with a solution..??
Hi Richard, did you set the email addresses in the script? Is the spreadsheet's sheet 1 word without spaces? Are there empty columns in between content columns in your spreadsheet? It can be any of those issues.
DeleteHi David, yes my email address is set (a gmail address), "docKey" is set, its called "Sheet1", this is also correct and there are no blank rows (rows A-M)..
Deletethis is why i'm struggling as it all looks correct, exact copy of your script, all variables set, triggers set and notification rules set..
but for some reason, it worked twice out of 28+ submissions..
Hi David,
Deletedoesn't look like my reply posted correctly..
Yeah everything is set, sheet is "Sheet1", there are no empty columns and email address is set correctly..
just cant figure it out.?
Try removing any spaces that you might have in the column headers
DeleteHi David,
ReplyDeleteUnrelated to my question just above for which you helped me with the right solution, I've got a different problem at the moment:
When I submit an entry in the form, the data goes to the Spreadsheet, but nothing happens. The data doesn't go to my email, and I don't even get an error message. I've followed your instructions in previous spreadsheets and everything worked no problem, but in the case of this spreadsheet, it's just not working, and the program seems unaware that any submission has been made.
Would appreciate your help and thanks!
I love this script. Maybe I missed this question or answer, but is there a way for the E-mail to just send specific columns? So, for example, when the form is submitted, only column b and e send on the email?
ReplyDeletethanks,
Kurt
Kurt. Thanks for your post and work. I've followed your instructions, but got this error: TypeError: Cannot call method "getRange" of null
ReplyDeleteWhat am I doing wrong?
I'm getting the same error... does anyone know why?
DeleteHey Guys,
ReplyDeleteThanks for all of your hard work. I put the custom form code on my website with our own thank you age. Everything is working except I can not get the detailed notifcation email to work.
Is there a different way to get this to work with a custom embedded form?
Got it working. Wanted to note that I had to change getSheetByName("Sheet") to match the name of the sheet which by default these days is Sheet1 so:
ReplyDeletegetSheetByName("Sheet1")
Also took out the sample question columns not sure if empty columns are problematic.
Anyone know how to customize the "from" email address ?
Unfortunately permissions do not allow you to customize that field. We've tried every which way according to the documentation, but because of permissions, it will always set the from address to be the email address that initiated the function. HCC... Holyoke Community College?
DeleteThank you ;)
ReplyDeleteWork great !
Just read the "Common issues" to qolve my issues ^^
Thank you for this wonderful script! I know absolutely nothing about this stuff so I was so happy to stumble upon this.....however...
ReplyDeleteI had it working great but I think when I added columns to my original spreadsheet I may have messed it up. I know I read above about hidden columns messing things up, these columns are at the end of my spreadsheet.
A little background as to what I am using it for... I am trying to track Marketing Material Requests and have it so the person who submits the form can view the status of the request online (aka the spreadsheet). That is why I had to add columns at the end of the spreadsheet, so they can see the status which I manually type in to the spreadsheet when it is completed. These columns aren't in the original form (just on the spreadsheet for viewing purposes only). Could these additional columns at the end be the reason my email script isn't working? Or is it just because it hates me?
Please contact me at David@confluentforms.com if you need personalized assistance.
DeleteThanks so much! I have been working on a project which has a form embedded into a webpage and I couldn't have gotten it to work without this information!
ReplyDeleteDavid - Thanks for the script... works great!
ReplyDeleteIs it possible to have the email go to a specific address from the form itself? For example, if they select test@email.com it goes to that email but if they choose test2@email.com it goes to that one?
Yes, that should be possible, but would require some coding. Please see the contact form regarding hiring us for customization if you're interested.
DeleteIf you're a user of our script please take note that we've made a substantial update to the script (above) that fixes many problems that you might have had. Give it a try!
ReplyDeleteSo... I am running on the following error:
ReplyDeleteTypeError: Cannot read property "values" from undefined.
Thank you for the great script, hope it works for me :)
So when you get that error, what are you doing? Did you submit content through the form? How are you getting that error message? And did you try our "common issues" above?
ReplyDeleteSorry for that. It works now. It didn't work for some reason when I tested the form from the form menu.
ReplyDeleteDavid!!! your script works so well!!! I was able to go thru the documentation for MailApp and customize it so desk.com takes the email and replies to each form with the proper name and reply email!
ReplyDeleteif anyone is interested in the code, let me know :)
hello there, one one for I am constantly receiving - TypeError: Cannot call method "toString" of undefined
ReplyDeleteI have done this on other forms but for this form when sumitted it doesnt work, please help - here is form - https://docs.google.com/a/phoenixcommunity.org/spreadsheet/viewform?pli=1&formkey=dEpOTlhpd0ZLc2RMazV5SUpIaEFLUEE6MQ#gid=0
Are you getting this message when content is submitted via your form?
ReplyDeleteI'm getting an error. Something to do with what the parameter 'e' is when the function is called. My error is Cannot read property "values" from undefined. (line 41)
ReplyDeleteWhat is the e object?
"e" is the array of submission values. When are you receiving this error? What are you doing when you see it? Have you tried submitting a value through the form?
ReplyDeleteI'm receiving this error no matter what I do: TypeError: Cannot read property "values" from undefined. I think it might have to do with the e parameter as other people were mentioning.
ReplyDeleteCan you tell me how/when you're getting this error? Is it when you're submitting through your form?
ReplyDeleteHi David, please help. TypeError: Cannot call method "getRange" of null. (line 34, file "Code"). Thank you
ReplyDeleteCan you tell me how/when you're getting this error? Is it when you're submitting through your form?
ReplyDeleteYes, count the columns. If you have 8 columns, replace it with the number 8.
ReplyDeletei'm getting an error online 20 that says Missing ) after argument list. (line 20, file "Code") -
ReplyDeletewhat am i doing wrong? all i've done is cut and paste the url to the spreadsheet
Hello I'm getting an issue in this line:
ReplyDeletemessage += headers[i] + ' = '+ e.values[i].toString() + '\n\n';
What I'm i doing wrong?
can you paste the complete code that you have?
ReplyDeletecan you paste the complete code that you have? would love to see it so I can find the syntax error
ReplyDeletewhat is the error message that you're getting?
ReplyDeleteI have the same issue.
ReplyDeleteIt say: No item with the given ID could be found, or you do not have permission to access it. (line 20, file "Code")
I just used the code from David, and replace the correct e-mailadres.
Can you help David?
that seems like your dockey is incorrect. did you change the dockey variable value?
ReplyDeleteHi there, I see others are having the same issue with the following error but I have yet to see people post their entire code when you've asked them, so not seeing the answer. I am getting this error:
ReplyDeleteTypeError: Cannot read property "namedValues" from undefined. (line 20, file "Code")
Here is my code:
function sendFormByEmail(e)
{
var email = "MYEMAIL";
var subject = "SUBJECT LINE";
var s = SpreadsheetApp.getActiveSheet();
var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
var message = "EMAIL TEXT";
for(var i in headers)
message += headers[i] + ' = '+ e.namedValues[headers[i]].toString() + "\n\n";
MailApp.sendEmail(email, subject, message);
}
that can't possibly be your code as that wouldn't validate or work at all. but my question is when are you seeing that error? have you tried submitting a value through your form and does it send you the contents? I copy/pasted the code provided in this tutorial into a brand new form and it worked immediately. The only change that needs to be made is Sheet1 to match your form's sheet name as indicated in "Common Issue #1"
ReplyDeleteO-kay well, in that last code, I submitted it without the //text. I was using another persons code that was actually submitting fine to the spreadsheet and sending emails upon submission, but oddly only a few - not all of them. I'm now using your entire code (below) and am getting this error.
ReplyDelete**Cannot read property "values" from undefined. (line 38, file "Code")**
My guess is that it's because of my added columns in my spreadsheet but can't figure out what exactly I need to change. Thanks for the help with this!
******************
function sendFormByEmail(e) {
var emailSubject = "Google Form Submission";
// Set with your email address or a comma-separated list of email addresses.
var yourEmail = "EMAILREMOVEDFORPRIVACY";
// Set with your spreadsheet's key, found in the URL when viewing your spreadsheet.
var docKey = "0AoRCaUeuzkeedEttM0JIdW1wcWVETFc3UkpLODNSNHc";
// If you want the script to auto send to all of the spreadsheet's editors, set this value as 1.
// Otherwise set to 0 and it will send to the yourEmail values.
var useEditors = 0;
// Have you added columns that are not being used in your form? If so, set this value to
// the NUMBER of the last column that is used in your form.
// for example, Column C is the number 3
var extraColumns = 10;
if (useEditors) {
var editors = DocsList.getFileById(docKey).getEditors();
if (editors) {
var notify = editors.join(',');
} else var notify = yourEmail;
} else {
var notify = yourEmail;
}
// The variable e holds all the submission values in an array.
// Loop through the array and append values to the body.
var s = SpreadsheetApp.getActive().getSheetByName("ORCHARD");
if (extraColumns){
var headers = s.getRange(1,1,1,s.getLastColumn(7)).getValues()[0];
} else var headers = s.getRange(1,1,1,s.getLastColumn(7)).getValues()[0];
var message = "TEXTREMOVEDFORPRIVACY";
for(var i in headers) {
message += headers[i] + ' = '+ e.values[i].toString() + '\n\n';
}
MailApp.sendEmail(notify, emailSubject, message);
}
This part is wrong:
ReplyDeleteif (extraColumns){
var headers = s.getRange(1,1,1,s.getLastColumn(7)).getValues()[0];
} else var headers = s.getRange(1,1,1,s.getLastColumn(7)).getValues()[0];
Should be:
if (extraColumns){
var headers = s.getRange(1,1,1,extraColumns).getValues()[0];
} else var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
ok, that is edited. Now I get the error below for line 38:
ReplyDeleteLine 38: message += headers[i] + ' = '+ e.values[i].toString() + '\n\n';
Error: Cannot read property "values" from undefined. (line 38, file "Code")
If you're getting that error by hitting "play" in the script editor it's going to give you that message since no values were submitted. Submit your form to test it instead with a notification sent to you for errors. Let me know if the submission works.
ReplyDeleteThanks David. The form submits into the spreadsheet but does not email me.
ReplyDeleteActually, I just received an email but it was an error message from google:
ReplyDeleteTypeError: Cannot call method "toString" of undefined. (line 38, file "Code")
email me your code at david@confluentforms.com
ReplyDeleteI received the same error when submitting through the form. Could you help with the solution? I certainly appreciate your help.
ReplyDeleteCan I set my subject line to include one of the responses in the form submission? For example, my form asks for a name, and I'd like to see the subject of the email as "Name's Form Submission" so I can quickly locate specific responses. Thanks!
ReplyDeleteNo, when I run the code. I only ran the code when I tested the form+code and it didn't work.
ReplyDeleteThis is my code, this is a problem showing up in the comments happening every where you look up this method. Here's the code I've been using:
function sendFormByEmail(e)
{
// Remember to replace XYZ with your own email address
var email = "nadia@nadiachaudhry.com";
// Optional but change the following variable
// to have a custom subject for Google Docs emails
var subject = "Google Docs Form Submitted";
// The variable e holds all the form values in an array.
// Loop through the array and append values to the body.
var s = SpreadsheetApp.getActiveSheet();
var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
var message = "";
// Credit to Henrique Abreu for fixing the sort order
for(var i in headers)
message += headers[i] + ' = '+ e.namedValues[headers[i]].toString() + "\n\n";
// This is the MailApp service of Google Apps Script
// that sends the email. You can also use GmailApp here.
MailApp.sendEmail(email, subject, message);
// Watch the following video for details
// http://youtu.be/z6klwUxRwQI
// By Amit Agarwal - www.labnol.org
}
I also got the same problem.
ReplyDeleteTypeError: Cannot read property "values" from undefined. (line 38, file "Code")
Here's my code:
function sendFormByEmail(e) {
var emailSubject = "Google Docs Form Submission";
// Set with your email address or a comma-separated list of email addresses.
var yourEmail = "MYEMAILADDRESS";
// Set with your spreadsheet's key, found in the URL when viewing your spreadsheet.
var docKey = "0Ao8VO3aUvVFudGxMTnZfb1VNOU5uWDRuUko1amRaRFE";
// If you want the script to auto send to all of the spreadsheet's editors, set this value as 1.
// Otherwise set to 0 and it will send to the yourEmail values.
var useEditors = 1;
// Have you added columns that are not being used in your form? If so, set this value to
// the NUMBER of the last column that is used in your form.
// for example, Column C is the number 3
var extraColumns = 0;
if (useEditors) {
var editors = DocsList.getFileById(docKey).getEditors();
if (editors) {
var notify = editors.join(',');
} else var notify = yourEmail;
} else {
var notify = yourEmail;
}
// The variable e holds all the submission values in an array.
// Loop through the array and append values to the body.
var s = SpreadsheetApp.getActive().getSheetByName("FormResponses");
if (extraColumns){
var headers = s.getRange(1,1,1,extraColumns).getValues()[0];
} else var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
var message = "";
for(var i in headers) {
message += headers[i] + ' = '+ e.values[i].toString() + '\n\n';
}
MailApp.sendEmail(notify, emailSubject, message);
}
Thanks for your help!
Very useful, thank you. Saved me lots of time and headache!
ReplyDeleteI keep getting this error when I run: TypeError: Cannot call method "getSheetByName" of null. (line 31, file "Code") I have the sheet name without spaces as per the current issues suggestion.
ReplyDeleteDavid, I am trying to set this up and getting an error on line 38. I have some fields that are not required, and therefore blank. Is this the issue?
ReplyDeleteDetails:
StartFunctionError MessageTriggerEnd7/9/13 11:50 AMsendFormByEmailTypeError: Cannot call method "toString" of undefined. (line 38, file "Code")formSubmit7/9/13 11:50 AM
I am having issues with the GetSheet command. My code is below and I would love some help.
ReplyDeletefunction sendFormByEmail(e) {
var emailSubject = "Customer Load Request Submission";
// Set with your email address or a comma-separated list of email addresses.
var yourEmail = "ptadamson@gmail.com, paul@reptiledog.com";
// Set with your spreadsheet's key, found in the URL when viewing your spreadsheet.
var docKey = "0AnKhRjNvhGbRdHFjVUZuam9ISzByb2RfM2w4elYwYWc#";
// If you want the script to auto send to all of the spreadsheet's editors, set this value as 1.
// Otherwise set to 0 and it will send to the yourEmail values.
var useEditors = 0;
// Have you added columns that are not being used in your form? If so, set this value to
// the NUMBER of the last column that is used in your form.
// for example, Column C is the number 3
var extraColumns = 11;
if (useEditors) {
var editors = DocsList.getFileById(docKey).getEditors();
if (editors) {
var notify = editors.join(',');
} else var notify = yourEmail;
} else {
var notify = yourEmail;
}
// The variable e holds all the submission values in an array.
// Loop through the array and append values to the body.
var s = SpreadsheetApp.getActive().getSheetByName("Sheet1");
if (extraColumns){
var headers = s.getRange(1,1,1).getValues()[0];
} else var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
var message = "";
for(var i in headers) {
message += headers[i] + ' = '+ e.values[i].toString() + '\n\n';
}
MailApp.sendEmail(notify, emailSubject, message);
}
David, have had a hell of a time with this script with numerous errors:
ReplyDelete#1 - Can you confirm that the final version of the script is here: http://pastie.org/6499318
#2 - I am constantly getting the "TypeError: Cannot call method "getSheetByName" of null. (line 31, file "Code")" even though the sheet name and name in the script are the same
#3 When I try to play and test the code from the Script itself it gives me the attached error
Please help, I desperately need to get this thing working for my team at work!
Yes, it is the most up to date and it does work. Please contact us if you'd like/need personal attention!
ReplyDeleteI have tried to make this script work but keep getting the same error on one of two lines, either 33 or 34.
ReplyDeleteThe error is as follows:
TypeError: Cannot call method "getRange" of null. (line 33, file "Code")
Here is the code:
function sendFormByEmail(e) {
var emailSubject = "Google Docs Form Submission";
// Set with your email address or a comma-separated list of email addresses.
var yourEmail = "Randy.Bach@Boydgroup.com";
// Set with your spreadsheet's key, found in the URL when viewing your spreadsheet.
var docKey = "7QYQW5jFgqctwlrfdVbQ";
// If you want the script to auto send to all of the spreadsheet's editors, set this value as 1.
// Otherwise set to 0 and it will send to the yourEmail values.
var useEditors = 0;
// Have you added columns that are not being used in your form? If so, set this value to
// the NUMBER of the last column that is used in your form.
// for example, Column C is the number 3
var extraColumns = 8;
if (useEditors) {
var editors = DocsList.getFileById(docKey).getEditors();
if (editors) {
var notify = editors.join(',');
} else var notify = yourEmail;
} else {
var notify = yourEmail;
}
// The variable e holds all the submission values in an array.
// Loop through the array and append values to the body.
var s = SpreadsheetApp.getActive().getSheetByName("Avaya Phone System Issue Log");
if (extraColumns){
var headers = s.getRange(1,1,1,extraColumns).getValues()[0];
} else var headers = s.getRange(1,1,1,8).getValues()[0];
var message = "";
for(var i in headers) {
message += headers[i] + ' = '+ e.values[i].toString() + '\n\n';
}
MailApp.sendEmail(notify, emailSubject, message);
}
Any help would be appreciated, I love the concept and would like to see this work.
Thanks
Randy
Give "common issue #2" a try.
ReplyDeleteI renamed my sheet and took out the spaces, still getting the same error. That and now I've broken the link that about 200 people have to register issues. Sure hope I can make this work to make this worthwhile.
ReplyDeleteThanks for the quick response.
Getting a message read: Cannot read property "namedvalues" from undefined. Checked on it several times, not sure where is the problem...Here is the code:
ReplyDeletefunction sendFormByEmail(e) {
var emailSubject = "Regal Daily Work Report Submission";
// Set with your email address or a comma-separated list of email addresses.
var yourEmail = "shufen@regaltravel.com";
// Set with your spreadsheet's key, found in the URL when viewing your spreadsheet.
var docKey = "0ApJkHurvn3aidE1icWhaTnc3RzhYVS10clRwak1CbGc#gid=0";
// If you want the script to auto send to all of the spreadsheet's editors, set this value as 1.
// Otherwise set to 0 and it will send to the yourEmail values.
var useEditors = 0;
// Have you added columns that are not being used in your form? If so, set this value to
// the NUMBER of the last column that is used in your form.
// for example, Column C is the number 3
var extraColumns = 0;
if (useEditors) {
var editors = DocsList.getFileById(docKey).getEditors();
if (editors) {
var notify = editors.join(',');
} else var notify = yourEmail;
} else {
var notify = yourEmail;
}
// The variable e holds all the submission values in an array.
// Loop through the array and append values to the body.
var s = SpreadsheetApp.getActive().getSheetByName("Responses");
var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
var message = "";
for(var i in headers) {
message += headers[i] + ' = '+ e.namedvalues[headers[i]].toString() + '\n\n';
}
MailApp.sendEmail(notify, emailSubject, message);
}
THANK YOU.
ReplyDeleteCan you send me link to the code that worked for you, My team is stuck and I'm at witts end.
ReplyDeleteIm getting error:
ReplyDeleteTypeError: Cannot read property "values" from undefined. (line 39, file "Code")
var message = "Sales Lead";
for(var i in headers) {
message += headers[i] + ' = '+ e.values[i].toString() + '\n\n';
call or email us; the script works "in general" but specifics oddities are usually the culprit.
ReplyDeleteMichael, check out this video, the script will be referenced here and you can see the walk through: http://www.youtube.com/watch?v=z6klwUxRwQI
ReplyDeleteI get same error. I think .toString() property is not supported anymore, or something else is missing. Could you guys check the issue and if there is workaround for break line replacement? Thanks!
ReplyDelete@fmiboy it works, we fixed @Michael Radie's issue and it wasn't an issue with the code. As I indicated in the "Common Issues", it is most likely your form or the variable values that you are including.
ReplyDeleteFor starters, remove #gid=0 from your docKey value.
ReplyDeleteHi. I used this script and got the "undefined" error. Adding the check for undefined vars as below fixed it. Now if the form field is empty, is is also listed empty in the submission email. Something like "FieldData = \n\n"
ReplyDeleteif (typeof e.values[i] != 'undefined'){
message += headers[i] + ' = '+ e.values[i].toString() + '\n\n';
}
this is a quite simple fix for my case were there is one field on my form that is not required. After this. your script is very great blessing. Thank you for sharing.
Great script - thank you! I don't know if you can help, but I have had to link my form to new spreadsheets so that the code works. The problem I'm running into now is that each iteration of the code (/spreadsheet) runs. In other words, I get 3 emails after each form submission. Any ideas how this can be fixed? Either way, I'm appreciative that you've put this out there. 3 emails is better than none!
ReplyDelete