Google 电子表格以三个单元格为条件

我一直在尝试在我的电子表格上实现一个条件，基本上是一个包含三个条件单元格的检查表，其中包含“是”或“否”。我想要实现的所有目标（使用 onEdit）是所有三个单元格都包含“是”，输入下一列以及输入最终“是”的日期。我已经设法创建了其他运行良好的脚本，但这个脚本让我难住了。

Thanks

由于单元格可以单独编辑，因此您的 onEdit 将始终需要检查所有条件单元格的值，并仅在所有单元格均为“是”时才写入时间戳。

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());
}