I just creating google form and wishing an Confirmation Email is sent after user input their email address.
Thanks to www.labnol.org, I have modifed the script. So this is complete Step
- After Creating Google Form, Linked it to Google Sheet
- From Google Sheet, select Tolols Script Editor
- Copy Paste This Script
You Can Change CC, BCC, and message column
Note : This Script Read Email From Column Name : Email Aktif, you can modify column name here
This script has logic if number of response reached more than 150 it responded with different email
/* Send Confirmation Email with Google Forms */
function Initialize() {
var triggers = ScriptApp.getProjectTriggers();
for (var i in triggers) {
ScriptApp.deleteTrigger(triggers[i]);
}
ScriptApp.newTrigger(“SendConfirmationMail”)
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onFormSubmit()
.create();
}
function SendConfirmationMail(e) {
try {
var ss, cc,bcc, sendername, subject, columns,nama;
var message, value, textbody, sender,message2;
var sheet,lastRow;
// This is your email address and you will be in the CC
cc = “i@gmail.com”;
bcc=”K@gmail.com”
nama=e.namedValues[“Nama Lengkap”].toString();
// This will show up as the sender’s name
sendername = “ai”;
// Optional but change the following variable
// to have a custom subject for Google Docs emails
ss = SpreadsheetApp.getActiveSheet();
columns = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];
lastRow=ss.getLastRow();
lastRow=lastRow-1;
// This is the body of the auto-reply
message = ” YOUR MESSAGE”;
message2= ” YOUR MESSAGE2″
// This is the submitter’s email address
// Make sure you havea field called Email Address in the Google Form
sender = e.namedValues[“Email Aktif “].toString();
// Only include form values that are not blank
for (var keys in columns) {
var key = columns[keys];
var val = e.namedValues[key] ? e.namedValues[key].toString() : “”;
if (val !== “”) {
message += key + ‘ :: ‘ + val + “<br />”;
}
}
if (lastRow < 152) {
textbody = message.replace(“<br>”, “\n”);
subject = “Subject1”;
GmailApp.sendEmail(sender, subject, textbody, {
cc: cc,
bcc: bcc,
name: sendername,
htmlBody: message
});
}
else
{
textbody = message2.replace(“<br>”, “\n”);
subject = “Sorry, more than 150”;
GmailApp.sendEmail(sender, subject, textbody, {
cc: cc,
bcc: bcc,
name: sendername,
htmlBody: message2
});
}
} catch (e) {
Logger.log(e.toString());
}
}