Categories: Ask a "how-to" question : Sheets :

How do I send an auto-response email to someone who fills out my Google docs form?

Showing 1-46 of 46 messages
How do I send an auto-response email to someone who fills out my Google docs form? i.a.nixon 5/9/11 2:16 AM
I have created a Google docs form and would like an automated email to be send to the responder, as soon as they press 'submit'.

Anyone got an idea?



Re: How do I send an auto-response email to someone who fills out my Google docs form? yachadhoo 5/10/11 7:41 PM

Sure...I just played around with this and got it to work.  :)

1) On the spreadsheet where the form submissions populate, put this formula in a cell in row one in a column to the right of all data being populated by the form, say cell X1:

=indirect("C"&counta(A1:A))

But substitute the Column letter for the email address submission question for “C”. i.e. if Question #10 is for them to enter their email address, and it populates Column K, then change the formula to:

=indirect("K"&counta(A1:A))

RESULT: places the email of the most recent submission into cell X1

2) Create and save a script, as follows:

function EmailFormConfirmation() {

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 

  var sheet = ss.getSheetByName("Submissions");

  var email = sheet.getRange(1,24,1,1).getValue();  

  var message = "<HTML><BODY>"

        + "<br /><br />"

        + "<P>This is an automated form completion email.  Thanks for submitting!!!"

        + "</HTML></BODY>";      

      MailApp.sendEmail(email, "Form Completion Email", "", {htmlBody: message});   

}

But substitute 24 with the number corresponding to the Column of the cell you entered the formula in step 1. i.e. if the cell is X1, this is Column X, which is Column #24

Also substitute the name of your form submission sheet for the current name, "Submissions"

Substitute your own message and subject in the above script.

3) Setup a trigger to run the above script upon form completion. Within the script editor, select "Triggers"...add a trigger to run the above script from the spreadsheet "On form submit"

= = =

And there you go!

Re: How do I send an auto-response email to someone who fills out my Google docs form? Gill 5/11/11 3:59 AM
Thank you!
Re: How do I send an auto-response email to someone who fills out my Google docs form? samiam344 5/13/11 11:35 PM
I tried to run this on my spreadsheet but get an error Cannot call method "getRange" of null. (line 7). i was able to get email address to appear in J1. here is what i am running
function EmailFormConfirmation() {

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 

  var sheet = ss.getSheetByName("Registration Form");

  var email = sheet.Range(1,10,1,1).getValue();  

  var message = "<HTML><BODY>"

        + "<br /><br />"

        + "<P>This is a confirmation email. You have been registered for class"

        + "</HTML></BODY>";      

      MailApp.sendEmail(email, "Form Completion Email", "", {htmlBody: message});

any suggestion on how to fix it would be greatly appreciated. 
Re: How do I send an auto-response email to someone who fills out my Google docs form? yachadhoo 5/14/11 5:23 AM
samiam344,
You line 7 is:
var email = sheet.Range(1,10,1,1).getValue();  

Instead, as in the example, try:
var email = sheet.getRange(1,10,1,1).getValue();  

Also, make sure that there is a valid email in J1 when you attempt to run this script.

In fact, I will add a valid email condition to my code...
Re: How do I send an auto-response email to someone who fills out my Google docs form? yachadhoo 5/14/11 8:35 AM
Ok, I modified my original script
1) Added instructions
2) Added a test to verify if the email submitted is of a valid format, and now it only sends an email if the email address is of a valid format
3) Added a confirmation notice into the Column where the indirect formula is at the top.
So, now it will now say, "Email Sent" next to each form submission, if the script worked and the email was valid
If the email address is invalid, it will say "Email not sent - Invalid Email Address submitted"

For all who stumble upon this thread via a search:

Here is my updated script that will automatically send an email to a person who fills out your form and submits a valid email address on your form.

Select and copy all of the below, then Select "Tools" => "Script Editor", highlight everything in rows 1-3, then paste, save, and enter a name for the script.  Then, within the script, follow the 3-step instructions to make the personal modifications needed to have it work with your form and spreadsheet:


