Auto Email when specific conditions in google sheet met

Question

I work in tender admin and am currently working on a sheet that lists all of our open tenders, their due dates, and the tender status (Pending review, to be submitted' etc).

I am trying to set up an Automatic email whenever a new tender is added to the spreadsheet or if there is a change in the due date or status column.

Currently I have the below code and have set up an On Edit Trigger. The problem is that with this code, an email notification is sent whenever there is a change to any cell within the range, so when I am entering a new tender to the sheet a new email notification is triggered as I update each cell within the row, rather than once the whole row is updated.

Does anybody have any ideas how I could update this code? I am very very new to this.

Ive also included an image of the table within the sheet.

//@OnlyCurrentDoc
function processEdit(e) {
  var sheet = SpreadsheetApp.getActive();
  var rows = sheet.getRangeByName("tendersopen").getValues();
  var headerRow = rows.shift();
  var editedRow = e.range.getRow();
  
  var template = HtmlService.createTemplateFromFile("Template");
  template.headerRow = headerRow;
  template.editedRow = editedRow;
  template.rows = rows;
  
  var html = template.evaluate().getContent();
  
  MailApp.sendEmail({
    to: "[email protected]",
    subject: "Tenders sheet updated",
    htmlBody: html
  });
}

Google Sheet Table

Thanks so much, Michelle

I tried to add an if statement to see if I could just get the email to send if the Action column was updated, but this stopped all email notifications coming through.

Answer 1

Try this:

function processEdit(e) {
  const sh = e.range.getSheet();
  const col = [4, 7];//Changing these should get new rows
  const idx = col.indexOf(e.range.columnStart);
  if (sh.getName() == "Your Sheet Name" && ~idx) {
    let t = HtmlService.createTemplateFromFile("Template");
    t.headerRow = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues();
    t.editedRow = sh.getRange(e.range.startRow, 1, 1, sh.getLastColumn()).getValues();
    t.rows = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
    var html = t.evaluate().getContent();
    MailApp.sendEmail({ to: "[email protected]", subject: "Tenders sheet updated", htmlBody: html });
  }
}

This is similar and it only accesses the if when column 4 and 7 are edited

function onEdit(e) {
  e.source.toast("Entry")
  const sh = e.range.getSheet();
  const col = [4, 7];//Changing these should get new rows
  const idx = col.indexOf(e.range.columnStart);
  if (sh.getName() == "Sheet0" && ~idx) {
    e.source.toast("Gate1");
    Logger.log(JSON.stringify(e));
  }
}