Google Sheets, pop up message based on cell value TRUE/FALSE-CodePudding

I am trying to get Google Sheets show a popup message whenever a cell changes from FALSE to TRUE. I have found a few ways of doing it using onEdit functions. The problem here is that the Cell in question does not really get edited, but rather changes value due to another set of cells meeting certain criteria. That means onEdit functions won't run when the value changes.

I tried this script, but it does nothing:

//popup message
function alertMessageYesNoButton() {
  var result = SpreadsheetApp.getUi().alert("Are you sure you want to send an alert about?", SpreadsheetApp.getUi().ButtonSet.YES_NO);
  SpreadsheetApp.getActive().toast(result);
}

function sendMessageYesNo(e){
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var cellValue = sheet.getRange(4, 17).getValue();

  if (cellValue == 'TRUE'){
    alertMessageYesNoButton();
  }else{}
}

If someone could come up with something that works, I would really appreciate it.

CodePudding user response：

From your following reply,

cell D17 changes from FALSE to TRUE when three other checkboxes are checked using this formula: {"";SUMPRODUCT(B16:B18)=COUNTA(B16:B18)}.

In this case, how about checking the checkboxes of "B16", "B17", "B18"? When this is reflected in your showing script, how about the following modification? I thought that when those checkboxes are manually checked, onEdit trigger can be used.

In this modification, when all checkboxes of "B16:B18" of "Sheet1" are checked, your script of alertMessageYesNoButton() is run. So, when you test this, please check all checkboxes of "B16:B18" of "Sheet1". By this, the script is run.

Pattern 1:

function onEdit(e) {
  const sheetName = "Sheet1"; // This is from your script.
  const checkBoxRange = "B16:B18"; // This is from your reply.
  const { range } = e;
  const sheet = range.getSheet();
  const r = sheet.getRange(checkBoxRange);
  const rowStart = r.getRow();
  const rowEnd = rowStart   r.getNumRows() - 1;
  const colStart = r.getColumn();
  const condition1 = sheet.getSheetName() == sheetName;
  const condition2 = range.rowStart >= rowStart && range.rowEnd <= rowEnd && range.columnStart == colStart;
  const condition3 = r.getValues().every(([b]) => b === true);
  if (!condition1 || !condition2 || !condition3) return;

  alertMessageYesNoButton(); // This is your script.
}

Pattern 2:

function onEdit(e) {
  const sheetName = "Sheet1"; // This is from your script.
  const checkBoxRange = "B16:B18"; // This is from your reply.
  const { range } = e;
  const sheet = range.getSheet();
  const r = sheet.getRange(checkBoxRange);
  const rowStart = r.getRow();
  const rowEnd = rowStart   r.getNumRows() - 1;
  const colStart = r.getColumn();
  const condition1 = sheet.getSheetName() == sheetName;
  const condition2 = range.rowStart >= rowStart && range.rowEnd <= rowEnd && range.columnStart == colStart;
  const condition3 = r.getValues().every(([b]) => b === true);
  if (!condition1 || !condition2 || !condition3) return;

  // This is your script.
  var cellValue = sheet.getRange(4, 17).getValue();
  if (cellValue == 'TRUE') {
    alertMessageYesNoButton();
  }
}

Reference:

Simple Triggers

Added:

From your following reply,

this works very well for the example I laid out, but I tried using several multiple instances of the same script within the same sheet and it always works only in one of them. So let´s say I have for instances of the same situation (B16:B18), (B13:B15), (D13:D15) and (D16:D18). Only when I check the boxes corresponding to the last function (from top to bottom), the message pops up.

From your question and your reply, I proposed an answer as the checkboxes of "B16:B18". But from your reply, when your checkboxes are "B16:B18", "B13:B15", "D13:D15", "D16:D18", how about the following sample script?

Sample script:

function onEdit(e) {
  const sheetName = "Sheet1"; // This is from your script.
  const checkBoxRanges = ["B16:B18", "B13:B15", "D13:D15", "D16:D18"]; // This is from your reply.

  const { range } = e;
  const sheet = range.getSheet();
  const actSheetName = sheet.getSheetName();
  const res = checkBoxRanges.find(checkBoxRange => {
    const r = sheet.getRange(checkBoxRange);
    const rowStart = r.getRow();
    const rowEnd = rowStart   r.getNumRows() - 1;
    const colStart = r.getColumn();
    const condition1 = actSheetName == sheetName;
    const condition2 = range.rowStart >= rowStart && range.rowEnd <= rowEnd && range.columnStart == colStart;
    const condition3 = r.getValues().every(([b]) => b === true);
    return condition1 && condition2 && condition3;
  });
  if (!res) return;
  Browser.msgBox(`Checked checkboxes of ${res}`); // Here, you can see the checkbox group that was checked.

  alertMessageYesNoButton(); // This is your script.
}

In this case, by giving const checkBoxRanges = ["B16:B18", "B13:B15", "D13:D15", "D16:D18"], the script detects the checked checkboxe group, and show the range as a sample.