function EmailFormConfirmation() {
  
  // 3-Step Instructions:
  // 1) Place the following formula into an empty cell in the first row, 
  //     in a Column to the right of Columns that populate from the form:   =indirect("C"&counta(A1:A))
  //     Change C in above formula to the Column in your sheet that has email addresses from the form 

  // 2) In the following two program lines, "var sheetname" and "var columnnumber",
  // Change the name of the sheet (currently set to Sheet1, the default name)
  // Change the column number to the number representing the Column that you placed the indirect formula, 
  // from step 1, above
  // i.e. B=2, C=3, D=4, J=10, O=15, T=20, Z=26, AA=27, AZ=52, etc...(currently set to 10, for Column J)
  
  var sheetname = "Sheet1"
  var columnnumber = 10
  
  // 3) After saving this script, select "Triggers" => "Current script's triggers" => Click to add a script
  //     Choose this script's name, select "From spreadsheet" and select "On form submit", and then save 
  // 
  //  That's it!



  // Main script follows       
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName(sheetname);
  var email = sheet.getRange(1,columnnumber).getValue();  

  // Determines row number of most recent form submission and sets it as "lastrow"
  if (sheet.getRange(sheet.getMaxRows(),1).getValue() != "") {
    var lastrow = sheet.getMaxRows()
        } 
  else {
      var count = 0
      for (var i = 0; i < sheet.getMaxRows(); i++) {
        if (sheet.getRange(sheet.getMaxRows()-i,1).getValue() != "") {
          var lastrow = sheet.getMaxRows()-i
          break;
        }  
      }
   }

  // Email address regex (regular expression)
  // Test for valid Email Pattern/Format - Allows any two-letter country code top level domain, 
  // and only specific generic top level domains 

  var emailPattern = /^[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|aero|asia|biz|com|coop|edu|gov|info|int|jobs|mil|mobi|name|museum|name|net|org|pro|tel|travel)\b/;

  var validEmailAddress = emailPattern.test(email); 
  
  // The following sends an email if the email pattern is valid (i.e. if the email address is of an acceptable format)  
  // Edit the text you want in the body of the email and the subject you want (send a test message to yourself to test)
                                           
    if (validEmailAddress == true) {
      var message = "<HTML><BODY>"
        + "<br /><br />"
        + "<P>This is an automated form completion email.  Thanks for submitting!!!"
        + "</HTML></BODY>";      
     MailApp.sendEmail(email, "Form Completion Email", "", {htmlBody: message}); 

  // Returns a confirmation message whether email was sent, in the Column designated in step 1
  // You can change these confirmation messages, or get rid of them altogether by making these
  // lines comment lines by adding the "//" to the beginning of the next 3 lines    
 
     sheet.getRange(lastrow,columnnumber,1,1).setValue("Email Sent");
      }  else{
     sheet.getRange(lastrow,columnnumber,1,1).setValue("Email not Sent - Invalid Email Address submitted");
}   
Re: How do I send an auto-response email to someone who fills out my Google docs form? zabethel 5/14/11 3:18 PM
Thanks. I got this to work.  From the first set of instructions I got the same line 7 error message until I realized I had put my document name in as the sheet name. *face palm*
Re: How do I send an auto-response email to someone who fills out my Google docs form? Teresa - Docs & Drive Community Manager 5/16/11 2:37 PM
@yachadhoo, thanks for helping out here!
Re: How do I send an auto-response email to someone who fills out my Google docs form? yachadhoo 5/17/11 4:08 PM
"@yachadhoo, thanks for helping out here!"

No problem.  ;)

For anybody who happens upon this thread by way of a search...

In response to another similar question, I just modified the above script to send a personal email to YOU based on a particular response to one of the questions on the form.  
You can check that out via the link, below...and modify it for your application.

:)
Re: How do I send an auto-response email to someone who fills out my Google docs form? bartagreda 5/19/11 8:15 AM
@yachadhoo

HI I added your script to my excel sheet. I know that the inderect function is working because everytime my spreadsheet gets updated it changes but the concern is its not emailing. i made sure the trigger is confiugred but the notificatoin does not come up only the default response that you get/

Your ideas are greatly appreciated. Thanks,

Btw here's the trimmed down script i removed ll your comments

