In this short tech tip, we are going to look at how to make a Google Forms send a confirmation email to the user each time they submit a form. This is just a basic template for how to make this happen, so it is worth mentioning that there are plenty of other ways to accomplish this one task and equally as many different things we could have this form do.
In the following steps, I am just going to give a brief overview of the different steps, so make sure to watch the video if you want to see HOW to complete this task.
-
Create a Google Form that accepts some form of user input and create an attached Google Sheet to store the form submissions. Your input fields can really be any of the options that Google Forms gives you. My advice is to think about how best to eliminate confusion for your user. This will ensure that they don’t send you bogus data, or something other than what you asked for.
-
Once you have created the form, open up the corresponding spreadsheet. Google Sheets use zero indexing, meaning that the leftmost column is column ‘0’ and the column directly to the right of column ‘0’ is column ‘1’ and so on. It isn’t a particularly difficult concept to grasp, but it will be important when we start to pull data from the spreadsheet.
-
Go to the ‘Tools’ menu in the spreadsheet and open up the ‘script editor.’ Once the script editor window opens, create a blank project. You should see the beginnings of a function that looks something like this: function myFunction (){}
-
This first thing we are going to do is pass this function an event parameter by placing a lowercase ‘e’ in between the parentheses: (e); this event holds all of the spreadsheet data that we are going to pull from.
-
Next, in between the curly braces we are going to define some variables to store some of the specific values we need from the spreadsheet. We are going to use ‘e.values[1]’ to pull in the data that the user submitted to the first column, which in the example video would correspond to ‘Name’ (remember zero indexing here). We will assign ‘e.values[1]’ to a variable name appropriately ‘Name’: var name = e.values[1];
-
Complete this same step to pull in the other data you want and also create variables for the body of the message and the subject line of the email the script will send.
-
Once you have defined all of the variable you want to be included in the email, you are going to call the Mail.App method and pass it three arguments, recipient, subject, and body.
-
The last step is setting a trigger to run the script you just created. In the ‘Resources’ menu, select the ‘current project’s triggers.’ You should see a menu that says there are no triggers for this project, so create a new trigger. We are going to want to create a trigger that runs ‘myFunction’ to process the spreadsheet event ‘on form submit,’ which is the far right drop down menu. Now, any time a user submits a form , this script should run. You will have to authorize Google to access the spreadsheets and you Gmail account to make this work.
-
-
The last step involves submitting some data to the form to perform a quick test.
If you have any questions about how do some of the programming needed for this example, take a look at some of the Javascript tutorials located here at CodeAcademy or some of the tutorials on Google App Script located here.
Here is the code for the script, but note that this will change based on your spreadsheet:
function myFunction(e){
var userName = e.values[1];
var userEmail = e.values[2];
var date = e.values[3];
var subject = "Form Submitted";
var message = "Thank you, " + userName + " for choosing " +date;
MailApp.sendEmail (userEmail, subject, message);}
Hi Jeff
The code for the script and all of the information has been incredibly helpful. Thank you. Is there any way to format the text in the message so it does not all run together?
Thanks
Stacey
Hi Stacey,
There is a new line character (n) that you can use to separate lines of the email message. You can look at some examples here: http://stackoverflow.com/questions/16208927/in-google-apps-script-how-can-i-preserve-newlines-from-user-input
JE
Hi,
thanks for code’s lines.
I implemented the code but all e-mail notifications instead to go to user that have sended the Subscription form arrive to me that I’m the creator.
How can I fix this problem ?
Bye
You probably just got the send to and send from email mixed up in the code. Can you post your code here so that I can take a look?
I have tried the script as follows but the debugger says that the second line is not defined. I wonder what is wrong. The script does not send the email out.
function myFunction(e){
var userName = e.values[1];
var userEmail = e.values[6];
var CreditHoursNow = e.values[3];
var QualityPointSum = e.values[4];
var AverageCreditHours = e.values[5];
var TotalQualityPointsTimesCreditHours = e.values[7];
var EstimatedGPA = e.values[8];
var subject = “Form Submitted” ;
var message = “Thank you, ” + userName + “for requesting this valuable information ” ;
MailApp.sendEmail(userName, userEmail, CreditHoursNow, QualityPointSum, AverageCreditHours, TotalQualityPointsTimesCreditHours, EstimatedGPA, subject, message);
}
It gets hung up in line 2. Thanks for any feedback that you wish to give.
This is a pretty common error, so I’m sure it is addressed 10+ time in the comments on this post. I’ve been meaning to publish a post dedicated to this error, so take a look at that and comment back if you have questions: http://www.jeffreyeverhart.com/2016/08/06/fix-typeerror-cannot-read-property-values-undefined/
There are some other things in here that you need to look at though. When you call sendEmail, it is only expecting three arguments (userEmail, subject, message), so you will need to template out the message for all of the other variables the same way you do with userName. See an example here: https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String).
Other than that, it looks good. Let me know if you need me to take another look.
Thanks for the script. Its really very useful.
Can i format the content of the mail to apply colors, bold etc? Ex I would like the score to be in bold and red. Is this possible?
Also is there any way that we can insert an image in the mail?
Thanks!
Yes, but you will need to format the message as HTML. Check out this link here and look for the example that contains a parameter in the MailApp called htmlBody: https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(Object)
Great tutorial! I was looking for something like this. One thing I still can’t figure out though. Is there a way to include data in the email message that comes from cells outside the range where the submission data is stored?
You use columns A through D in your example. What if you had an array formula in E1 that calculated a value based on the submitted values (like grading a quiz that the form was used for). I’ve tried adding a new variable using-
var score = e.values[4];
but the result in the email says undefined. It seems like the script is executing before the formula can update. I also tried adding a pause using Utilities.sleep(3000) but that didn’t help.
Yea this is tricky since the e.values[4] is actually taking those values from the form as it submits to the spreadsheet since we have the trigger set up to be on form submit.
One possible solution would be programming a new variable in the script itself that would calculate what you are doing in the spreadsheet and print it in the email.
The other option would be to use the getRange() method and set up a different trigger, although this might get a bit more complicated. Here is a link to more info on how to use getRange() or getDataRange(): https://developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange()
var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet = ss.getSheets()[0];
// Returns the first cell
var cell = sheet.getRange("A1");
I have the same issue here:
Here is my code
ss = SpreadsheetApp.getActiveSheet();
columns = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];
Not sure how to edit it in order for the confirmation email to catch my array formula and showing the total numbers on the email.
Please help out. Thanks in advance.
Ok, so first you will need to figure out what type of data is being stored in the var columns array. To do that, you could run console.log(columns); and see what kind of data is returned. Then there are a couple of methods you could run, like columns[index of item].toString() to print it to a string you could use elsewhere.
Hi, I tried your script, and it did not work. When I clicked the “run” button, it said
TypeError: Cannot read property “values” from undefined. Line 2.
This refers to the “Var Username” line, but I can’t figure out what is wrong with it.
HERE is the script I used:
function myFunction(e) {
var Username = e.values[1];
var Useremail = e.values[2];
var Date = e.values[3];
var Subject = “Thank you for your MEAC Request Form Submission”;
var Message = “Thank you” + Username + “for submitting an event request to the MEAC. The General Manager will be in touch with you via email to confirm details and dates”;
MailApp.sendEmail(Useremail, subject, Message)
Also, once this works, is there a way to set it up so that I get a copy of the confirmation email too?
Okay, so I did get the script to work with one of my regular Gmail addresses, but it will not give me a response email if I use my schoolsystem email account, which is run by google but ends in .net instead of gmail.com. Is there something about the school network Google accounts that is keeping this script from working with those maybe? Maybe there is some kind of permission or something that needs to be bypassed? Any Ideas?
Yes, sometimes these emails can get caught in SPAM folders. Since they are automatically generated, they can look like phishing scams to a mail server. You could get in touch with you IT department to add the address sending the notifications is added to the safe senders list.
Please see my response to NS’s question in the comments thread.
Hi. I had similar problem. I deleted my script and created a new script and this worked. Thanks for the script! This was my first!
Hi, thanks for this helpful tutorial. I’m continuing to get this message when I go to Run the script: TypeError: Cannot read property “values” from undefined. (line 2, file “Code”)
Here’s what I have in the code:
function myFunction(e){
var Event = e.values[1];
var userName = e.values[3];
var userEmail = e.values[4];
var subject = “Campus Connections Reservation Confirmation”;
var message = “Thank you,” + userName + “, for reserving a resource table for the following upcoming Campus Connections events:” + Event;
MailApp.sendEmail(userEmail, subject, message);
}
What do I need to edit?
Thanks!
If you are running the script in the script editor, this error is normal since the function does not have an event (form data) to process into the variables. If that is your only error, try saving it and actually submitting a test form and see what happens. It should work based on your code, so let me know if you get another error.
I did what NS did and it didn’t work for me when I tried to email it to myself. I tried another email but it did not work either? Why is that?
Did you put a trigger on the script to run on form submit?
I’ll need to see the script to offer more assistance.
Thanks for this.. it was very clear. But I have tested mine and no emails are being sent. I check the logs on the script and they are empty. Any ideas?
Sorry here is my current script
function myFunction(e){
var userfName = e.values[1];
var userlName = e.values[2];
var userEmail = e.values[12];
var abTitle = e.values[11];
var abType = e.values[13];
var ab = e.values[14];
var subject = “Thank you for your abstract for AMBBS 2014”;
var message = “Thank you, ” + userfName + ” ” + userlName + “n for your abstract submission of: n” +abTitle;
MailApp.sendEmail (userEmail, subject, message);}
Your script looks good. Two questions: did you set the trigger to run the function ‘on form submit’? and, did you add this script to the form or the spreadsheet?
Any idea why I am still getting the error message? “TypeError: Cannot read property “values” from undefined. (line 2, file “Code”)”
function myFunction(e) {
var Username = e.values[2];
var Useremail = e.values[3];
var Subject = “Thank you for your response”;
var Message = “Thank you” + Username + “, for volunteering and/or joining the PTA.”;
MailApp.sendEmail(Useremail, subject, Message)}
You can’t run the script in the editor since it needs event (form submission) data to place in the variable you’ve created. Have you tried setting the trigger and submitting a test form with your email as a way to test the script?
I have tried setting the trigger and submitting a test form. Unfortunately, it still does not work. I am getting notifications that say Line 7 Subject not defined, but as you can see in the code above, I did define a subject. Any other ideas? Thank you!
Hi
I was able to get the original script to work two times before I tried to customize it. I am trying to send random confirmation codes to respondents by loading an unused column with random numbers then using those numbers (column) in the email response. This would be simple if Google forms didn’t insert new lines for each response instead of pasting them. To compensate for that I move that column to a separate sheet, then linked the sheets using a importrange formula. Then I tried to run these two scripts together and that’s when I started getting that error everyone is talking about.
After thinking about it, I either need (1)to find a script that will let me place data from two sheets into the automated email
OR (2)I have to find a way to recalculate the imported range before sending the email.
I don’t have much experience in script, but this is my attempt at option 2 far after lots of research. They work separately but not together.
function onEdit() {
// get active spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
}
function myFunction(e){
var userName = e.values[3];
var userEmail = e.values[4];
var code = e.values[5];
var subject = “Revision Submission Confirmation Code”;
var message = “Thanks ” + userName + ” for submitting your proof of revision. Your confirmation number is ” +code;
MailApp.sendEmail (userEmail, subject, message);
}
Can you tell me what I am doing wrong? When I put them together only the first one (recalculator) works.
Thanks
Ok, so this is a pretty complex issue you’re describing, one that is also beyond the scope of this tutorial. I’ll try my best to help.
First, the var code=e.values[5] is still taking the event data from the form submission, not a pre-existing column as you describe, so this is an issue.
Second, I’m not sure what the first function is doing. You define a variable that opens the active sheet, meaning the sheet to which this script is attached. If you want to pull data in from another sheet you should use the SpreadsheetApp.GetSheetbyID(), which will let you pull in data from another specific sheet, although you will still need to specify the range.
However, a simpler solution, if you are just sending them random codes and you don’t need to cross reference the random codes, would be to create a variable that just generated a random number between 1-100, which would look like this: var code= Math.floor((Math.random() * 100)+ 1); You can find more about that here: http://www.w3schools.com/jsref/jsref_random.asp
Thanks so much for the random number generator. This is what I wanted to begin with. However, when I add it to the script, the email won’t send, and I am still getting: TypeError: Cannot read property “values” from undefined. (line 2, file “Code”
Here is what I have done:
function myFunction(e) {
var firstName = e.values[3];
var lastName = e.values[2];
var userEmail = e.values[4];
var code = Math.floor((Math.random() * 9999) + 1000);
var subject = “Revision Submission Confirmation Code”;
var message = “Thanks ” + userName + ” for submitting your proof of revision. Your confirmation number is ” +code;
MailApp.sendEmail (userEmail, subject, message);
}
Once again
Thanks so much
I got it to work. I saw my mistake with the firstName lastName.
Thanks so much
It works perfectly now
Awesome! Very cool extension of the initial code. Thanks for the comments!
instead of random can there be a sequence like 1,2,3… and not picking a random #?
That is tough because any counter variable you create will be reset each time the script is run. If you use the get last row method, you should be able to add a unique number that corresponds to the row being edited. Take a look at the docs here: https://developers.google.com/apps-script/reference/spreadsheet/sheet#getLastRow()
Hi, I get the random code in my email, works perfect! Is it also possible to get this random code on my spreadsheet (same code as in email…)?
BTW, Great site, very helpfull!
Thanks
Hey Kristof,
I’m not sure what you are asking here. What do you mean by random code? You can just use the example linked here to set a cell or range to a particular value: https://developers.google.com/apps-script/reference/spreadsheet/range#setValue(Object)
Let me know if that link helps, or provide me with a more specific example of what you want to do so that I can help.
Cheers,
JE
Help. I can not get this script to work!
Here’s my script
function EmailConfirm(e){
var userFirstName = e.values[1];
var userlastName = e.values[2];
var userGrade = e.values[3];
var Email = e.values[11];
var dayMon = e.values[4];
var dayTues = e.values[5];
var dayWed = e.values[6];
var dayThurs = e.values[7];
var dayFri = e.values[8];
var subject = “GOA Lunch Order Confirmation”;
var message = “Your lunch order for, ” + userFirstName + userlastName + ” has been received. Your order details:” + dayMon + dayTues + dayWed + dayThurs + dayFri;
MailApp.sendEmail (Email, subject, message);
}
Email Address is in column L so [11]
Trigger is set for form submit. This is the failure notification I get.
Start Function Error Message Trigger End
9/17/14 11:48 AM EmailConfirm Invalid email: undefined (line 14, file “Code”) formSubmit 9/17/14 11:48 AM
I can’t figure out where I am going wrong.
I am sending from a Google Apps account, I don’t know if that makes a difference.
Thank you for your help!
So, I’ve had an issue like this recently. I think there is a small bug when using the (e) event parameter. Since you have email so high up in the list of variables, it may be pulling an undefined value from another cell of e.values[4-10]. Id try creating a new form with the email as e.values[4] and all of the form fields that correspond to variables you are using to populate the three MailApp arguments (Email, subject, message) set to required, with “no choice” or something like that being an option.
I hope that makes sense; let me know if you need additional info.
This is the code that I have used on two other forms (text slightly edited) but otherwise the same:
function myFunction(e) {
var username = e.values[1];
var userEmail = e.values[5];
var subject = “Strathcona Ladies Business Showcase on November 15th”;
var message = “Dear ” + username + “, thank you for registering to participate in the Strathcona Ladies Business Showcase taking place on November 15th, 2014. If you have any questions or concerns, please email Kathy at ***. ”
MailApp.sendEmail(userEmail, subject, message)
}
I then receive the following error email:
myFunction Invalid email: No (line 7, file “Code”) formSubmit
I can’t figure out what I have done wrong. Sorry I am not overly competent with this ‘script’ business.
Details:
Start Function Error Message Trigger End
9/23/14 5:31 PM myFunction Invalid email: undefined (line 8, file “Code”) formSubmit 9/23/14 5:31 PM
I get the above in an email I receive after submitting a form. I just cannot figure out what I’m wrong. In fact I’m getting the same message on all three spreadsheets that I”m using. The code is:
function myFunction(e) {
var userName = e.values[1];
var userEmail = e.values[5];
var subject = “Strathcona Ladies Business Showcase on November 15th”;
var message = “Dear ” + userName + “, thank you for registering to participate in the Strathcona Ladies Business Showcase taking place on November 15th, 2014. If you have any questions or concerns, please email Kathy at ***. “;
MailApp.sendEmail(userEmail, subject, message);
}
Hi Kathy,
I’m going to reply to both comments in this single thread. The error you are getting means that when your code is running and gets to the MailApp section, the email address is not valid, and therefore it cannot send the email.
This can be caused by a few things: 1. the email address could in fact be invalid; you need to include the @validdomain for this script to run successfully. There is actually a data validation to the Google form that will check for a valid email address, and I’d recommend turning this on.
2. Something could be funky with the script itself, so I’d recommend commenting out the MailApp line so that it doesn’t run and then write over again and test one more time. Not sure why this works, but I had to do it the other day even though nothing was wrong with my code. Google updated forms recently and lots of things have been buggy. Here is a link to info on creating comments: http://www.w3schools.com/js/js_comments.asp
3. This is the most complicated, so let’s hope this isn’t the issue. I can see that your variables skip from e.values[1] to e.values[5]. If any of the values in between are optional, the script could be pulling that ‘undefined’ data into the userEmail variable by mistake. To fix this, you’d have to completely remake the forms, but put userName and userEmail as the first two form fields.
So, I’d recommend troubleshooting in this order (1,2,3) and let me know if you have other questions or issues!
Regards,
JE
Hi, I have this error
TypeError: Cannot read property “values” from undefined. (line 4, file “Código”)
This is my code:
function emailOnFormSubmit(e) {
var nombre = e.values[1];
var mail = e.values[2];
var participante = e.values[7];
// The subject of the email
var asunto = “Seminário Filosofia na Escola – Confirmação da inscrição para ” + name;
// emailBody
var emailBody = “Este é um e-mail confirma que ” + nombre +
“nse inscreveu exitosamente no ‘IX Seminário Filosofia na Escola – V Seminário PIBID – Filosofia’, ” +
“nsendo o tipo de inscrição ” + participante +
“nnMuito obrigado e boa participação!”;
// html
var htmlBody = “Este é um e-mail confirma que ” + nombre + “” +
“se inscreveu exitosamente no ‘IX Seminário Filosofia na Escola – V Seminário PIBID – Filosofia’, ” +
“sendo o tipo de inscrição ” + participante +
“Muito obrigado e boa participação!” +
“Att.”
“Comissão Organizadora”;
// Advanced Options Parameters
var advancedOpts = { name: “Comissão Organizadora do Seminário Filosofia na Escola”, htmlBody: htmlBody };
MailApp.sendEmail(mail, asunto, emailBody, advancedOpts);
}
I see that others have the same error, but I didn´t understand how to fixe it. Can you help me?
Thanks.
Typically this happens when you try to run in the editor. You can’t run the script in the editor since it needs event (form submission) data to place in the variable you’ve created. Have you tried setting the trigger and submitting a test form with your email as a way to test the script?
I am having the same issue. I did submit a response in the form, but it is still not working. Any suggestions?
The same with me 🙁
I did submit a response in the form, but it is still not working.
Thanks for the help 🙂
I’m trying your code but I get this error TypeError: Cannot read property “value” from undefined. (line 4, file “Code”)Dismiss
I understand is because I’m debugging in the editor. However when I run the form, it adds another row to the spreadsheet but ignores my code. I have set the trigger. Any suggestions?
function onSubmitForm(e) {
var toAddress = “edgardoimar@gmail.com”;
var timestamp = e.value[0];
MailApp.sendEmail(toAddress, “test”, timestamp);
}
A couple of things; first, it should be e.values not e.value for your timestamp variable. Second, instead of using the string ‘test’ in the MailApp method, create another variable called subject and set that equal to ‘test’. Third, I’d set error reports to send immediately so that we can get some more info when you submit the form and it fails to send.
first, this is a wonderfully concise script, thanks.
I am attempting to modify the script to include an already present value in a field. When mapped to the field that includes the current value in the spreadsheet, after the form is submitted, the pre inserted value moves down to the next row.
Might you know how to include this value in your script? I’ve included the code below.
Thanks again!
Chris
function myFunction(e){
var email = e.values[1];
var condition = e.values[2];
var ac = e.values[3];
var wo = e.values[4];
var subject = “Form Submitted”;
var message = ” Home Condition,” + condition + ” AC Condition” + ac + “Work Order Number” +wo;
MailApp.sendEmail (email, subject, message);}
found the answer if anyone was interested – used the row count instead:D
function FindRows() {
range = SpreadsheetApp.getActiveSheet().getLastRow();
return range;
}
Awesome, Chris. Glad you found the answer, and thanks for sharing with everyone!
Good Afternoon… Here is my code I created from watching your tutorial. IT works great! I was wondering how to make a line break in the “message” part of my email?? the n and others wont work…
Thank YOu
function myFunction(e) {
var staffnameFirst = e.values[4];
var staffnameLast = e.values[18];
var staffuseremail = e.values[26];
var date = e.values[5];
var studentinfraction = e.values[8];
var behaviordescription = e.values[10];
var studentnameFirst = e.values[1];
var studentnameLast = e.values[17];
var subject = “Referral Form Submitted”;
var message = ” Thank you ” + staffnameFirst + staffnameLast + ” for submitting your refferal for ” + studentnameFirst + studentnameLast + ” on ” + date +
” for the infraction ” + studentinfraction + ” described as ” + behaviordescription + ” Thank You ” + ” Mr Reineck ”
MailApp.sendEmail(staffuseremail, subject, message);
}
The n character should work, as it is the default new line character in JS. Check out this article and make sure you are implementing them the same way: http://stackoverflow.com/questions/5758161/how-do-i-create-a-new-line-in-javascript
If it still doesn’t work, let me know and I’ll find another way.
JE
Hi Jeff,
This was an amazing tool and helped me out with a few of my docs so far. I’m running into an issue when I have multiple sheets in one response doc. I have one sheet/tab for one type of vendor, and multiple others for other vendors, and one script won’t work for them all. Is there a way to change the script to correspond with each individual sheet, or would I need to set up separate sheets to be able to do this? Any help would be appreciated!
Thank you!
Hey Sam,
That is good questions, and there are a lot of ways to grab data from other sheets. I’d check out the getActiveSheet and openByID methods at the following link for some examples of how to get data from sheets other instead of event data.
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app
Regards,
JE
Thank you for the quick reply! I’ll give those a shot. I appreciate it!
Hi Jeff,
Sorry to bug you again. I followed the link, but don’t think I understand this exactly as I should. I have a Spreadsheet with 4 sheets (Beer, Cheese, General & Food). I need auto reply emails to be sent out for each sheet, but the data isn’t arranged the same way on each sheet, so the basic code to send out an email won’t work. Here is what I have from before, and in addition to what I was hoping would separate them, but I’m clearly missing something.. Could you give me just a little more direction on this, or tell me if I’m way off?
function myFunction(e) {
var userName = e.values[3];
var brewery = e.values[1];
var email = e.values[9];
var ss = SpreadsheetApp.getActiveSpreadsheet();
var Beer = ss.getSheetByName(“Beer”);
Beer.activate();
var subject = “Registration Received”;
var message = “Thank you, ” + userName + “for registering ” + brewery + “with Isthmus Beer and Cheese Fest 2015. We have received your registration and will contact you with more details as the event draws near. If you have any questions, please don’t hesitate to contact us. “;
MailApp.sendEmail(email, subject, message);
Thank yoU!!
Hello,
Thanks for this post, it helped me. I can send submission email. However I have one question is there any way to debug this email confirmation code, since we can only check with form submission. I can send confirmation email at time of form submission but I tried to use this code in my function of Reminder Email, but it is not working fine.
Here is my code, may be you can guide me: function firstAppointmentDeadLine(e){
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;
var lastRow = SpreadsheetApp.getActiveSheet().getLastRow();
var dataRange = sheet.getRange(startRow,2,lastRow,14);
var values = dataRange.getValues();
var today = new Date();
var lastCol = SpreadsheetApp.getActiveSheet().getLastColumn();
for(i=0;i<values.length;++i){
//[14] Appointment Date
var firstAppoint = values[i][12] ; // 12 is the index of the column having appointment date converted from string format (column5) to date format.
var timeDiff = Math.abs(firstAppoint.getTime() – today.getTime()); //calculating time diff
var diffDays = Math.ceil(timeDiff / (1000 * 3600 * 24));
// if two days are left in appointment then send automatic reminder email
if (diffDays == 2) {
var userFirstName = e.values[1];
var userEmail = e.values[4];
var FirstAppoint_date = e.values[5];
var subject = "Reminder for First Appointment";
var message = "Dear " + userFirstName +","+ "nnWe like to remind you about your First Appointment on " +FirstAppoint_date+ "nPlease be on time.";
MailApp.sendEmail (userEmail, subject, message);
} //if ends
} //for loop ends
} // function ends
Interesting project. What is the script doing now when you run it, error message, sending something you didn’t expect, or just nothing?
It looks like, in the for loop, you are comparing the date of the appointment to today’s date and if there are fewer than two days left to the appointment, the script will send an email. Since this function isn’t really tied to the event submission, you might try removing the (e) parameter and on form submit trigger and changing to a time driven trigger, maybe running this every day at the same time. I would also have the date/time evaluator cycle through each row of the spread sheet and pull out the data using something other than e.values, since that pulls from the submission event.
Thanks for your reply. Right now it is giving error message “TypeError: Cannot read property “values” from undefined” at line where it is reading User Name from e.values (var userFirstName = e.values[1];).
I have set trigger for this function Time Based and getting this error. Can this be possible e.values be used as Time trigger as well or it can just be used on form submission ?
Are you aware of any other method to pull data other than e.values on time trigger?
Thanks in advance
Yes, the e.values and time triggers don’t mix well since the event happens on form submission and the time trigger could run later.
There are a couple of other methods that you could use, and here is a link to the Google Spreadsheet Documentation:https://developers.google.com/apps-script/reference/spreadsheet/range#getValue()
getValue() or getValues() would likely work for what you’re trying to do, something like this example:
var values = SpreadsheetApp.getActiveSheet().getRange(2, 3, 6, 4).getValues();
There are also tons of other get methods you could string together to make sure you are getting the cells and values you need. Check out the Google Developers site for some examples and a list of methods.
Cheers! Happy coding.
Thanks a lot, it worked for me 🙂
I need to send emails out AFTER they submitted the form ;( We didn’t get this far when first trying this great google form generator.
Can I send emails out from “edit” from the spreadsheet using this code? I so hope so. Thank you in advance for your quick reply!
What do you mean by “from ‘edit’ from the spreadsheet”
First of all thank you so much for this tutorial. I have one request, in similar lines i.e., through the script (script using if conditional cases) can we prevent multiple form submissions by same user(using same name, email id , and/or etc….)
once again Thank You so much.
Ok, so I’m a bit confused by this question. Are you asking if we can prevent the script from sending more than on email reply to users based on user name etc., or if we can prevent them from submitting more than one form?
If you’re question is the second, I’m not sure that we can, since the form would need to process the data before you could fire any function contained in a conditional statement. However, you might be able to use a conditional statement to check to see if a user name, etc. already exists in the form, and if it does, delete that row.
Here is a link to the Google Developers site on how to delete rows: https://developers.google.com/apps-script/reference/spreadsheet/sheet#deleteRow(Integer)
Another thing to check would be whether or not there is a Google Forms setting that would only allow one submission, but that might not exist.
Thank you so much for responding. My Question was, is there a way to stop multiple form submissions by the same user.
—- Thanks in advance.
May be this might help you. http://www.labnol.org/internet/prevent-multiple-form-submissions/28675/
Yes! This would work, but there are some limitations to this option, as the author notes. Great resource! Thanks, Syed.
Thank you so much. I think this feature Google has added in recent past. Anyways thank you so much.
So, my inkling is that url will be unique for every form submission; otherwise, how would Google know which submission needs to be edited. The problem is that I’m not sure the link will even be generated until the form is actually submitted.
I’d dissect the url to see if there is some predictable pattern it follows, and then you could just include that in the message of the body and concatenate it with whatever other info, like spreadsheet id or submission number, that you would need to make it direct the user to the correct place.
Google recently added GetEditResponseURL() which is documented at https://developers.google.com/apps-script/reference/forms/form-response. I’m struggling how to integrate this function into the send email sequence though.
Okay, I checked out the documentation and some questions on StackOverflow and found one that might help: http://stackoverflow.com/questions/20136528/retrieving-href-link-from-google-forms
I’d check out the link to “Understanding Events” in the best answer. Since your script now is in the Sheet not the Form, you could add the e.response.getEditResponseURL() to the form and also have that create a new column in the Sheet that would contain the URL, which could then just be pulled into your message as usual.
Awesome question though. Please let me know if you find a less complicated solution!
Cheers, JE
Is it possible to change/determine from which email the response will come from? Or do I need to create everything in the account that I want the information?
having trouble formatting message body. How do I separate lines? Everything is running together.
Here is a link to the JavaScript new line character: http://stackoverflow.com/questions/1155678/javascript-string-newline-character
That should help your message formatting. As for your other question, I think it has to come from the Gmail associated with the account.
Hi great video, for some reason Im not receiving a confirmation email after implementing the script. Ive actually successfully done it before but for some reason its not working this time. Below is the code I used, maybe you can catch a mistake Ive missed.
Ive made sure to use the trigger Thanks
function myFunction(e){
var timestamp = e.values[0];
var firstname = e.values[1];
var MDlocal = e.values[6];
var userEmail = e.values[89];
var demodate = e.values[83];
var demostart = e.values[84];
var demoend = e.values[85];
var subject = “Demo Report Confirmation”;
var totalhours = e.values[86];
var message = “Hello ” + firstname + “, this email confirms that on ” + timestamp + ” you submitted a demo report for ” + MDlocal + “, which occurred between ” + demostart + ” and ” + demoend + ” for a total of ” + totalhours + “. REMEMBER: It is your responsibility to retain all confirmation emails in the event they are required as proof of time submitted.”;
MailApp.sendEmail(userEmail, subject, message);
}
Found the problem, miss counted the columns in the spreadsheet which threw off the the variables in script. Forgot to count the first column as zero like your instructions said.
Thanks for the instructional video, this was great!
Hi there,
It is a very useful and clear tutorial. However, I did follow ever step and this error code keep appearing when I try to run it.
“TypeError: Cannot read property “values” from undefined. (line 2, file “Code”)”
My code is
function formSubmitReply(e) {
var Useremail = e.values[3];
MailApp.sendEmail(userEmail,
“Confirmation”,
“Thanks for registering DSD ” +
“working on it as soon as possible. nnHelp Desk”,
{name:”marketing”});
}
Can you advice me on that?
Thanks
So, a couple of points of feedback:
First, I’d create a separate message variable so that the sendEmail method is cleaner.
Second, your userEmail variable is spelled differently on line 2 and on line 3, which will cause your code to throw an error.
Third, you cannot run this code from the editor since there is not form data to pull values from. I’ve talked about this elsewhere in the comments, so you can read through them to find a more in-depth explanation. Long story short, to really test you need to submit a test form.
Regards, JE
Hi JE,
I’ve deleted the old ones and start a new script which follows step by step, but the error code still occur. I found out that the script cant read any variable that I insert in row 2. I wonder why.
This is my new code:
function myFunction(e) {
var userName = e.values[1];
var gender = e.values[2];
var userEmail = e.value[3];
var subject = ” Form submitted”
var message = ” Thank you for registering DSD 2014″
MailApp.sendEmail(userEmail, subject, message)
}
Ok, this looks much cleaner. You are missing a semi-colon at the end of the MailApp line and you need to make value>values for userEmail, but other than that everything looks good. Have you tried submitting a test form? You will always get that error when running in script editor. Please let me know how you are testing this.
Thanks,
JE
I have a google form that I am using for people to RSVP for an event. I want to send an automatic confirmation email after they submit their form but I want the message to be different depending on whether they RSVP Yes or No. Below is my code. It appears no matter what value I enter in the RSVP field of my form (Yes or No), I always get the first Message in my if-then statement returned.
function myFunction(e){
var LastName = e.values[1];
var FirstName = e.values[2];
var userEmail = e.values[3];
var group = e.values[4];
var rsvp = e.values[5];
var guests = e.values[6];
var amtdue = guests * 25;
var subject = “RSVP Received – “;
var YesMessage = “You are receiving this email as confirmation that we received your RSVP for the following:”+ “nn” + group + ” + ” + guests + ” Guest(s)”+ “nn” +
“Your amount due is $” + amtdue;
var NoMessage = “You are receiving this email as confirmation that we received your ” + “RSVP for the following:”+ “nn” + ” – “+ FirstName + ” ” + LastName + ” will NOT be attending the banquet.”;
if (rsvp = “Yes”) {
MailApp.sendEmail (userEmail, subject, YesMessage);
}
else {
MailApp.sendEmail (userEmail, subject, NoMessage);
}
}
Ok, in your if statement, you are only using one equal sign, which is used for variable assignment.Basically, you are setting the value of rsvp to “Yes,” regardless of what is actually in the form submission values.
You need to use the triple equals in this case (===) so that the if statement will check to see if the rsvp variable is the same type (string) and equal to (‘Yes’), which it should be.
Try that and let me know if you still have trouble.
Cheers, JE
Thank you so much! That fixed it!
I keep getting the error message “Missing ; before statement. (line 4, file “Code”)”.
Here is my code…
function myFunction(e) {
var UserName = e.values[1];
var Email = e.values[2];
var subject “Rocket Template! Home Science Ideas”;
var message “Hi ” + UserName + “! CLICK HERE for your Rocket Template and start making them right away!”;
MailApp.sendEmail(Email, subject, message);
}
Also, if I want to send out in that auto reply a PDF how can I do that? Thank you so much for the great video!
I got it:) I did not add the “=” to the subject and message lines. It needed to be “var subject =” and “var message =”.
Now I just need to know how to send out that auto reply with a PDF file attached to it? Thank you so much!
Hey Matt,
Glad you caught that syntax error!
You can also use the Google Drive API to attach or create documents. Do you want to send just a static document from the Drive, or do you want to populate the PDF with submissions from the form, like we’ve done in this code example?
I’ve been thinking about making a video on your example for some time, so let me know what you’re trying to do and I’ll put one together, or I can suggest some resources to help get you started.
Thanks for posting. Cheers.
Jeff
I am wanting actually wanting to send out a confirmation email letting the user know I have received there info and add a link to a downloadable PDF on another page. I want to give out step by step instructions to making things like that I do on my Youtube channel. Things like raised garden beds, chicken coops, etc etc. I have actually got things working now but I am now running into another issue… Google has a limit as to how many emails can be sent in a day. I think the limit is like 100 auto emails per day and I have over 10,000 subs that I will be giving these PDF’s to so I think I will hit that limit every day! My channel is http://www.youtube.com/HomeFarmIdeas
Here is my form. Please try it out and let me know how it is working and is there a way I can host something on my website that I can run a script that can do all the things I need it to do so I do not hit those limits? https://docs.google.com/forms/d/1RqzIsI8d1dzVqo77kjymTOFqWJb9HgElAI1mF23eiZw/viewform
Ok, well we could certainly program the form to do what you want, but I wouldn’t recommend using Google Apps to run a sophisticated email campaign. The only limits I was able to find are here, but you’d still bump up against these: https://support.google.com/a/answer/166852?hl=en
At some point, it may be worth upgrading your tools, to either WordPress which would allow you do some baked-in relationship/contact management functions and allow people to receive email updates from your blog, or you could consider something like MailChimp: http://mailchimp.com/features/
To hand code a solution would require an HTML form, a few PHP scripts, and a dedicated mail server somewhere to send all 10,000 emails, which is more than most people want to manage.
Let me know if I can help out going forward. I do my own gardening, so I’ll be sure to check out your channel.
Regards,
JE
I cannot really afford mailchimp right now so I’m going to need to do the HTML form with some some PHP. I need lots of help with this:( Can you point me in the right direction?
I tried using formmule but that limit that Google has killed it.
Anything out there I can buy once or maybe you can do it for me? Let me know Jeff and thanks for the help man!
God Bless
I made a short tutorial video on how to make Google Apps send a PDF from Google Drive: https://www.youtube.com/watch?v=FzBQZAhjHeI
It’s pretty straightforward and should get you going. Let me know if I can help in any way. One thing I just thought of when reviewing the Gmail sending limits is that they limit the number of emails, but a single email could have up to 99 recipients in the BCC. Something to think about, although it would be a bit different to program.
JE
Thanks for a great tutorial Jeff, with a bit of experimenting (and reading through the comments) I can now do exactly what I wanted to achieve.
This was super helpful, but I still seem to be having issues with my code. I keep getting this: TypeError: Cannot read property “values” from undefined. (line 3, file “Code”)
I am using Google Forms to make an application, and I want to only send an email to people who respond ‘No’ to one question. Here’s what I have:
function SendEmail(e) {
var DV = e.values[3];
var Email = e.values[6]
var subject = “Application”;
var message = “<If you wish to edit your response, please click on this link.”;
if (DV === “No”) {GmailApp.sendEmail(Email, subject, message)}
}
The script is part of the actual form, not spreadsheet, and the trigger is on form submit.
Any advice?
First, if you read previous comments, you find an explanation for the TypeError. Second, is there a reason you are using GmailApp instead of MailApp? If you are looking for something more robust, then GmailApp is it, but if you just want to send email, I’d go with MailApp, fewer moving parts. Third, you are missing a semicolon after the sendEmail method on line 8.
Lastly, there is a pretty big difference in the event object (e) between Google Forms and Google Sheets. The example that I give in the video needs to be bound to the sheet, not the form. The Google Form event option produces e.response instead of e.values, and you would have to access those values differently than done here and in the video. I would just move this script to the corresponding sheet, otherwise you’ll have to rewrite the code. You can read more about the various event objects here: https://developers.google.com/apps-script/guides/triggers/events
Let me know if you want me to look at a revision if you continue to have bugs/errors.
Cheers,
JE
I have it figured out to be the link in the body of the email. It’s the response.GetEditResponseUrl() that I can’t get to work.
Somewhere in the comments, we’ve touched on that before, but I can’t remember if that person posted the solution. Can you post code and the type of error notification you are getting?
Another thing to think about is that the link you want will need to be rendered using HTML, not just a string. Sending an HTML body requires additional options. Here is an ex: https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object)
HTML body is listed in advanced parameters.
Here’s my code:
function myFunction(e) {
var DV = e.values[4];
var Email = e.values[5];
var subject = “Application”;
var body = “Thanks. To edit or finish click this link.”;
if (DV = “No”) MailApp.sendEmail(Email, subject, body)
}
I left the closing bracket out on purpose so that it would not translate like it did in my first message.
The script runs fine without the link. Put the link in, and I get nothing. Not even an error code.
Here’s my code:
function myFunction(e) {
var DV = e.values[4];
var Email = e.values[5];
var subject = “Application”;
var body = “Thanks. To edit or finish click href=”” + response.getEditResponseUrl() + “”>this link.”;
if (DV = “No”) MailApp.sendEmail(Email, subject, body)
}
I left the closing bracket out on purpose so that it would not translate like it did in my first message.
The script runs fine without the link. Put the link in, and I get nothing. Not even an error code.
I’m confused by what you mean by “I left the closing bracket out on purpose so that it would not translate like it did in my first message.” I assume you are talking about the if statement and the missing curly brace, but there are lots of other things going on here that need fixing.
In this example, it looks like you have to call getEditResponseURL() on the Form object, not response: https://developers.google.com/apps-script/reference/forms/form-response#getEditResponseUrl()
You would call response on the event object like so e.response, similar to the e.values.
I’m also not sure it’s running your if statement because of that missing curly brace, and you should use the triple equals operator (===) to compare DV with the string “No”. Also, if you want to include HTML, you’re going to have to format the body a bit differently.
var body = “Thanks. To edit or finish click
this link";Unless you use the html body option I mentioned earlier, this might also just appear as a string instead of an HTML anchor element. You might also have to change the formatting depending on the return value of getEditResponseURL().
At this point, it might be worth just working on getting the edit response URL into a variable and then figure out how to work it into the rest of the code later.
Hi, thanks so much for all of this hard work. Has anyone figured this piece out yet?? I think it would be so helpful to many folks… how do we get the response edit url as a variable? I’m very new to all of this scripting so don’t think I have a good chance of figuring it out myself, but if someone else could explain how to define that var I would so appreciate it!
ok, here was my best go… doesn’t work, but maybe it’s a start?
function emailConfirm(e){
var form = FormApp.openByUrl(‘https://docs.google.com/forms/d/1Jn1wCbyCZwluummifjiC6cSzD3ip0NSKTV0v5XDCrRI/edit’);
var formResponses = form.getResponses();
var lastResponse = formResponses[formResponses.length – 1];
var formEditUrl = lastResponse.getEditResponseUrl();
var userName = e.values[1];
var userEmail = e.values[2];
var changeDate = e.values[5];
var studentName = e.values[3];
var newPlan = e.values[6];
var subject = “Change Submitted for ” + changeDate;
var message = userName + “, Your change of plans has been recorded. On ” + changeDate + “, ” + studentName +” will be notified and sent to: ” + newPlan + “. If this is a mistake or you need to make another change to the plan for this date, DO NO SUBMIT ANOTHER FORM. Use the link below or contact the Lower School Office. ” + formEditUrl;
MailApp.sendEmail (userEmail, subject, message);
}
Ok, this looks ok. What happens when you submit the form?
So, I wrote something that works for your situation. Feel free to cut it up and use the parts you want, but if you put this in a blank form by itself it will get the edit response url for the last submission:
function myFunction() {
var form = FormApp.openById("1m1Ts1SD6MTblFQA4p8qPLauyzfFI07laNUkupshi9o4");
var responses = form.getResponses();
//loop to get last response
for (var i = 0; i < responses.length; i ++){ var formResponse = responses[i]; var editURL = formResponse.getEditResponseUrl(); } Logger.log(editURL); MailApp.sendEmail("jeffeverhart383@gmail.com", "success", editURL); }
Thank so much! That loop to get the last submission is the key I think. Really, thanks for taking the time to help out!
Yea, for some reason when you called formResponses.length it returned a float (7.0) instead of a whole number integer (7), which is why your solution didn’t work. Not really your fault as the Goggle documentation says length will return an int, which we need to access the index of the response. You took a really good stab at it, so thanks for helping out with that. I’ll make another video in the next few weeks to help out the community.
Appreciate this post. Will try it out.
Hi jeff,
I am trying to get the scripts working, but somehow it just doesn’t do anything.
I have used the following code:
function myFunction(e){
var userName = e.values[1];
var userEmail = e.values[8];
var subject = “PDSA Summer School Registration”;
var message = “Thank you, we have received your 2015 PDSA Summer School registration for ” + userName;
MailApp.sendEmail (userEmail, subject, message);}
Can you see if anything is wrong. I did put in the trigger, but it just doesn’t do anything.
I don’t see anything obviously wrong here. Make sure your triggers are set up correctly and also send error notifications immediately, as they might provide a clue as to what’s going wrong.
Make sure this is the spreadsheet script editor and not the form script editor.
Also, try running the debugger in the scripts editor to see if it provides more info. You will always get a cannot get values of undefined error on line 2, but there may be some additional info there as well.
Regards,
JE
Is there a way to change the account from which the email is sent so that when the user gets the email confirmation it isn’t coming from an individual’s personal email address, but from a google group email?
I just don’t want to open the users to direct communication with an individual’s personal account but to the entire group who will be handling the response.
I don’t think there is a way to change who sends the email, at least without changing the account that installs the script and authorizes it in the spreadsheet. However, there is a reply to setting that you could use to set the reply address to your group account. Very easy, just an extra parameter in the SendEmail method: https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,String)
Hope that helps!
Let me know if you have additional ?’s
JE
Hi Bill,
There is a collaboration function you could use to start this over.
Click the Send Form button on the upper right corner. In the dialog box, at the bottom there is a collaboration link, enter the group email. Have that group email access the form. Then follow the steps below:
1. Copy the Script you have done to a Notepad or anywhere just for a copy.
2. Delete that Script file
3. Unlink the Spreadsheet
4. Use the group email account to set up the Script, instead of retype the Script, now you can paste it in from the Notepad.
For the steps after it just follow the prompt.
There might be another way to do it, but this is how I did it to my project.
Hi, Jeff,
Thanks for your succinct and useful tutorial and video. I tried getting it to work on an already existing form/spreadsheet combination with no luck, so I created a new form/spreadsheet for testing.
Here is the code. It is attached to the spreadsheet and the trigger has been set correctly (as far as I can tell…since it’s not being triggered). I have notifications set to immediate.
function myFunction(e) {
var userName = e.values[1];
var userEmail = e.values[2];
var subject = “Form Submitted”;
var message = “Thank you, ” + userName;
MailApp.sendEmail (userEmail, subject, message);
}
When I enter something into the form, the information is entered into the spreadsheet, but I get neither a confirmation mail to the email address entered nor an emailed error message.
Thanks for your help.
Hi Kris,
Thanks for commenting. Everything in the script looks good, so I’m not sure where the error is by looking at what you have here. When you set the trigger and notifications, did it ask you to authorize the script to send mail as you? Usually, that will happen after you save the trigger.
If not, go ahead and do it. If so, I might even delete the old trigger and set a new one.
Other things to think about: did you put in email validation? If it is trying to send to misspelled address, it will bounce around for awhile before sending an error message. Check SPAM folders as well. If you are trying to send to a corporate account or work address, these things somethings get caught in SPAM filters. Also, sometimes it can just take a painfully long time to arrive.
Keep me posted on your progress.
JE
Hi,
I am using the =Query for my form to the second sheet.
Since [4] is Responses sheet email data column. Which is sheet 1
var userEmail = e.values[4]
Then, what number should I use to read the email column of the sheet 2?
Thanks,
Jims
You will have to create a new variable to access the second sheet. It is a little different than what I’ve done in the tutorial, but you can use the setActiveSheet class to accomplish this: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#setActiveSheet(Sheet)
He guys,
I created a form for my final school assignment and need some help. I`m a total rookie at writing scripts. I added formulas in sheets for every question and it automatically adds creates a final score. I created 3 different score ranges so there are 3 different results. All 3 results have a different advice email/message. The advice mails are automatically added in the same sheet. My question. Is it possible that the respondent automatically gets a reply message with the advice message that is added in sheets? How do I write the script so it adds the advice message I added in sheets? I`ve been breaking my jaw on this one, really cant get it to work. Tutorials don`t cover this part. Hope someone can help me. Would be great! Greetings Jay
Hi Jeff,
Thnx for your fast reply. I saw the script for adding a attachment earlier. My form gives 3 different variables when it comes to response messages. These are automatically calculated in google sheets. I need to add this specific cell in the auto reply mail.
Hey Jay,
What do you mean by the messages being calculated? We can do any kind of calculation in Google Apps Script, so we shouldn’t need to pull it from the sheet. Can you post your code or link to the form so that I can get a good idea of what you’re trying to do?
Thanks,
JE
Hi Jeff,
What i mean hmmz lets give it a try starting from the beginning. I made a form to check how good or bad people score on risk factors for backpain. Each riskfactor has its own value. All questions related to risk factors are rated and after a form is submitted a total score is being calculated in sheets. There are 3 different ranges people wil get advice for. Range red, orange and green (like a traffic light). Each outcome has a different advice message which is automatically added in sheets. This advice message is a total email message, so in fact I only have to add the cell that gives the advice message in a email message to the emailadress of the respondent.
This is the code i tried.
// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = “EMAIL_SENT”;
function sendEmails2() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 3; // First row of data to process
var numRows = 1000; // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, 3)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var emailAddress = row[0]; // First column
var message = row[1]; // Second column
var emailSent = row[2]; // Third column
Logger.log(emailAddress);
if ( emailSent ){
if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
var subject = "Advies reponse preventieve rugklachten";
/*MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();*/
}
}
//MailApp.sendEmail(emailAddress, 'blaat', message);
}
}
edit: I added the wrong script. The one above i tried some stuff with. Below is the one close to working proper. Is there a way i can add screenshots so you can see how I have my sheet set?
// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = “EMAIL_SENT”;
function sendEmails2() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 3; // First row of data to process
var numRows = 1000; // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, 3)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var emailAddress = row[0]; // First column
var message = row[1]; // Second column
var emailSent = row[2]; // Third column
Logger.log(emailAddress);
if ( emailSent ){
if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
var subject = "Advies response preventieve rugklachten";
MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
}
}
//MailApp.sendEmail(emailAddress, subject, message);
}
}
Hi, Thanks for the script. It worked great. Then I tried to add CC to myself but failed. I undid the change but I got the error Cannot read property “values” from undefined.
I scrapped the whole thing and recreate the form from scratch exactly as you showed again but this time it errors out the same message.
Thanks,
Dienson
Usually, you get that error when you run from the script editor. Try submitting a test form and reply back with what happens. Please post your code so I can look at it.
Regards,
JE
Thanks for the great tutorials.
I have successfully used the script you provided.
However, it only seems to work with a Google docs form. I have been trying it with an external form (on Squarespace). Squarespace allows you to have a form and then link it with Google docs to store the names, email addresses, etc.
However, using the script you provided, it doesn’t generate an automatic email reply.
Do you know if Google docs is set up to be able to work with external forms?
Thanks
Simon
Hey Simon,
I’ve never tried to use a Squarespace form before, so I’m not sure how that would work out…
Hi,
I wish to send a confirmation email with customized dates depending on the number response. For instance, the first 70 entries will have a confirmation email with the date “21st July” and the next 70 will be on “22nd July”.
Is there a way to script this in?
Please let me know, thanks!
This is going to take some tinkering, but I think you can do it. First you need to select the form and get its responses:
var form = getFormById("formidhere");
var formResponses = form.getResponses(); //This should return an array of responses
//next call the length property on formResponses to see if it is over 70 and then set your data variable to be the date you want:
//use a conditional to check and reassign the date value
if (formResponses.length > 70) {
date === "21st July";
} else if (formResponses.length > 90) {
date === "22nd July";
} else {
date === "some third date";
}
You can find an example of this and other documentation here: https://developers.google.com/apps-script/reference/forms/form-response
Hi! Thanks for your prompt reply. However, my script still doesn’t work, please help…
function myFunction(e){
var userName = e.values[1];
var userEmail = e.values[2];
var form = getFormById(“Form Responses 1”);
var formResponses = form.getResponses();
if (formResponses.length < 0) {
date = "3rd August";
} else if (formResponses.length < 70) {
date = "4th August";
} else if (formResponses.length < 140) {
date = "5th August";
} else if (formResponses.length < 210) {
date = "6th August";
}
var subject = "Thank you for your Inquiry!";
var message = "Thank you, " + userName + " for submitting your inquiry. Your appointment is set on " + formResponses + ". Please bring along this confirmation email, along with any other supporting documents, to our office on your appointment date. We look forward to speaking with you soon.";
MailApp.sendEmail (userEmail, subject, message);}
Sorry, I left a piece of code out and you put in the wrong id. It should be:
var form = FormApp.openById(" ");
The id of the form is a long alphanumeric string that appears in the URL of the form. Try this and let me know how it works. If it fails, please include the error information you get in the error email as it will give us more to go on.
Also, all of you inequalities are less than, so right now, unless there are more than 210 responses, all of the conditionals will evaluate to true. IT should be if (formResponses > (are greater than) 70) do something here.
function myFunction(e){
var userName = e.values[1];
var userEmail = e.values[2];
var form = FormApp.openById(“1I-IyBiW8sbYjzgK4gVu2snI3jcD5QbZWuxtIM9_dyRs”);
var formResponses = form.getResponses();
if (formResponses.length > 0) {
date = “3rd August”;
} else if (formResponses.length > 70) {
date = “4th August”;
} else if (formResponses.length > 140) {
date = “5th August”;
} else if (formResponses.length > 210) {
date = “6th August”;
}
var subject = “Thank you for your Inquiry!”;
var message = “Thank you, ” + userName + ” for submitting your inquiry. Your appointment is set on ” + formResponses + “. Please bring along this confirmation email, along with any other supporting documents, to our office at 369 Tanjong Katong Rd between 9AM-7PM on your appointment date. We look forward to speaking with you soon.”;
MailApp.sendEmail (userEmail, subject, message);}
Error message: TypeError: Cannot read property “values” from undefined. (line 2, file “”)
Please let me know what I can change before Monday as that’s when I need the form to go out by.
Thank you for everything!!
You always get that error message when running it in the script editor since there are no values in the submission event. Try submitting a few test forms and see what happens.
The only thing you will need to change is the date = “4th August” parts. They need to use the triple equals operator: === to check if they are equivalent. Right now that is assigning values like with variables.
date === “4th August”;
Hi Jeffrey – leading zeros in my responses are getting left out. Do you know if there is a way to force the e.value to include them?
Hey Matt,
What type of input are you using on the form? Also, can you post your code here so I can take a look?
Thanks
JE
Hello Jeff, Firstly thanks for your time!
I am in need to send a notification to submitters with URL Link so that they can resubmit their responses from their email. I see lots of discussions in this thread, could you paste me the Script to be copied into my Form – Script Editor?
Thank you,
Best Regards,
Sudhakar
Hello Sudhakar,
I don’t have that script saved anywhere, but that is something that I’ve addressed elsewhere in the comments. If you dig through them, you should find a few threads with the snippets you need.
Thanks for watching!
Hi,
I am trying to create a test on google form and want to send the test score to email as an automated response on form submission. Could you help me on how to do the same.
Hello,
You will need to check the answers using if login. Ex (if (e.values[1] === “True”) {score + 1};
Hi Jeff,
I have copied my script below, i will be grateful if you can assist if possible to check my script for approval its able to send mail to second approval when when state2 is approved. But its failing to change the state2 to either approved or denied so that it can trigger for a third request. i have linked all the forms to one sheet
Thanks
Joseph
var row = data[i];
row.rowNumber = i + 2;
if (!row.state) {
//Email the Supervisor to approve the request
sendReportToSupervisor(row);
if (row.state === STATE_APPROVED && row.state2 === STATE2_APPROVED) {
}
responsesSheet.getRange(row.rowNumber, COLUMN_STATE).setValue(STATE_SUPERVISOR_EMAIL);
} else if (row.state == STATE_SUPERVISOR_EMAIL) {
for (var j = 0; j < firstApprovalData.length; ++j) {
var approval = firstApprovalData[j];
if (row.rowNumber != approval.procurementRequestId) {
continue;
}
//send approval results to the requestor notifying supervisor's decision
sendApprovalResults(row, approval);
responsesSheet.getRange(row.rowNumber, COLUMN_STATE).setValue(row.state);
}
if (row.state == "APPROVED"){
sendReportToManager(row);
responsesSheet.getRange(row.rowNumber, COLUMN_STATE2).setValue(STATE2_MANAGER_EMAIL);
} else if (row.state2 == STATE2_MANAGER_EMAIL) {
for (var j = 0; j < secondApprovalData.length; ++j) {
var approval = secondApprovalData[j];
if (row.rowNumber != approval.procurementRequestId) {
continue;
}
if (row.state2 == "APPROVED"){
sendReportToDirector(row);
responsesSheet.getRange(row.rowNumber, COLUMN_STATE3).setValue(STATE3_DIRECTOR_EMAIL);
} else if (row.state3 == STATE3_DIRECTOR_EMAIL) {
for (var j = 0; j < thirdApprovalData.length; ++j) {
var approval = thirdApprovalData[j];
if (row.rowNumber != approval.procurementRequestId) {
continue;
}
responsesSheet.getRange(row.rowNumber, COLUMN_STATE).setValue(row.state);
responsesSheet.getRange(row.rowNumber, COLUMN_STATE2).setValue(row.state2);
responsesSheet.getRange(row.rowNumber, COLUMN_STATE3).setValue(row.state3);
// update the state of the report to APPROVED or DENIED
break;
}
}
}
I’m not sure you’ve given me enough here to fully understand the problem or what type of error you are getting, but there are a couple of things going on here that I’d check. First, you are evaluating a few things, and I’m not sure where they are coming from, such as STATE_APPROVED. Is this a variable declared elsewhere, or some type of data that you’re pulling from the sheet. Other places it’s clear you’re evaluating strings, but for some of these comparisons, I’m not sure what type of data you’re using.
Can you provide all of the code if you can, including any variables that appear before these code blocks.
Second, your loops may be incrementing the wrong way, as they are incrementing before they are evaluated, meaning ‘j’ is never evaluated as 0. You can check out a short description of the difference between “++j” and “j++” from the MDN here: https://msdn.microsoft.com/en-us/Library/26k41698(v=vs.94).aspx
Hi,
Thanks for responding to my request.
The error massage Invalid email: undefined (line 164, file “GS_Main_Code”) am getting this error msg from google notification ,but its able to send the the request.But its failing to change the state to either “denied or Approved” in the state2 row
The STATE_APPROVED is a variable that has been declared
var STATE_SUPERVISOR_EMAIL = “SUPERVISOR_EMAIL”;
var STATE_MANAGER_EMAIL = “MANAGER_EMAIL”;
var STATE_DIRECTOR_EMAIL = “DIRECTOR_EMAIL”;
var STATE_APPROVED = “APPROVED”;
var STATE_DENIED = “DENIED”;
var STATE2_DENIED = “DENIED”;
var STATE2_APPROVED = “APPROVED”;
var STATE3_DENIED = “DENIED”;
var STATE3_APPROVED = “APPROVED”;
var COLUMN_STATE = 5;
var ss = SpreadsheetApp.openByUrl(”);
var sheet = ss.getSheets()[0];
var lastRow = sheet.getLastRow();
var responsesSheet = ss.getSheetByName(‘Responses’);
var firstApprovalSheet = ss.getSheetByName(‘First Approval’);
var secondApprovalSheet = ss.getSheetByName(‘Second Approval’);
var thirdApprovalSheet = ss.getSheetByName(‘Third Approval’);
var data = getRowsData(responsesSheet);
var firstApprovalData = getRowsData(firstApprovalSheet);
var secondApprovalData = getRowsData(secondApprovalSheet);
var thirdApprovalData = getRowsData(thirdApprovalSheet);
for (var i = 0; i < data.length; ++i) {
var row = data[i];
row.rowNumber = i + 2;
if (!row.state) {
//Email the Supervisor to approve the request
sendReportToSupervisor(row);
if (row.state === STATE_APPROVED && row.state2 === STATE2_APPROVED) {
}
responsesSheet.getRange(row.rowNumber, COLUMN_STATE).setValue(STATE_SUPERVISOR_EMAIL);
} else if (row.state == STATE_SUPERVISOR_EMAIL) {
for (var j = 0; j < firstApprovalData.length; ++j) {
var approval = firstApprovalData[j];
if (row.rowNumber != approval.procurementRequestId) {
continue;
}
//send approval results to the requestor notifying supervisor's decision
sendApprovalResults(row, approval);
responsesSheet.getRange(row.rowNumber, COLUMN_STATE).setValue(row.state);
}
if (row.state == "APPROVED"){
sendReportToManager(row);
responsesSheet.getRange(row.rowNumber, COLUMN_STATE2).setValue(STATE2_MANAGER_EMAIL);
} else if (row.state2 == STATE2_MANAGER_EMAIL) {
for (var j = 0; j < secondApprovalData.length; ++j) {
var approval = secondApprovalData[j];
if (row.rowNumber != approval.procurementRequestId) {
continue;
}
if (row.state2 == "APPROVED"){
sendReportToDirector(row);
responsesSheet.getRange(row.rowNumber, COLUMN_STATE3).setValue(STATE3_DIRECTOR_EMAIL);
} else if (row.state3 == STATE3_DIRECTOR_EMAIL) {
for (var j = 0; j < thirdApprovalData.length; ++j) {
var approval = thirdApprovalData[j];
if (row.rowNumber != approval.procurementRequestId) {
continue;
}
responsesSheet.getRange(row.rowNumber, COLUMN_STATE).setValue(row.state);
responsesSheet.getRange(row.rowNumber, COLUMN_STATE2).setValue(row.state2);
responsesSheet.getRange(row.rowNumber, COLUMN_STATE3).setValue(row.state3);
// update the state of the report to APPROVED or DENIED
}
}
}
}
}
Thank you for the great tutorial and script – it worked perfectly! I was wondering if there’s a way to do a signature block in a different font at the end of the message? I usually have my name in script font at the bottom of my emails along with my address. Again, thanks for the script!!!
Yes, you could do that, but you’ll need to do it with an HTML message instead of plain text. Take a look at the advanced parameters and send an HTML body: https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object)
Great, thanks!!!
Hi,
I used the same script (with custom values of course), and it worked fine.
Then, I decided to received an email too when form is submitted (to be aware of new forms filled), so I created a new script which should be sent to me.
I added a second trigger related to this second script, but here what happened:
– I received a mail, the submitting email did not, like if only the second trigger worked. For unclear reason, the email value of the submitter was blank.
– I tried to understand what was wrong, didn’t found anything, so I just decided to rename scripts and functions (to have something organized), then update triggers. After the test, I received en email with ALL values blank, the submitter received nothing.
– Now… it doesn’t work at all. No email is received (on both addresses), even if I delete one of the scripts… :/
Is it a problem to link 2 scripts with the same trigger?
Can I use MailApp multiple times in a script?
Hmm, if you need to use two scripts, I would also set two triggers for each of the functions. However, the best solution is to just run the MailApp.sendEmail method twice in the same script. I do this quite often and it works great.
Two triggers were set. I wanted to scripts for the clarity.
But I’ve found the problem, it’s just MailApp requires vars only where I used direct values for sender.
two*
Anyways, thanks for answer and tutorial.
Cool, glad everything is working for you. Thanks for watching!
Hey:)
thank you for the great tutorial! It works just fine.
I have a question, how can I have this email sent to the people that already have submitted the form?
Thanks,
Alexandra
You would need to write a separate script that looped through the email addresses and ran the Mail.App for each one. When we do this, we usually add another column to indicate if an email has already been sent so that people don’t get email after email. It is much easier to just have the email sent on form submit.
I wanted to make some texts to be in bold when am sending email requests.This is the information filled in from the Google form.
Thanks
You can use this version of MailApp to send an HTML email instead of plain text: https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object)
I’m trying to do this for teacher observations but I keep getting the following error:
Missing ; before statement. (line 11, file “Code”)
I think it’s a syntax error but I can’t find it! Can you offer any help? Thank you!! My code is listed below:
function myFunction(e) {
var username = e.values[1];
var email = e.values[2];
var period = e.values[3];
var SLO = e.values[4];
var Engagement = e.values[5];
var Learning = e.values[6];
var Techniques = e.values[7];
var Blooms = e.values[8];
var Subject = “Rapid Check”;
var message = “Thank you ” + username + ” for letting me observe your classroom. Here is some feedback from this observation. Please feel free to contact me with questions or comments.” + “n” + “Observation occurred during ” + period + “n” + “Was the SLO present ” + SLO + “n” + “Students were ” + Engagement “engaged.” + “n” + “The learning was occurring ” + Learning + “n” + “Some instructional techniques observed were ” + “n” + Techniques “Levels of Blooms Taxonomy covered were ” + Blooms;
MailApp.sendEmail (email, Subject, message);}
Looks like you are missing some plus signs after the Engagement and Techniques variables in the message.
Thank you! Now I’m getting this error through an email from Google:
TypeError: Cannot read property “1” from undefined. (line 2, file “Code”)
Any thoughts?
Also your tutorial was so helpful. Thank you for posting this!
I’m still having the above issue. If you have any ideas on how to fix it I’d greatly appreciate them! Additionally, I would like two other people to be able to use my form, but when I made them collaborators they could not get the form to work. How can I give them the correct access to be able to use the form?
Thanks!
Check to make sure that the value you are referencing in the code is in the place it needs to be in the spreadsheet, should be 2nd column using zero indexing. Sometimes things can get shifted around when you move form inputs after creating a spreadsheet. Sometimes copying the form and spreadsheet helps. Then I post the code in the newly created script editor if it doesn’t copy over. Try that and if it doesn’t work, you can share the form and spreadsheet with my Gmail acct listed on the contact page of the blog. I’ll take a look when I get a moment.
Hi Jeff,
I’m new to writing scripts. I’ve spent the last hour reading your responses on this thread. It seems you’ve managed to crack every code thrown at you. I was wondering if you could help me in this one. In this example I’m trying to send a text filled out in a Google Form and added to a Spreadsheet to another group of people’s email addresses, using the script editor in the Spreadsheet. The text of the email is e.values[1] which comes from the field I have available in the Form. It is Paragraph format which allows people to write a lengthy text. The problem is that I have been unable to have it display correctly as an HTML. It all displays up as one chunky paragraph. I read on the thread that wrapHTML() can be used, but I don’t know how. Is it possible you could please help me? Thanks.
function formSubmitReply(e) {
var userEmail = “example@gmail.com”;
var letterdraft = ” + e.values[1] + ” + e.values[2] + ”;
var name = e.values[2];
GmailApp.sendEmail (userEmail, name, letterdraft, {htmlBody: letterdraft, name: name, replyTo: e.values[4], cc: “another@gmail.com, thirdemail@gmail.com“});
}
It seems like you are asking about why the message, the variable letterdraft, is not displaying as HTML. Right now, all you have is a string of characters without any markup. Try creating another variable to display the letterdraftHTML when devices enable it. Here is an example:
Then you can pass the letterdraftHTML variable into the options object you created:
GmailApp.sendEmail (userEmail, name, letterdraft, {htmlBody: letterdraftHTML, name: name, replyTo: e.values[4], cc: “another@gmail.com, thirdemail@gmail.com“});If that still looks wonky, and you want to retain any spacing the user entered, you could wrap the data from the paragraph input in a pre tag, and that might work. Let me know if that fixes it or if you still have questions afterwards.
I tried the code you suggested (at least I think I did), but same result. Am I doing it wrong?
function formSubmitReply(e) {
var userEmail = “example@gmail.com”;
var letterdraft = ” + e.values[1] + ” + e.values[2] + ”;
var letterdraftHTML = ” + e.values[1] + ” ” + e.values[2] + ”;
var name = e.values[2];
GmailApp.sendEmail (userEmail, name, letterdraft, {htmlBody: letterdraftHTML, name: name, replyTo: e.values[4], cc: “another@gmail.com, thirdemail@gmail.com“});
}
Also I’d be very grateful if you could please let me know what you mean when you say: you could wrap the data from the paragraph input in a pre tag.
Sorry, I’m a bit of a novice with the codings. I tried the first option you mentioned but it seems to me to be the same. I’m not sure if I did what you mentioned accurately. I have copied above the code I put in the script. Is it possible you could please see the above script in case I did it wrong? Many thanks for your help.
A pre tag is an HTML tag that formats pre-formatted text. You can read more about that here if you want to: http://www.w3schools.com/tags/tag_pre.asp
I also replied to your earlier message with a link to a fixed example. Best of luck. Let me know if I can help further. Thanks for watching!
There are still some errors in your code. Try copying and pasting the code that I’ve written here: https://gist.github.com/JEverhart383/cc1d8d2f03c5ebac5c70
Let me know if that works!
Has something changed with Google’s scripting? I tried your script and I get no emails. Triggers are turned on and set to form submit. Project is saved. Permissions granted. I have an ultra simple variant of your original post:
function SendEmailConfirmation(e){
var userEmail = e.values[8];
var subject = “Applicant Information”;
var message = “This is a receipt of your submission. Please click, follow and read the link for further instructions: url_goes_here “;
MailApp.sendEmail (userEmail, subject, message);}
The email addresses are in column I which should be “8” assuming that A = 0
There’s more columns up to P after I, that’s it.
Any ideas at all?
Did you install the script in the spreadsheet or the form? Needs to be in the spreadsheet. Also, can you post your code if it’s not working?
Hey Jeff,
I have tried your simple code (below) and it is not working. I am not using a google form, but I have a Squarespace site that sends the submissions to my Google spreadsheet.
When I try to run the code in the script editor it says “TypeError: Cannot read property “values” from undefined. (line 2, file “Code”)”. I know nothing about code, but have watched your video and looked at all these comments and still can’t figure it out. I also have submitted my info as a test and still receive no email. Help please!
function myFunction(e){
var userName = e.values[1];
var userEmail = e.values[2];
var date = e.values[3];
var subject = “Form Submitted”;
var message = “Thank you, ” + userName + ” for choosing ” +date;
MailApp.sendEmail (userEmail, subject, message);}
Hey Caleb,
I think it’s failing because of the Squarespace piece. The trigger I’m using here is “onformsubmit” not “onedit.” Since you’re not actually submitting a form, there is no form submission to trigger the event. In this case, you would need to change the trigger and find the latest updated row in the spreadsheet and use that to populate the call to the MailApp function in the last line.
Here are some links to the Google Docs: https://developers.google.com/apps-script/guides/triggers/#onedit
Please let me know if you’re looking for a
article writer for your site. You have some really great posts and I feel I would be a good asset.
If you ever want to take some of the load off, I’d really like to write some
articles for your blog in exchange for a link back to mine.
Please blast me an e-mail if interested. Thank you!
hie jeff,
i m using a script and i want to send a email to the customers whose issues have been sorted out by my imployes, everything is working fine but the thing is i need to change the name of the sender i.e who so ever fills up the form the emil should be send from his name. this is what i m trying to do
function support(e) {
var issue = e.values[5];
var Email = e.values[8];
var support = e.values[9];
var subject = “STRAWBERRY POS UPDATE”;
var message = “Greetings Sir/ Ma’am,n ”
+ “nThis is to bring in your kind attention that today we received a call from your outlet regarding”
+ “n” + ” -” + issue
+ “n” + “nWe are glad to inform you that the issue has been resolved by our Client success Team. ”
+ “n” + ” Customer satisfaction is our top priority and Strawberry POS believes in providing world class support round the clock. ”
+ “n” + “nKindly call on this number in case of any query. ”
+ “n” + ” Client Success Team 01166763916 ” + ” And PRESS 2.”
+ “n” + “nRegards,”
+ “n” + ” Strawberry POS Success Team.”;
MailApp.sendEmail(Email, subject, message, {replyTo: support});
MailApp.sendEmail(support, subject, message,{replyTo: Email} );
Some other people have had luck using Gmail aliases and using the getAliases method to send email as another address: https://developers.google.com/apps-script/reference/gmail/gmail-app#getaliases
Hi, so thankful for your help in this thread!
I managed to get everything going except for one thing..
Using your example, what if I have multiple columns of ‘Date’ e.g 4 to 10 in the spreadsheet, and only one out of the 7 columns is filled for each row of data that comes from each form submission?
On a separate spreadsheet tab I have ported all the input from columns 4-10 into a single column, but I understand that e.values captures data only from the original spreadsheet.
Here is an example script I suggested for someone with a similar problem, so please ignore the references to course and substitute date:
I would just write a function to check how many of the cells in 4:12 are undefined, meaning there is nothing in them, versus the one with the course name: you could use something like below:
function findCourse (e){ var courseToTake; //loop through values for ( var i = 4; i <=12; i ++){ //pull value into variable var value = e.values[i]; if (value != undefined){ //if we find an actual string value, set the course to take variable courseToTake = value; } } return courseToTake; }Hi,
How do I go about adding another attachment??
You can send multiple files in an array, like this:
GmailApp.sendEmail('mike@example.com', 'Attachment example', 'Please see the attached file.', { attachments: [firstFile, secondFile, thirdFile], name: 'Automatic Emailer Script' });Take a look at this link for more documentation: https://developers.google.com/apps-script/reference/gmail/gmail-app#sendemailrecipient-subject-body-options
Hi,
this is a so usefull script, thanks!
Is also possible to send a CCN notification to a specific email (the same email for all the notification) ?
And to create a PDF of the form to attach?
Thanks
Diego
Hey Diego,
Thanks! I’m not sure what a CCN is, but I’ve also got an example of how to attach a PDF: Google Forms Attachments
Hi,
thanks, CCN is the Bcc (Blind Carbon Copy), i wrote it in Italian, sorry!
Diego
Ciao è possibile inserire nel corpo del messaggio un collegamento ad un file su internet e nascondere l’indirizzo sotto la scritta DOWNLOAD?
un collegamento ipertestuale
Grazie
Sì , è necessario includere il messaggio come un corpo HTML . Fare riferimento a questa risorsa : https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object )
I followed your tutorial step-by-step, it was perfect for exactly what I am trying to do. I took out some variables that weren’t necessary, but other than that, I followed you completely. This is the message that pops up when I try to move on to the triggers: Missing ; before statement. (line 2, file “Code”)
This is my code:
function SendEmailConfirmation(e){
var userName = e.values{1};
var userEmail = e.values{3};
var subject = “Form submitted”;
var message = “Thank you,” + username + “for submitting your monthly inspection.”;
MailApp.sendEmail(userEmail, subject, message)}
Thank you in advanced!
It says you are missing a semicolon on line two, but that looks good. You could try one after the call to the maillapp method.
That is for sure doable, but you will need to create branching logic in your code using if/else statements to check that multiple choice box. Here is a quick example:
var attachment;
if (multipleChoice == thing1){
attachment = Drive.GetFileByID(thingToGetForThing1);
} else if (multipleChoice == thing2){
attachment = Drive.GetFileByID(thingToGetForThing1);
}
//Then send the email with attachment
I hope that helps! Write back if you have any other questions. And share the code you have if something isn’t working. I can give better feedback that way.
JE
Hi,
this is actually very helpful. I am very new to google form. I have created a leave application for my employees now I want to send them a reply of either approved or reject and also I want to merge data of employee and mine. For example a employee has sent a leave application through google from and have send a approved reply then approved should get merge with respective observation. I don’t know about any syntax or function that we use in script. Can you please help me in doing this.
Hi, I’m glad you found this helpful. I would think you would want to add some functionality to the spreadsheet that stores that data to approve/deny the particular request and then send the employee an email. The basics of that is covered here: https://developers.google.com/apps-script/guides/menus
Let me know if you have questions as you get started.
I am non-IT but got created one google form for matrimony profile. Here, when the matrimony profile submitted in the google form, how to get the proof by the sender, what the information sent in the form for future record. Like when we send any mail, we can check in the the sent mail what we have sent.
Hi Jeff,
short question: how to get the number of the answer in order to be able to issue a msg like that: “you are listed with #5”?
Hey Stan, I’m not sure what you mean by “number of the answer.” Do you mean the index of the question in the form, or the number of the answer in like a dropdown menu?
I am trying to trigger myFunction from the spreadsheet on form submit.
I get the message Authorization Required. I know I need to authorize access to my spreadsheets and my Google account, but I cannot figure out how to do this. Can you help?
Also, could you look at my code just to be certain that it looks correct. The column that the User’s Email is located in is L or column 12. Here is my code:
function myFunction(e){
var userEmail = e.values[12];
var subject = “FirstGuard Storm Information and Indepentdent Contract Forms”;
var message = “Please review storm information and complete the attached Independent Contractor forms. Fax completed forms to 334-323-5667 or Email to info@firstguardofficers.com“;
MailApp.sendEmail (userEmail, subject, message);}
How do you get this message “Authorization Required”? Usually, you can just click the run button in the script editor interface and there should be a pop-up window that allows you to authorize the script.
Everything in the code looks good to me. Best of luck. Let me know if you continue to have issues.
JE
Hey. I sent a screenshot. Did you get it?
No, I must have missed it. Can you please post the info here. Thanks,JE
my script editor does not have an option for “trigger.”
Do you know why this could be?
Thank you,
Suzanne
Can you post a screenshot? You should have it, or your GSuite admins have shut it off.
Good Article
What if we are not using a form on submit.
I am manually updating my spreadsheet and created a custom code to generate pdf file. and its working
not trying to update sheet with pdf file link to specific cell.
Any help.
Hi Thanks for reading. I’ve had a lot of people ask about an automation like this that runs from a Google Sheet, so check out this tutorial to see if that helps you out: https://jeffreyeverhart.com/2020/09/29/auto-fill-a-google-doc-template-from-google-sheet-data/