so I borrowed some scripts and combined them for my purpose. Unfortunately they don't quite work out as I imagined.. After hours testing I just don't get the result I need.
I'm getting my data from form responses, in ColE the request gets approved or rejected. If "Approved" in ColE I want to send out an email notification to the belonging email in ColA.. My only problem is that the script sends out an email to all email addresses in ColA if something gets approved in ColE (only if confirmation in ColC is empty). Basically I want to catch only one row if status changed. I know row 33 & 34 need to be replaced, but I just couldn't come up with something that worked. Below the script so far.
Thx for help in advance.
function Initialize() {
var triggers = ScriptApp.getProjectTriggers();
for(var i in triggers) {
ScriptApp.deleteTrigger(triggers[i]);
}
ScriptApp.newTrigger("sendNotification")
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onEdit()
.create();
};
/**
*
*/
function sendNotification(e) {
if("E" == e.range.getA1Notation().charAt(0)) {
if(e.value == "Approved") {
var EMAIL_SENT = "EMAIL_SENT";
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;
var numRows = 100;
var dataRange = sheet.getRange(startRow, 1, numRows, 5)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var emailAddress = row[0]; // First column
var message = row[1]; // Second column
var emailSent = row[2]; // Third column
if (emailSent != EMAIL_SENT) {
var subject = row[3]; // Fourth column
MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
SpreadsheetApp.flush();
}
}
}
}
}