function myFunction() {
  function EmailFormConfirmation() {
  
  var sheetname = "Sheet1"
  var columnnumber = 6
     
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName(sheetname);
  var email = sheet.getRange(1,columnnumber).getValue();  

  if (sheet.getRange(sheet.getMaxRows(),1).getValue() != "") {
    var lastrow = sheet.getMaxRows()
        } 
  else {
      var count = 0
      for (var i = 0; i < sheet.getMaxRows(); i++) {
        if (sheet.getRange(sheet.getMaxRows()-i,1).getValue() != "") {
          var lastrow = sheet.getMaxRows()-i
          break;
        }  
      }
   }


  var emailPattern = /^[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|aero|asia|biz|com|coop|edu|gov|info|int|jobs|mil|mobi|name|museum|name|net|org|pro|tel|travel)\b/;

  var validEmailAddress = emailPattern.test(email); 
 
                                           
    if (validEmailAddress == true) {
      var message = "<HTML><BODY>"
        + "<br /><br />"
        + "<P>This is an automated form completion email.  Thanks for submitting!!!"
        + "</HTML></BODY>";      
     MailApp.sendEmail(email, "Form Completion Email", "", {htmlBody: message}); 
     sheet.getRange(lastrow,columnnumber,1,1).setValue("Email Sent");
      }  else{
     sheet.getRange(lastrow,columnnumber,1,1).setValue("Email not Sent - Invalid Email Address submitted");
}   
}
Re: How do I send an auto-response email to someone who fills out my Google docs form? yachadhoo 5/19/11 2:06 PM
bartagreda,
You skipped the step in bold, in my instructions:
"Select and copy all of the below, then Select "Tools" => "Script Editor", highlight everything in rows 1-3, then paste, save, and enter a name for the script.  Then, within the script, follow the 3-step instructions to make the personal modifications needed to have it work with your form and spreadsheet:"

Delete the first line your script:
function myFunction() {

And delete the last line of your script:
}

...and then try again.

:)
Re: How do I send an auto-response email to someone who fills out my Google docs form? yachadhoo 5/19/11 2:11 PM
Here is the comment-free version of the script that you can copy and paste:

 function EmailFormConfirmation() {
  
  var sheetname = "Sheet1"
  var columnnumber = 6
     
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName(sheetname);
  var email = sheet.getRange(1,columnnumber).getValue();  

  if (sheet.getRange(sheet.getMaxRows(),1).getValue() != "") {
    var lastrow = sheet.getMaxRows()
        } 
  else {
      var count = 0
      for (var i = 0; i < sheet.getMaxRows(); i++) {
        if (sheet.getRange(sheet.getMaxRows()-i,1).getValue() != "") {
          var lastrow = sheet.getMaxRows()-i
          break;
        }  
      }
   }

  var validEmailAddress = emailPattern.test(email);   var emailPattern = /^[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|aero|asia|biz|com|coop|edu|gov|info|int|jobs|mil|mobi|name|museum|name|net|org|pro|tel|travel)\b/;

    if (validEmailAddress == true) {                                           
      var message = "<HTML><BODY>"
        + "<br /><br />"
        + "<P>This is an automated form completion email.  Thanks for submitting!!!"
        + "</HTML></BODY>";      
     MailApp.sendEmail(email, "Form Completion Email", "", {htmlBody: message}); 
     sheet.getRange(lastrow,columnnumber,1,1).setValue("Email Sent");
      }  else{
     sheet.getRange(lastrow,columnnumber,1,1).setValue("Email not Sent - Invalid Email Address submitted");
}   
Re: How do I send an auto-response email to someone who fills out my Google docs form? samiam344 5/21/11 1:58 PM
Finally got it to work thanks. 

Re: How do I send an auto-response email to someone who fills out my Google docs form? lmt125 5/25/11 10:09 AM
Hi, this script looks great. I am trying to add it to my form but it is not sending an email nor populating anything like Email Sent etc...here is what I put in the script...Also the Formula =indirect(E,counta(A:A1)) I am putting in column M which is the first row without any data from my form...it populates emails but how do I make it do it for new entries...Kind of new to this...thanks for any help =). The email addresses are in column E and the sheet is called sheet1...

