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