'Running a function in apps script when a certain value in a range is changed to a specific Value
I have this function that I use to loop thru a spreadsheet and send emails depending on the value of the status Column. I'm currently using a trigger that runs the sendEmailFunction on change in the spreadsheet.
However, I would prefer to write a programmatic trigger, that runs this sendEmailFunction when the cell value in the statusCol Range is changed from blank -> Initiate. I have tried using the onEdit(e) method, but can't seem to get it to work. Thanks
function sendEmailFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getSheets()[0];
var totalRows = ss.getLastRow() -1;
var statusCol = ss.getRange(2, 16, totalRows, 1).getValues();
for (var i=0; i<statusCol.length; i++)
{
if(statusCol[i] == 'Email Sent' || statusCol[i] == null)
{
continue;
}
else
{
var col1 = ws.getRange(i+2, 1).getValue();
var col2 = ws.getRange(i+2, 2).getValue();
var status = ws.getRange(i+2, 16).getValue();
}
}
if (status == 'Initiate'){
MailApp.sendEmail('[email protected]', 'subject', 'body')
}
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