function FBContactUs() {

 
  var sheetname = "Sheet1"
  var columnnumber = 5
    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(Sheet1);
  var email = sheet.getRange(1,13).getValue(); 

  if (sheet.getRange(sheet.getMaxRows(),1).getValue() != "") {
    var lastrow = sheet.getMaxRows()
        }
  else {
      var count = 0
      for (var i = 0; i < sheet.getMaxRows(); i++) {
        if (sheet.getRange(sheet.getMaxRows()-i,1).getValue() != "") {
          var lastrow = sheet.getMaxRows()-i
          break;
        } 
      }
   }

  var validEmailAddress = emailPattern.test(email);   var emailPattern = /^[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|aero|asia|biz|com|coop|edu|gov|info|int|jobs|mil|mobi|name|museum|name|net|org|pro|tel|travel)\b/;

    if (validEmailAddress == true) {                                          
      var message = "<HTML><BODY>"
        + "<br /><br />"
        + "<P>This is an automated form completion email.  Trial!!!"
        + "</HTML></BODY>";     
     MailApp.sendEmail(email, "Trial Email", "", {htmlBody: message});
     sheet.getRange(lastrow,13,1,1).setValue("Email Sent");
      }  else{
     sheet.getRange(lastrow,13,1,1).setValue("Email not Sent - Invalid Email Address submitted");
}  
}  
Re: How do I send an auto-response email to someone who fills out my Google docs form? lmt125 5/25/11 11:25 AM
Ok, I got it to return the email address on the form...but not much else...doesn't seem complicated.
Re: How do I send an auto-response email to someone who fills out my Google docs form? yachadhoo 5/25/11 1:15 PM
lmt125,
You changed a few things in the script that were not suppose to be changed.
I am thinking that my instructions must not be very clear.
Therefore, I am writing them again from scratch, for the sake of others who happen upon this thread.  They will be below, and should work for you.
 
:)
Re: How do I send an auto-response email to someone who fills out my Google docs form? yachadhoo 5/25/11 4:35 PM
OK, here is a simple version for anybody interested in automatically sending a customizable form completion email to someone who fills out your form.
  
Please follow the instructions exactly as written:

1) Select and copy the entire script below.  Select "Tools" => "Script Editor".  Highlight and delete everything in rows 1-3 and then paste the below script.  Save the script and name it "FormConfirmationEmail". 

2) In the script, only change that which is in BOLD (in the first few lines):

Change YYYYYYY to name of the sheet/tab that the form populates.  This is NOT the name of the spreadsheet, but the name of the sheet (which is Sheet1 by default, unless you changed it).  For example, if you have not changed the name of the sheet, and it is Sheet1, then the line in the script should be:

var sheetname = "Sheet1"

Change ZZZZZZZ to the Column number that the Emails are in. A=1, G=7 Z=26, AA=27, AZ=52, etc.  For example, if the Emails are in Column E, change the line to:

var columnnumber = 5
 
[Do not change anything else in the script at this point]

4) Save the script again.  Select "Triggers" => "Current script's triggers" => Click to add a script.  Choose "FormConfirmationEmail", select "From spreadsheet" and select "On form submit", and then save.

5) Fill out the form yourself, and enter your email address as a test
6) Confirm that the email sent.
7) Finally, adjust the message in the script and the subject of the email in the script to whatever you want. 



function FormConfirmationEmail() {

  var sheetname = "YYYYYYY"
  var columnnumber = ZZZZZZZ
     
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName(sheetname);
    
  if (sheet.getRange(sheet.getMaxRows(),1).getValue() != "") {
     var lastrow = sheet.getMaxRows()    
  } else {
     var count = 0
     for (var i = 0; i < sheet.getMaxRows(); i++) {
        if (sheet.getRange(sheet.getMaxRows()-i,1).getValue() != "") {
        var lastrow = sheet.getMaxRows()-i
        break;
        }  
     }
  }
  
  var email = sheet.getRange(lastrow,columnnumber).getValue();  
  var emailPattern = /^[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|aero|asia|biz|com|coop|edu|gov|info|int|jobs|mil|mobi|name|museum|name|net|org|pro|tel|travel)\b/;
  var validEmailAddress = emailPattern.test(email); 
  
  if (validEmailAddress == true) {
    var message = "<HTML><BODY>"
     + "<P>This is an automated form completion email.  Thanks for submitting!"
     + "</HTML></BODY>";      
     MailApp.sendEmail(email, "Form Completion Email", "", {htmlBody: message});      
  }
}
Re: How do I send an auto-response email to someone who fills out my Google docs form? lmt125 5/26/11 4:41 AM
Thank you...I'll try this in about an hour when I'm by my computer and let you know...thanks for your help.
Re: How do I send an auto-response email to someone who fills out my Google docs form? lmt125 5/26/11 5:42 AM
Great, it sends the email...the only question is on the spreadsheet where I put the formula =indirect(E &counta(A1:A))
I put this in M2, am i supposed to put in M1? How will it copy to the rest of the cells...I get the email address there, not "Email Sent"...
Re: How do I send an auto-response email to someone who fills out my Google docs form? yachadhoo 5/26/11 7:15 AM
lmt125,
If you followed the instructions in the post 3 above this one, you no longer need the indirect function at all.  You can safely delete it from M2 altogether.
 
