I have a function that runs based on edits made in Column V starting from Row 2 in a worksheet. That function sends an email on edit.
function onEdit_ColumnV(e){
if(activeSheetName === targetSheetName && activeRow >= startRow && activeCol === 22 && activeVal !== blank){
let incidentClassification = targetSheet.getRange(activeRow,7).getValue();
let incidentDescription = targetSheet.getRange(activeRow,9).getValue();
let site = targetSheet.getRange(activeRow,3).getValue();
let subject = "[ SITE " + site + " ] FACILITIES INCIDENT ESCALATION REPORT";
let main = "<p>Dear Team,</p>" +
"<p>We are writing to inform you about a recent " + incidentClassification + " - " + incidentDescription + " that occurred in our [Site " + site + "]. Please see incident report below.</p>" +
"<hr>";
let closing = "<p>Your cooperation and understanding are greatly appreciated. For any urgent concerns, do not hesitate to contact the facilities management team.</p>" +
"<p>Best regards,</p>" +
"<p>[ SITE " + site + " ] Facilities"
if(incidentClassification === incidentClassification2){
let htmlBodyN1 = main + notification1_Body() + closing;
GmailApp.sendEmail("[email protected]",subject,'',{htmlBody: htmlBodyN1});
setUpTrigger();
}
}
}
After sending an initial email which contains the values of the active row in Column V where the edit was made, the function will create a time-based trigger that sends a second email after 20 minutes.
function setUpTrigger(){
ScriptApp.newTrigger("sendNotification2")
.timeBased()
.after(1000)
.create();
}
The values of the second email are pulled from the same row of the initial email. Here is function sendNotification2().
function sendNotification2(){
let incidentClassificationNotif2 = targetSheet.getRange(activeRow,7).getValue();
let incidentDescriptionNotif2 = targetSheet.getRange(activeRow,9).getValue();
let siteNotif2 = targetSheet.getRange(activeRow,3).getValue();
let subjectNotif2 = "[ SITE " + siteNotif2 + " ] FACILITIES INCIDENT ESCALATION REPORT";
let mainNotif2 = "<p>Dear Team,</p>" +
"<p>We are writing to inform you about a recent " + incidentClassificationNotif2 + " - " + incidentDescriptionNotif2 + " that occurred in our [Site " + siteNotif2 + "]. Please see incident report below.</p>" +
"<hr>";
let closingNotif2 = "<p>Your cooperation and understanding are greatly appreciated. For any urgent concerns, do not hesitate to contact the facilities management team.</p>" +
"<p>Best regards,</p>" +
"<p>[ SITE " + siteNotif2 + " ] Facilities"
let htmlBodyN2 = mainNotif2 + notification2_Body() + closingNotif2;
GmailApp.sendEmail("[email protected]",subjectNotif2,'',{htmlBody: htmlBodyN2});
}
The variable activeRow is declared outside of the functions to be used globally. It’s ss.getActiveCell().getRow().
PROBLEM: When I run function sendNotification2 manually, it gets the values of the active row. But when the time-based trigger runs the function, it gets the values of the header row (row 1). I want the second email to contain the values of the active row, not the row header. The documentation states that when time-driven triggers are used, there no active selections detected, how do I make the function to run 20 minutes after the first email was sent and insert the values of the active row in the second email?
I read a lot of articles about time-driven triggers including the documentation.
3