I have a spreedsheet that gets fed from a form. Based on the yes or no value in column F it will send one email for the No answers and another for the Yes answers. It has been working for me but every so often it will send both emails at the same time.
Unfortunately the sheets are protected by my admin and I can no change it. Here is a copy of the scripts. Please have a look and see if I am missing anything. Or if there is anything you can see that would cause both emails to be sent.
If 'No' is found-
function sendEmails() {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1").activate();
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Template 2").getRange(1,1).getValue();
for (var i = 2;i<=lr;i++){
var currentEmail = ss.getRange(i,1).getValue();
var currentName = ss.getRange(i,5).getValue();
var currentMessage = ss.getRange(i,2).getValue();
var currentName1 = ss.getRange(i,5).getValue();
var messageBody = templateText.replace("{Name}",currentName1);
var subjectLine1 = "COVID-19 UPDATE-" + currentName + "-Fit For Duty";
var status = ss.getRange(i, 2).getValue();
var None = ss.getRange(i, 6).getValue();
if (None == "None"){
if (status == "")
MailApp.sendEmail('trevor.donaldson@lafargeholcim.com, christine.crumbie@lafargeholcim.com', subjectLine1, messageBody);
ss.getRange(i, 2).setValue("Email Sent")
}
}
}
If 'yes' is found-
function sendEmails1() {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1").activate();
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Template 1").getRange(1,1).getValue();
for (var i = 2;i<=lr;i++){
var currentEmail = ss.getRange(i,1).getValue();
var currentName = ss.getRange(i,5).getValue();
var currentMessage = ss.getRange(i,2).getValue();
var currentName1 = ss.getRange(i,5).getValue();
var currentName2 = ss.getRange(i,6).getValue();
var currentName3 = ss.getRange(i,7).getValue();
var currentName4 = ss.getRange(i,10).getValue();
var currentName5 = ss.getRange(i,11).getValue();
var currentName6 = ss.getRange(i,12).getValue();
var currentName7 = ss.getRange(i,8).getValue();
var currentName8 = ss.getRange(i,9).getValue();
var currentName9 = ss.getRange(i,13).getValue();
var currentName10 = ss.getRange(i,14).getValue();
var messageBody = templateText.replace("{Name}",currentName1).replace("{Answer}",currentName2).replace("{Answer}",currentName3).replace("{Answer}",currentName4).replace("{Answer}",currentName5).replace("{Answer}",currentName6).replace("{Answer}",currentName7).replace("{Answer}",currentName8).replace("{Answer}",currentName9).replace("{Answer}",currentName10)
var subjectLine1 = "COVID-19 UPDATE-" + currentName + "-MAY NOT BE FIT FOR DUTY";
var status = ss.getRange(i, 2).getValue();
var YES = ss.getRange(i, 6).getValue();
if (YES = "Yes"){
if (status == "")
MailApp.sendEmail('trevor.donaldson@lafargeholcim.com', subjectLine1, messageBody);
ss.getRange(i, 2).setValue("Email Sent")
Logger.log(MailApp.sendEmail)
}
}
}
I have these set up with a trigger to send every time to form submits something.
Thanks for your time.
Aucun commentaire:
Enregistrer un commentaire