If you want the "Email Sent" message to appear in Column M, then add the following to the very bottom of the script, immediately above the two final brackets "}":
Re: How do I send an auto-response email to someone who fills out my Google docs form? HappyJess 5/26/11 7:55 AM
Yachadhoo -

I knew absolutely nothing about scripts before I read this thread. You've really helped me A TON, and made my job a whole lot easier. Thanks so much!

Here is a new problem for you though:

I set up three separate scripts for the same form, because submitters send three different email addresses with their form entry. (It is summer camp registration; we need each parent's email and the student's email as well.) The student is only required to enter their own email address, but we hope that they will also enter their parent's as well.

So, here is the problem:

If  "John" submits a form entry with three email addresses (John, John's Mom, John's Dad)
and 10 minutes later "Bob" submits a form entry two email addresses (Bob and Bob's Dad)

When Bob hit's "Submit" - the following three people get a response email: Bob, John's Mom, and Bob's Dad.

This is because "John's Mom"'s email address is still populating the cell field for the auto-reply email, because Bob didn't submit something new.

I've temporarily fixed this by requiring the students to submit three email addresses, and instructed them to just re-enter their email address if they don't have mom or dad's. If that happens, however, they are going to get two email responses, which is a bummer, but not as bad as someone random getting two , three, four, or goodness knows how many responses until someone else populates that field with a new email address.

My trigger is for "On form submit." I don't want it to be for "on form modification", because we are constantly modifying other sheets of the same document.

Any suggestions?




Re: How do I send an auto-response email to someone who fills out my Google docs form? lmt125 5/26/11 8:32 AM
Yachadoo,

Thanks so much for all your help...am very new to html and I just copy and paste, but was able to add spaces and links to my confirmation email...learning little by little!
Re: How do I send an auto-response email to someone who fills out my Google docs form? yachadhoo 5/26/11 8:48 AM
"Any suggestions?" -HappyJess
 
Well, I am not sure how you have set this up, exactly...
Because the examples I have provided in this thread were designed to avoid this very problem you seem to be having.
 
If you want to share the spreadsheet, I'd be happy to take a look at it for you.  (you could make a copy of your current sheet and share that, if you prefer...and then share the link to it in here)
 
Also, I would recommend only have 1 script, and have it send various emails, as necessary, rather than three different scripts all triggered by a form submit, as well.
So, one form submit, one script, and multiple emails as desired.  In fact, the email messages could all be different, as well.  One message for parents, one for the student.
 
-
 
But the scripts are designed to email only the most recent submitter, and also only if they provide a valid email address.  If it is left blank, the script would email no one, as a blank is not a valid email.  It definitely should not email the previous submitter!
 
 
Re: How do I send an auto-response email to someone who fills out my Google docs form? yachadhoo 5/26/11 8:50 AM
"Thanks so much for all your help...am very new to html and I just copy and paste, but was able to add spaces and links to my confirmation email...learning little by little!" -lmt125
 
No problem...and be encouraged:  I am fairly new to all of this, too!  ;)
Re: How do I send an auto-response email to someone who fills out my Google docs form? raulpino 5/31/11 12:02 PM
Teresa:

I have used the instructions at the top of this page.  Created the script, changed the variables to fit my spreadsheet, but I get "invalid email" every time I submit a test. I'm a missing something when using the regular expression?

can I just remove the email verify test and have it send an email, even if the email is invalid?

Thanks for your help!
Re: How do I send an auto-response email to someone who fills out my Google docs form? yachadhoo 5/31/11 12:12 PM
raulpino,
Instead of using the instructions at the top of the page, try using the following.  This is a simplified version with easier to understand and more explicit instructions.
 
;)
 
= = = = = =
 
1) Select and copy the entire script below.  Select "Tools" => "Script Editor".  Highlight and delete everything in rows 1-3 and then paste the below script.  Save the script and name it "FormConfirmationEmail". 

2) In the script, only change that which is in BOLD (in the first few lines):

Change YYYYYYY to name of the sheet/tab that the form populates.  This is NOT the name of the spreadsheet, but the name of the sheet (which is Sheet1 by default, unless you changed it).  For example, if you have not changed the name of the sheet, and it is Sheet1, then the line in the script should be:

var sheetname = "Sheet1"

Change ZZZZZZZ to the Column number that the Emails are in. A=1, G=7 Z=26, AA=27, AZ=52, etc.  For example, if the Emails are in Column E, change the line to:

var columnnumber = 5
 
[Do not change anything else in the script at this point]

