Trigger an email when a cell is a certain colour?

Question

I have very VERY limited knowledge on this. So little I probably shouldn't even refer to it as "knowledge"

This is what I'm trying to do: I have a column with dates referring to send dates. If the date has already passed, the cell turns red (#a33838). I want to send an email if a cell in that column turns red, so that I know it has not been sent, and needs doing. I also want the email to hold the information of the row that cell is in

I have been looking through the forums and I think I need a mixture of these two different posts:

Change cell value based on cell color in google spreadsheet

Trigger an email when a cell has certain values

If anyone knows a way to do this, it would really be a lifesaver! Thanks so much.

There are no triggers that do this directly. However, you could use a time based trigger to search for these changes occasionally throughout the day. — Cooper, Oct 29 '19 at 17:22
You don't need color check. Just do date check- you need to do this on your own — TheMaster, Oct 29 '19 at 17:57

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

This code will help you in what you need

var EMAIL_ADDRESS = "email@domain";
var SUBJECT = "Passed dates";
var message = "";

function alertDate() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // get the sheet where values are
  var date_col = sheet.getRange(1, 1, sheet.getLastRow()); // get the column with the dates
  var dates = date_col.getValues();
  var row = 0;
  for(var i = 0; i < dates.length; i++){ // iterate over all dates 
    if( dates[i] < Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy")){ // check if date already passed
      date_col.getCell(i + 1, 1).setBackground("#A33838"); // change cell´s color
      row = i + 1;
      message += "This date " + dates[i] + " on row " + row.toString() + " has already passed\n"; // build message to send
    }
  }
  MailApp.sendEmail(EMAIL_ADDRESS, SUBJECT, message); // send email
}

Notice:

I´m using the format "MM/dd/yyyy" for the dates in my example, so if you use another format in your sheet, you need to change this:

Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy")

Trigger an email when a cell is a certain colour?

1 Answers1

Notice:

Linked