Google spreadsheet for three cells

Question

I am trying to fulfill a condition on my spreadsheet, basically a checklist with three conditional cells with "Yes" or "No" in them. All I want to achieve (using onEdit) is all three cells containing "Yes", enter the next column with the date of the last Yes. I managed to create other scripts that work fine, but everything is fine with me.

thank

Answer 1

Since cells can be edited individually, your onEdit will always check all the values ​​of your conditional cells and record the timestamp only when everything is "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()); }