4) Save the script again.  Select "Triggers" => "Current script's triggers" => Click to add a script.  Choose "FormConfirmationEmail", select "From spreadsheet" and select "On form submit", and then save.

5) Fill out the form yourself, and enter your email address as a test
6) Confirm that the email sent.
7) Finally, adjust the message in the script and the subject of the email in the script to whatever you want. 



function FormConfirmationEmail() {

  var sheetname = "YYYYYYY"
  var columnnumber = ZZZZZZZ
     
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName(sheetname);
    
  if (sheet.getRange(sheet.getMaxRows(),1).getValue() != "") {
     var lastrow = sheet.getMaxRows()    
  } else {
     var count = 0
     for (var i = 0; i < sheet.getMaxRows(); i++) {
        if (sheet.getRange(sheet.getMaxRows()-i,1).getValue() != "") {
        var lastrow = sheet.getMaxRows()-i
        break;
        }  
     }
  }
  
  var email = sheet.getRange(lastrow,columnnumber).getValue();  
  var emailPattern = /^[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|aero|asia|biz|com|coop|edu|gov|info|int|jobs|mil|mobi|name|museum|name|net|org|pro|tel|travel)\b/;
  var validEmailAddress = emailPattern.test(email); 
  
  if (validEmailAddress == true) {
    var message = "<HTML><BODY>"
     + "<P>This is an automated form completion email.  Thanks for submitting!"
     + "</HTML></BODY>";      
     MailApp.sendEmail(email, "Form Completion Email", "", {htmlBody: message});      
  }
}
Re: How do I send an auto-response email to someone who fills out my Google docs form? raulpino 5/31/11 12:54 PM
Thanks! It worked.

Now that I see the email being sent, is there a way to assign the email to come from a different user, or different domain alias?
Re: How do I send an auto-response email to someone who fills out my Google docs form? yachadhoo 5/31/11 1:09 PM
"Now that I see the email being sent, is there a way to assign the email to come from a different user, or different domain alias?"
= = =

The email will always come from the email address registered to the account a user is logged into when writing/saving a script.  I don't know of any way to change this.

So, if you want it to come from a different user or company email, then you'd have to "Share" the spreadsheet with editing privileges with that other user/company account.  Then, that other user/alias (or you signed in to a company account) would have to then open the spreadsheet and paste/save the script and enable the trigger.

If you do this, you could also have the other user/alias/company account share the actual script with you/your main account, and then you could make changes/edit the script on your main account, and the email would still be from the other user/alias/company account email.

:)
Re: How do I send an auto-response email to someone who fills out my Google docs form? raulpino 5/31/11 1:46 PM
Thanks, yachadhoo.

I am waiting or my MX records on the secondary domain to finish validating so that I can have a different user/domain send the auto-reply email.

Thanks again for your help.
Re: How do I send an auto-response email to someone who fills out my Google docs form? lmt125 6/1/11 6:36 AM
What if I also want it to send me an email with the data the person input to the form? How can I do that?
Re: How do I send an auto-response email to someone who fills out my Google docs form? Yirba 6/3/11 4:33 PM
@lmt125: This is fairly straightforward.

1. Go to Google Docs - Home <http://docs.google.com/>
2. You should see a list of your documents, including your form. Click on the link for your form to open it in spreadsheet (results) view.
3. In the top-right corner of the screen, there should be a 'Share' button. Click the drop-down arrow next to it and select 'Set notification rules'.
4. Tick the box next to 'A user submits a form'.
5. You can either choose 'daily digest' or 'straight away'. 'Daily digest' will send you one email each day that someone has submitted the form. 'Straight away' sends you an email as soon as someone submits the form.
6. Click the 'Save' button.

Done! :-D
Re: How do I send an auto-response email to someone who fills out my Google docs form? lmt125 6/4/11 5:30 AM
Yirba, thanks for your response but someone else helped me with a script that sends the person who filled out a form a confirmation email ac/dc it sends me an email with the details the person filled out...let me know I can cot the script on to here =)
Re: How do I send an auto-response email to someone who fills out my Google docs form? raulpino 6/4/11 8:39 AM
Lmt125:

Can you please do so? I was wondering if this was possible.

