The code needs to send a mail only once when there is an edit in the 'KYC' sheet of the Google Spreadsheet. I am attaching an installable trigger to the function. There are similar functions and associated triggers for other sheets as well (Total 8 sheets & 8 Triggers).
CODITION : - the trigger needs to be executed only when a person is finished editing the C column not when he / she is editing in the A or B column.
But if an edit is done on any of the sheets of the Google Spreadsheet, all the Triggers are executed an the person who is supposed to get only mail ends up getting more than 20 mails at once.
{
var ss = SpreadsheetApp.getActive();
var sheet1 = ss.getSheetByName("KYC");
var value1 = sheet1.getRange("A13:A").getValue();
var value2 = sheet1.getRange("C13:C").getValue();
var value3 = sheet1.getRange("D13:D").getValue();
if(value1 !== "" && value2 !== "" && value3 !== "")
{
MailApp.sendEmail("abc@abc.com","Query added in KYC Sheet","A new query has been added in the KYC Sheet. Please confirm")
MailApp.sendEmail("def@abc.com","Query added in KYC Sheet","A new query has been added in the KYC Sheet. Please confirm")
MailApp.sendEmail("ghi@abc.com","Query added in KYC Sheet","A new query has been added in the KYC Sheet. Please confirm")
}
}```
If you want to send an email only once when someone has finished editing column C, only consider the current row i.e.
function specialOnEdit(event){
var sheet1 = event.source.getActiveSheet();
var rng = event.source.getActiveRange();
if (sheet1.getName()=='KYC' && rng.getColumn()==3){
var value1 = sheet1.getRange("A"+rng.getRow()).getValue();
var value2 = sheet1.getRange("C"+rng.getRow()).getValue();
var value3 = sheet1.getRange("D"+rng.getRow()).getValue();
if(value1 !== "" && value2 !== "" && value3 !== ""){
// send email
sheet1.getRange("Z"+rng.getRow()).setValue('sent on '+new Date())
}
}
}
put an installable trigger on specialOnEdit. I also suggest memorizing the date as a flag to know if an email has already been sent, and test the value as for A, B and C