如果选中了复选框，则Google Sheets脚本可以隐藏行 [英] Google Sheets Script to Hide Row if Checkbox Checked

问题描述

我正在尝试找到一个工作代码，如果选中了该行F列中的复选框，该代码将自动隐藏该行。

I am trying to find a working code that will automatically hide a row if the checkbox in column F of that row is checked.

我尝试了所有找到的脚本，但似乎没有任何效果。不幸的是，我不精通代码，无法找到问题。

I have tried every script I have found and nothing seems to work. Unfortunately I am not code savvy and I am unable to find the issue.

这是我目前拥有的：

function onOpen() {
  var s = SpreadsheetApp.getActive().getSheetByName("Checklists");
  s.showRows(1, s.getMaxRows());

  s.getRange('F2:F200')
    .getValues()
    .forEach( function (r, i) {
    if (r[0] == "TRUE") 
      s.hideRows(i + 1);
    });
}

我正在处理的工作表是清单，并且包含复选框为F。该复选框的值为TRUE或FALSE。如果值为TRUE，则我希望该行被隐藏。

The sheet I am working on is "Checklists" and the column that contains the checkbox is F. The value of the checkbox is either TRUE or FALSE. If the value is TRUE, I want that row to be hidden.

有人可以帮忙!!!

推荐答案

快速测试我能够运行的是在F列中设置一列复选框，然后创建一个函数来捕获工作表上的每个编辑事件。当用户选中一个框并隐藏该行时，它将立即捕获。

The quick test I was able to run was to set up a column of checkboxes in column F, then to create a function that catches each edit event on the sheet. This will immediately catch when the user checks a box and will then hide that row.

使用 onEdit 事件与确定实际更改的单元格有关。对于您而言，仅当F列中的复选框发生更改时，您才希望完全遵循自己的逻辑。在我的代码中，我一直在使用一个函数来确保更改在所需范围内。函数如下所示：

The trick with using the onEdit event is with determining which cell was actually changed. In your case, you only want to fully follow your logic if the change happens to a checkbox in column F. In my code, I've been using a function to make sure the change is in the desired range. The function looks like this:

function isInRange(checkRange, targetCell) {
  //--- check the target cell's row and column against the given
  //    checkrange area and return True if the target cell is
  //    inside that range
  var targetRow = targetCell.getRow();
  if (targetRow < checkRange.getRow() || targetRow > checkRange.getLastRow()) return false;

  var targetColumn = targetCell.getColumn();
  if (targetColumn < checkRange.getColumn() || targetColumn > checkRange.getLastColumn()) return false;

  //--- the target cell is in the range!
  return true;
}

因此，您所有的 onEdit 函数要做的是在触发编辑事件时进行快速调用，以查看更改是否在您要查找的范围内。在这种情况下，我用范围设置了一个变量来检查：

So then all your onEdit function has to do is to make a quick call when the edit event is fired to see if the change falls within the range you're looking for. In this case, I set up a variable with my range to check:

var thisSheet = SpreadsheetApp.getActiveSheet();
var checkRange = thisSheet.getRange("F2:F200");  
if (isInRange(checkRange, eventObj.range)) {

选择行号并隐藏或显示的问题。这是完整的示例解决方案：

After that, it's just a matter of picking the row number and hiding or showing. Here's the full example solution:

function isInRange(checkRange, targetCell) {
  //--- check the target cell's row and column against the given
  //    checkrange area and return True if the target cell is
  //    inside that range
  var targetRow = targetCell.getRow();
  if (targetRow < checkRange.getRow() || targetRow > checkRange.getLastRow()) return false;

  var targetColumn = targetCell.getColumn();
  if (targetColumn < checkRange.getColumn() || targetColumn > checkRange.getLastColumn()) return false;

  //--- the target cell is in the range!
  return true;
}

function onEdit(eventObj) {
  //--- you could set up a dynamic named range for this area to make it easier
  var thisSheet = SpreadsheetApp.getActiveSheet();
  var checkRange = thisSheet.getRange("F2:F200");  
  if (isInRange(checkRange, eventObj.range)) {
    //--- so one of the checkboxes has changed its value, so hide or show
    //    that row
    var checkbox = eventObj.range;
    var rowIndex = checkbox.getRow();
    Logger.log('detected change in checkbox at ' + checkbox.getA1Notation() + ', value is now ' + checkbox.getValue());
    if (checkbox.getValue() == true) {
      Logger.log('hiding the row');
      thisSheet.hideRows(rowIndex, 1);
    } else {
      Logger.log('showing the row');
      thisSheet.showRows(rowIndex, 1);
    }
  }
}

这篇关于如果选中了复选框，则Google Sheets脚本可以隐藏行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！