I would appreciate it!
Re: How do I send an auto-response email to someone who fills out my Google docs form? lmt125 6/6/11 6:00 AM
Things to change are in bold...make a second sheet in the form that says Email and in there put the auto response things you want it to send also put Body Line 1 etc next to what you write in the Email example A1 says Hello B1 says Body Line 1 for subject example F1 Thanks F2 Subject Line...Also look at the source code to get var senderEmail...if you change the order of the columns in the spreadsheet make sure the email hasn't changed if not change it again in the script...Also in w/e order you have the columns in the spreadsheet is the order you will get in the email it sends to you so rearrange it accordingly.

function sendConfirmationEmail() {
  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var submissionSS = Spreadsheet.getSheets()[0];
  var emailBodySS = Spreadsheet.getSheetByName('Email');
 
  var subMittedData = submissionSS.getRange(submissionSS.getLastRow(), 2, 1, submissionSS.getLastColumn()-1)
      .getValues();
  var header = submissionSS.getRange(1,2,1,submissionSS.getLastColumn()-1).getValues();
  var senderEmail = subMittedData[0][2]; Get this if it in column B then it is [0][1] always -1 C=2 etc...
 
  //Sned mail to user
  var mailTextArray = emailBodySS.getRange(1, 1, emailBodySS.getLastRow(), 1).getValues();
  var name1 = mailTextArray[4][0]; Get this from the email sheet, if it is on the 5th row then it is 4 not 5: [4.0]
  var subject1 = mailTextArray[0][0]; Get this from the email sheet
  var htmlBody1 = '';
      for (var i=1; i< emailBodySS.getLastRow(); i++){
        htmlBody1 += '<br/><br/>'+mailTextArray[i][0];
      }
  sendMail(senderEmail,subject1,htmlBody1,name1);
 
  //Send mail to Admin or yourself
  var adminEmail = 'your email';//Comma separated mailIds
  var subject2 = 'subject';
  var htmlBody2 = '<table><tbody>';
  for (var j = 0; j < submissionSS.getLastColumn()-1; j++){
    htmlBody2 += '<tr><td><b>'+header[0][j]+'</b></td><td>'+subMittedData[0][j]+'</td></tr>'
  }
 htmlBody2 += '</tbody></table><br/><br/>';
  htmlBody2 += '<a href = "'+Spreadsheet.getUrl()+'">Check it here</a>'
  sendMail(adminEmail,subject2,htmlBody2,name1);
}

function sendMail(recepient,subject,htmlBody,name){
  MailApp.sendEmail( recepient, subject,'body',  {htmlBody : htmlBody, name : name});
}
Re: How do I send an auto-response email to someone who fills out my Google docs form? dlund100 6/9/11 6:36 AM
Imt125 - Thank you for putting up this script.  I was looking for something very similiar!
 
I am needing a script that will email the newly submitted data to an email address included in the form.  I was able to use yachadoo's script to get an automatic email to the email in the form, now I just need to figure out your script to include all the data.  I think I would need the column titles and data.  Any ideas?
Re: How do I send an auto-response email to someone who fills out my Google docs form? Denis de Castro 6/9/11 8:14 AM
@ dlund100, could I recommend the work of Henrique Abreu on this ? Available at http://sites.google.com/site/formemailer/form-emailer. Other lighter-weight emailers are available in the script templates (Insert>Script...>Gallery)
Re: How do I send an auto-response email to someone who fills out my Google docs form? yachadhoo 6/9/11 11:32 AM
dlund100,
I am very much a beginner in scripts compared to Henrique Abreu, so the reference that Denis refers to might be of much more value.  However, it might not be easy to adapt Henrique's scripts for your purposes.

However, if you were able to follow my simple script to send an email confirmation, you might want to have a look at the script I mentioned in the thread below [1].  You can easily adapt it to combine with the above script to populate your email with specific data in your spreadsheet.  It demonstrates how you simply create variables to pull in data from your sheet and then have it output in your email, exactly like you want it.

My scripts are very straight-forward and easily adapted, though I am sure the Gallery and Henrique have much more elegant and powerful scripts.

:)
Re: How do I send an auto-response email to someone who fills out my Google docs form? NHGoldenEye 6/20/11 10:18 AM
ok so i have the following code but all i get is the header data and not the actual submitted data?
what am i missing... all i want is the form data submitted to my email address..

