// (Addition) I need this trigger to still run for the specific email address that was previously added
function sendEmail() {
var activesh = SpreadsheetApp.getActiveSpreadsheet();
var sheet = activesh.getSheetByName('emailreminder');
var dataValues = sheet.getDataRange().getValues();
var e = 0;
var last = getLastRowSpecial(e);
console.log(last);
var email = dataValues[last][0]; // Column A (Email Address)
console.log(email);
var subject = dataValues[last][1]; // Column B (Subject)
console.log(subject);
var now = new Date(); // Date today
console.log(now);
var oneWeek = new Date(dataValues[last][7]); // Column H (After 7 days)
console.log(oneWeek);
if (now <= oneWeek) { // If today is one week after the response, remind the corresponding email address.
var message = dataValues[last][5]; // Column F (Message)
MailApp.sendEmail(email, subject, message); }
else { // For testing only. Instead of a deadline, I want this trigger to stop
// if recipient replied "DONE" instead.
ScriptApp.getProjectTriggers().forEach(function(trigger) {
if (trigger.getHandlerFunction() == "sendEmail") {
ScriptApp.deleteTrigger(trigger);
}
});
}
}
// This function identifies the last row of the spreadsheet that is blank, so it makes the last row
// the blank row which is next to the recently added row/data
function getLastRowSpecial(e) {
const ss = SpreadsheetApp.getActiveSpreadsheet()
const sheet = ss.getSheetByName("emailreminder");
const lastRow = sheet.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() - 1;
console.log(lastRow);
return lastRow
}
// This function creates a trigger that runs sendEmail. This trigger is timebased.
// The thing is I also want to make this so that after a week, it will also run every two days until the
// recipient replies "DONE". Also applies to other email address that was previously added.
function createTrigger() {
ScriptApp.newTrigger("sendEmail")
.timeBased()
.everyWeeks(1)
.create();
}
Thank you very much.