I am using a google spreadsheet which looks like:
A B C D
1 Name e-mail Identifer Status
2 Alex ax@gmail.com ERT ER A
3 Micke miike477@gmail.com Ejyu er w
4 John john7788@tri.com Arb Ed C
I have a drop down list in column D
(let say A,B & C for example), now i want that whenever the value changes (Initially the column D
would be blank) in column D
against a particular Name
than an automatic e-mail trigger to e-mail id mentioned in column B
by below mentioned sender id and content.
The email should be trigger whenever value changes in column D
except for the blank, and if there were previously value was "B" and now it change to "C" than mail should be trigger.
Sender-example@gmail.com
CC-test1@gmail.com,test2@gmail.com
E-mail Body:
Hi Alex (Should be picked from column A depending against which name e-mail trigger
)
some sentence here.
some sentence here with your ERT ER (Should be pick from column C
) has status A (should be pick from column D
).
Regards,
example
123456789
I am trying using below mentioned script (which is working fine just want to bold value of identifier
and status
in e-mail body):
function sendEmailToUser(event){
var eventRange = event.range;
var sheet = eventRange.getSheet();
var sheetName = sheet.getName();
var column = eventRange.getColumn();
if (sheetName == "Sheet1" && column == 4){ // Make sure the edited column is in the correct sheet, otherwise editing Column D in Sheet3 might trigger this
var row = eventRange.getRow(); // You need to know which row so you can send the email to the correct person
var rowValues = sheet.getRange(row, 1, 1, 4).getValues();
var name = rowValues[0][0];
var sendTo = rowValues[0][1];
var identifier = rowValues[0][2];
var status = rowValues[0][3];
if (status != "") { // Don't send the email if the status is blank
var cc = "test1@example.com, test2@example.com";
var subject = "What is the: " + identifier;
var content = "Hi " + name + "\nWhat is the value " + identifier + " with the status " + status + "?";
MailApp.sendEmail(sendTo, subject, content, {
cc: cc
});
}
}
}