function sendConfirmationEmail() {
  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var submissionSS = Spreadsheet.getSheets()[0];
  var emailBodySS = Spreadsheet.getSheetByName('Sheet1');

  var subMittedData = submissionSS.getRange(submissionSS.getLastRow(), 2, 1, submissionSS.getLastColumn()-1)
      .getValues();
  var header = submissionSS.getRange(1,2,1,submissionSS.getLastColumn()-1).getValues();
  //Send mail to Admin or yourself
  var htmlBody2 = '<table><tbody>';
  for (var j = 0; j < submissionSS.getLastColumn()-1; j++){
    htmlBody2 += '<tr><td><b>'+header[0][j]+'</b></td><td>'+subMittedData[0][j]+'</td></tr>'
  }
  htmlBody2 += '</tbody></table><br/><br/>';
  htmlBody2 += '<a href = "'+Spreadsheet.getUrl()+'">Check it here</a>'
  MailApp.sendEmail( "m...@mysite.com", "2011 Form data",'',  {htmlBody : htmlBody2});
}
Re: How do I send an auto-response email to someone who fills out my Google docs form? lmt125 6/20/11 11:58 AM
NHGoldenEye,
 
The last line MailApp.sendEmail I don't think you were supposed to change that...
MailApp.sendEmail( recepient, subject,'body',  {htmlBody : htmlBody, name : name});
}
I can share my page so you see how I have the email sheet set up...try leaving it like that instead...run the script manually first.
Re: How do I send an auto-response email to someone who fills out my Google docs form? lmt125 6/20/11 12:00 PM
You're also missing this:   //Send mail to Admin or yourself

  var adminEmail = 'your email';//Comma separated mailIds
  var subject2 = 'subject';
Try copying the code I posted before and just updating the things in bold...And make a second sheet in the same workbook that says Email and in there put Subject etc....let me know I'll share my page...
Re: How do I send an auto-response email to someone who fills out my Google docs form? lmt125 6/20/11 12:02 PM
Also this is sending a confirmation email to the person that responded which is what the second sheet of Email if for...I think you just need to put this code to have it just send you the response info...
 
 //Sned mail to user
  var mailTextArray = emailBodySS.getRange(1, 1, emailBodySS.getLastRow(), 1).getValues();
  var name1 = mailTextArray[4][0]; Get this from the email sheet, if it is on the 5th row then it is 4 not 5: [4.0]
  var subject1 = mailTextArray[0][0]; Get this from the email sheet
  var htmlBody1 = '';
      for (var i=1; i< emailBodySS.getLastRow(); i++){
        htmlBody1 += '<br/><br/>'+mailTextArray[i][0];
      }
  sendMail(senderEmail,subject1,htmlBody1,name1);
 
  //Send mail to Admin or yourself
  var adminEmail = 'your email';//Comma separated mailIds
  var subject2 = 'subject';
  var htmlBody2 = '<table><tbody>';
  for (var j = 0; j < submissionSS.getLastColumn()-1; j++){
    htmlBody2 += '<tr><td><b>'+header[0][j]+'</b></td><td>'+subMittedData[0][j]+'</td></tr>'
  }
 htmlBody2 += '</tbody></table><br/><br/>';
  htmlBody2 += '<a href = "'+Spreadsheet.getUrl()+'">Check it here</a>'
  sendMail(adminEmail,subject2,htmlBody2,name1);
}

function sendMail(recepient,subject,htmlBody,name){

  MailApp.sendEmail( recepient, subject,'body',  {htmlBody : htmlBody, name : name});
}
Re: How do I send an auto-response email to someone who fills out my Google docs form? NHGoldenEye 6/20/11 12:15 PM
yea i dont need to send a copy to the submitter just myself/admin so i removed that unneeded portion.
I changed the VAR's to hard coded because i didnt need to manipulate them as variables , just use the raw data. 
Re: How do I send an auto-response email to someone who fills out my Google docs form? NHGoldenEye 6/20/11 12:17 PM
i think the issue has to do with the loop and the 'subMittedData'

seams that this VAR is not populated correctly because the data i get back says "undefined"


 var subMittedData = submissionSS.getRange(submissionSS.getLastRow(), 2, 1, submissionSS.getLastColumn()-1).getValues();

the email does send and the header column text shows but not the form submitted data
Re: How do I send an auto-response email to someone who fills out my Google docs form? lmt125 6/20/11 12:23 PM
I don't know what to tell you...it works perfect for me...
Re: How do I send an auto-response email to someone who fills out my Google docs form? NHGoldenEye 6/20/11 12:54 PM
i tried the code from above, changed the right elements and it still did not work... :(
Re: How do I send an auto-response email to someone who fills out my Google docs form? lmt125 6/20/11 1:05 PM
Why don't you contact this guy...he wrote the script for my form...really nice...https://sites.google.com/site/appsscripttutorial/contact-me
More topics »