Tip: Making a Google Doc submission form email you the results

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.

Comments

  1. Hi,

    Thank 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

    ReplyDelete
  2. I think I figured it out. Line 30 should read:

    MailApp.sendEmail(notify, emailSubject, message);

    instead of

    MailApp.sendEmail(notify, replyAddress, message);

    ReplyDelete
  3. 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 :)

    ReplyDelete
  4. I have tried to use this and get the error:
    TypeError: Cannot read property "namedValues" from undefined. (line 25)
    Any idea what the issue is?

    Thanks

    ReplyDelete
  5. 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.

    -David

    ReplyDelete
  6. 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

    TypeError: Cannot read property "namedValues" from undefined. (line 25)

    ReplyDelete
  7. Hi Kris,

    To 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

    ReplyDelete
  8. 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.

    ReplyDelete
  9. This 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.

    What 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!

    ReplyDelete
  10. 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.

    This might be causing your problem and might necessitate re-creating the form with the form fields in the correct order.

    ReplyDelete
    Replies
    1. 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.

      I 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

      Delete
    2. 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).

      Create 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.

      Delete
    3. I am having the same problem as Sandy. Do you have example code of this ?

      Having the email send the fields in the form in an random order just makes extra work

      Delete
    4. 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.

      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 = 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);

      Delete
    5. Nice addition!

      I've updated the code accordingly, thank you.

      -David

      Delete
    6. That has worked for me, thanks

      Delete
    7. Anyone any ideas how I can make the headers appear in bold with the responses being left as normal ?

      Every 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

      Delete
    8. 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

      Delete
  11. Is 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?

    ReplyDelete
    Replies
    1. You 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?

      Delete
  12. yes 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.

    ReplyDelete
  13. I 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?

    ReplyDelete
  14. Hello all, the code has been updated with the ordering fix thanks to a reader. Grab the clean version here: http://pastie.org/4281106

    ReplyDelete
  15. I found this really helpful. I inserted the updated code but I keep getting this error:

    sendFormByEmail 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);
    }

    ReplyDelete
    Replies
    1. Did you set variables for email address and docKey?

      Delete
  16. hey, I am getting the same error and i have set email & docKey.
    The version that produced entries in the wrong/random order worked fine.

    ReplyDelete
    Replies
    1. 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.

      Give it a try after doing that.

      Delete
  17. 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.
    Here'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);
    }

    ReplyDelete
    Replies
    1. This seems to be the line in question:
      var 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.

      Delete
  18. 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.

    ReplyDelete
  19. 2 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.

    ReplyDelete
  20. yes to both.
    could 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)

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. thank 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.
      Solved!

      Delete
    3. 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!

      Delete
    4. Hi Vitalis, move those empty/hidden columns towards the end of the form and it should work.

      Delete
  21. I 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';

    Can you tell me what to do?

    ReplyDelete
    Replies
    1. Hello, did you authorize the script and set the variables correctly?

      Delete
    2. Yes. 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!

      Delete
  22. I 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.

    Thanks for the script!

    ReplyDelete
    Replies
    1. I 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

      Delete
  23. Hey David,
    I 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.

    ReplyDelete
    Replies
    1. 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.

      Delete
  24. What is the doc key?

    ReplyDelete
    Replies
    1. When you create a form in Google Docs, your URL for that form will look something like this:

      https://docs.google.com/spreadsheet/ccc?key=XXXXXXXXXXXXXXXXXXXXXXXXXX#gid=0

      The value of "XXXXXXX" is your doc key.

      Delete
  25. 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?

    thanks for writing this article.

    Tara

    ReplyDelete
    Replies
    1. 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.

      Delete
  26. Hey 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.

    ReplyDelete
    Replies
    1. When 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.

      Delete
    2. I have the same problem- no email gets sent. There were no options to authorize "on submission of data."

      Delete
  27. I got a failure e-mail! Yay! It says:

    TypeError: Cannot call method "toString" of undefined. (line 32)

    Tara

    ReplyDelete
  28. Hey David, I started all over and I got it to work.

    I 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!

    ReplyDelete
  29. This 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!

    ReplyDelete
    Replies
    1. Hi Matt, this is the line you want to look at:

      message += headers[i] + ' = '+ e.namedValues[headers[i]].toString() + '\n\n';

      wrap it within an if to check e.namedValues[headers[i]] !=""

      Delete
    2. Awesome! Thanks so much! It work great when I tested it this morning!

      Delete
  30. noobish attack here
    I 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

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. mY BAD All Fixed thx U r the best

      Delete
  31. Hi,
    Another 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

    ReplyDelete
    Replies
    1. 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.

      Delete
  32. Thank 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!

    ReplyDelete
  33. I followed these instructions to the letter, but there's nothing sending to email. Any ideas? I'm using the correct doc key and email.

    ReplyDelete
  34. Hi David!

    Your 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!

    ReplyDelete
    Replies
    1. Hi Marcus,

      Try 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.

      Delete
  35. Hello,

    I 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

    ReplyDelete
    Replies
    1. 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.

      On 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.

      Delete
    2. Thanks so much for the quick response!

      Delete
  36. Hi 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.
    Thanks in advance. I'd be totally lost without this thread and your help.

    ReplyDelete
    Replies
    1. Hi Keith, would love to learn more about how you're using it! please email me at david@blogxpertise.com

      regarding 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.

      Delete
  37. Hi there,
    This 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?

    ReplyDelete
  38. 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:

    TypeError: 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?

    ReplyDelete
  39. Hi David,

    I 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

    ReplyDelete
  40. Hi David, thanks for this helpful script!

    Is 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!

    ReplyDelete
    Replies
    1. 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.

      Delete
  41. Thanks 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:
    https://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.

    ReplyDelete
    Replies
    1. You can send the form TO the form submitter, but not FROM the form submitter. (I think I misread your question)

      See 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.

      Delete
    2. Phew, glad to know it's possible. Thanks so much! That's really helpful.

      Delete
  42. Hello,

    firstly 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..??

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. Hi 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)..

      this 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..

      Delete
    3. Hi David,

      doesn'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.?

      Delete
    4. Try removing any spaces that you might have in the column headers

      Delete
  43. Hi David,

    Unrelated 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!

    ReplyDelete
  44. 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?

    thanks,
    Kurt

    ReplyDelete
  45. Kurt. Thanks for your post and work. I've followed your instructions, but got this error: TypeError: Cannot call method "getRange" of null
    What am I doing wrong?

    ReplyDelete
    Replies
    1. I'm getting the same error... does anyone know why?

      Delete
  46. Hey Guys,

    Thanks 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?

    ReplyDelete
  47. 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:
    getSheetByName("Sheet1")

    Also took out the sample question columns not sure if empty columns are problematic.

    Anyone know how to customize the "from" email address ?

    ReplyDelete
    Replies
    1. 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?

      Delete
  48. Thank you ;)
    Work great !

    Just read the "Common issues" to qolve my issues ^^

    ReplyDelete
  49. Thank you for this wonderful script! I know absolutely nothing about this stuff so I was so happy to stumble upon this.....however...

    I 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?

    ReplyDelete
    Replies
    1. Please contact me at David@confluentforms.com if you need personalized assistance.

      Delete
  50. Thanks 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!

    ReplyDelete
  51. David - Thanks for the script... works great!

    Is 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?

    ReplyDelete
    Replies
    1. Yes, that should be possible, but would require some coding. Please see the contact form regarding hiring us for customization if you're interested.

      Delete
  52. If 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!

    ReplyDelete
  53. So... I am running on the following error:

    TypeError: Cannot read property "values" from undefined.



    Thank you for the great script, hope it works for me :)

    ReplyDelete
  54. 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?

    ReplyDelete
  55. Sorry for that. It works now. It didn't work for some reason when I tested the form from the form menu.

    ReplyDelete
  56. David!!! 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!

    if anyone is interested in the code, let me know :)

    ReplyDelete
  57. hello there, one one for I am constantly receiving - TypeError: Cannot call method "toString" of undefined

    I 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

    ReplyDelete
  58. Are you getting this message when content is submitted via your form?

    ReplyDelete
  59. I'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)
    What is the e object?

    ReplyDelete
  60. "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?

    ReplyDelete
  61. I'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.

    ReplyDelete
  62. Can you tell me how/when you're getting this error? Is it when you're submitting through your form?

    ReplyDelete
  63. Hi David, please help. TypeError: Cannot call method "getRange" of null. (line 34, file "Code"). Thank you

    ReplyDelete
  64. Can you tell me how/when you're getting this error? Is it when you're submitting through your form?

    ReplyDelete
  65. Yes, count the columns. If you have 8 columns, replace it with the number 8.

    ReplyDelete
  66. i'm getting an error online 20 that says Missing ) after argument list. (line 20, file "Code") -


    what am i doing wrong? all i've done is cut and paste the url to the spreadsheet

    ReplyDelete
  67. Hello I'm getting an issue in this line:

    message += headers[i] + ' = '+ e.values[i].toString() + '\n\n';



    What I'm i doing wrong?

    ReplyDelete
  68. can you paste the complete code that you have?

    ReplyDelete
  69. can you paste the complete code that you have? would love to see it so I can find the syntax error

    ReplyDelete
  70. what is the error message that you're getting?

    ReplyDelete
  71. I have the same issue.

    It 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?

    ReplyDelete
  72. that seems like your dockey is incorrect. did you change the dockey variable value?

    ReplyDelete
  73. Hi 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:

    TypeError: 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);
    }

    ReplyDelete
  74. 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"

    ReplyDelete
  75. O-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.

    **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);

    }

    ReplyDelete
  76. This part is wrong:

    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];

    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];

    ReplyDelete
  77. ok, that is edited. Now I get the error below for line 38:

    Line 38: message += headers[i] + ' = '+ e.values[i].toString() + '\n\n';

    Error: Cannot read property "values" from undefined. (line 38, file "Code")

    ReplyDelete
  78. 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.

    ReplyDelete
  79. Thanks David. The form submits into the spreadsheet but does not email me.

    ReplyDelete
  80. Actually, I just received an email but it was an error message from google:

    TypeError: Cannot call method "toString" of undefined. (line 38, file "Code")

    ReplyDelete
  81. email me your code at david@confluentforms.com

    ReplyDelete
  82. I received the same error when submitting through the form. Could you help with the solution? I certainly appreciate your help.

    ReplyDelete
  83. Can 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!

    ReplyDelete
  84. No, when I run the code. I only ran the code when I tested the form+code and it didn't work.

    This 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

    }

    ReplyDelete
  85. I also got the same problem.

    TypeError: 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!

    ReplyDelete
  86. Very useful, thank you. Saved me lots of time and headache!

    ReplyDelete
  87. I 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.

    ReplyDelete
  88. David, 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?

    Details:
    StartFunctionError MessageTriggerEnd7/9/13 11:50 AMsendFormByEmailTypeError: Cannot call method "toString" of undefined. (line 38, file "Code")formSubmit7/9/13 11:50 AM

    ReplyDelete
  89. I am having issues with the GetSheet command. My code is below and I would love some help.

    function 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);

    }

    ReplyDelete
  90. David, have had a hell of a time with this script with numerous errors:
    #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!

    ReplyDelete
  91. Yes, it is the most up to date and it does work. Please contact us if you'd like/need personal attention!

    ReplyDelete
  92. I have tried to make this script work but keep getting the same error on one of two lines, either 33 or 34.

    The 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

    ReplyDelete
  93. I 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.
    Thanks for the quick response.

    ReplyDelete
  94. 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:


    function 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);
    }

    ReplyDelete
  95. Can you send me link to the code that worked for you, My team is stuck and I'm at witts end.

    ReplyDelete
  96. Im getting error:

    TypeError: 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';

    ReplyDelete
  97. call or email us; the script works "in general" but specifics oddities are usually the culprit.

    ReplyDelete
  98. Michael, check out this video, the script will be referenced here and you can see the walk through: http://www.youtube.com/watch?v=z6klwUxRwQI

    ReplyDelete
  99. I 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
  100. @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.

    ReplyDelete
  101. For starters, remove #gid=0 from your docKey value.

    ReplyDelete
  102. Hi. 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"

    if (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.

    ReplyDelete
  103. 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

Post a Comment