Google Spreadsheet conditional on three cells

Question

I've been trying to implement a conditional on my spreadsheet, basically a check-sheet with three conditional cells with "Yes" or "No" in them. All I want to achieve (using onEdit) is one all three cells contain "Yes", enter the next column with the date the final Yes was entered. I've managed to create other scripts which work fine, but this one has me stumped.

Thanks

Answer 1

Since the cells can be edited individually, your onEdit will always need to check all of your conditional cells' values, and write the timestamp only when all are "Yes".

function onEdit(event) {
  var conditionalCells = [ "B1", "B2", "B3" ];  // Array of monitored conditionals
  var inList = false;                           // assume edit was outside of the conditionals
  var allYes = true;                            // and that all values are "Yes".
  var sheet = event.source;  // Sheet that was edited
  var cell = event.range.getA1Notation();  // get range description
  // Loop through all conditionals checking their contents.
  // Verify that the edit that triggered onEdit() was in one
  // of our conditional cells, setting inList true if it was.
  for (var i = 0; i < conditionalCells.length && allYes; i++) {
    if (cell == conditionalCells[i]) inList = true;
    allYes = (sheet.getRange(conditionalCells[i]).getValue() == "Yes");
  };
  // If this was our final Yes, record the date.
  // By validating inList, we ensure we record only the first time
  // all conditionals are "Yes".
  if (inList && allYes) sheet.getRange("C1").setValue(new Date());
}