Google电子表格有三个单元的条件 [英] Google Spreadsheet conditional on three cells

问题描述

我一直试图在我的电子表格上实现一个条件，基本上是一张带有三个条件单元的检查表，其中有是或否。我想实现的所有（使用onEdit）是一个全部三个单元格都包含是，输入下一列并输入最终是的日期。我已经设法创建了其他脚本可以正常工作，但这一个让我很难过。

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

推荐答案

由于单元格可以单独进行编辑，因此onEdit将始终需要检查所有条件单元格的值，并且只有在全部为是时才写入时间戳记。 b
$ b

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

这篇关于Google电子表格有三个单元的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！