I have a sheet where productivity actions are tracked, on a 'good day' people change the cell background colour to green which requires no action, if it's a bad day (not met certain KPIs) then they will change the cell colour to red, if the day's cell colour is changed to red they must raise a remidial action to resolve/look into the issue.

I want to create a warning message via a script where if someone changes the background colour of a cell to red, a warning/reminder message appears and says 'Please remember to raise/check for actions relating to this issue'.

For an example:

• My cell range with the data in is RW6:TR36
• If any cells in this range have the background colour changed to #ff0000, a warning message will appear
• The warning message would only need to appear if the last change made is changing a cell background colour to #ff0000 thus ignoring any cells in the range that had previously been coloured to #ff0000 on previous days

Thankyou.

CodePudding user response：

This is quite a modified solution from @MiMi's answer. Since based on your description, you are trying to add a warning message on a specific cell when the background color was changed to red. Instead of displaying an alert box, this will add a note in the cell that was modified, including the timestamp when the cell was changed.

Sample Code:

function onChange(e) {
  Logger.log(JSON.stringify(e));
  var sh = e.source.getActiveSheet();
  var cell = e.source.getActiveCell();

  if (sh.getName() == 'Sheet2' && e.changeType == "FORMAT" &&
      cell.getRow() >= 6 && cell.getRow() <= 36 &&
      cell.getColumn() >= 4 && cell.getColumn() <= 7) {
    if (cell.getBackground() == '#ff0000') {
      // Add note to the cell
      var dateTimeStr = Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"yyyy-MM-dd' 'HH:mm:ss");
      var note = "Please remember to raise/check for actions relating to this issue."
      note  = "\n\nNote Created: " dateTimeStr;
      cell.setNote(note);
    }
  }
}

• In this example, the note will be added when range D6:G36 background color was changed to red.

What it does?

1. Check if the modified cell is in sheet Sheet2 and if the changeType is FORMAT
2. Check if the modified cell is within rows 6-36. Check if modified cell is within columns D-G (index 4-7). In your case, you need to get the column index of RW and TR
3. Check if the background color of the modified cell is red. Create a timestamp string and add a note to that cell.

Output:

Note:

It is not possible to use data validation help text as a warning message, that is why I decided to use note instead.

CodePudding user response：

Generate an alert message if background color is changed to red by a user format change:

function onMyChange(e) {
  //e.source.toast('Entry');
  const sh = e.source.getActiveSheet();
  if (sh.getName() == 'Sheet0' && e.changeType == "FORMAT") {
    const rg = e.source.getActiveCell();
    if (rg.getBackground() == '#ff0000') {
      //e.source.toast('bg is red')
      SpreadsheetApp.getUi().alert('Type your alert message here.')
    }
  }
}

I had to use an installable trigger to get this to work.