Google脚本根据单元格文本隐藏行 [英] Google script hide rows based on cell text

问题描述

我目前正在尝试找到一种方法来隐藏表格11至16的B列中文本为是"的每一行.目前，我可以隐藏所有包含是"但仅在第11张工作表中的行.而不是接下来的几个.任何有关如何解决此问题的想法将不胜感激.

I'm currently trying to find a way to hide each row that has the text "Yes" in column B across sheets 11 to 16. Currently I can get it to hide rows containing "Yes" but only on the 11th sheet and not on the next few. Any ideas on how to fix this would be much appreciated.

function Hide() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  for (var i = 11; i <= 16; i++) {
    var sheet = ss.getSheets()[i];

    sheet.showRows(1, sheet.getMaxRows());
    for (var i = 1; i < sheet.getMaxRows()+1; ++i) {
      if (sheet.getRange(i,2).getValue() == "Yes") {
        sheet.hideRows(i,1);
      }
    }
  }
}

推荐答案

您使用i两次，一次用于工作表，一次用于行.

You are using i twice, once for the sheet, once for the rows.

此外，如果您有很多行，则一次读取该列，然后遍历数据是有意义的.

Also, if you have a lot of rows it makes sense to read the column once and then iterate through the data.

function Hide() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (var sheetI = 1; sheetI <= 3; sheetI++) {
    var sheet = sheets[sheetI];
    sheet.showRows(1, sheet.getMaxRows());
    var colB = sheet.getRange("B:B").getValues().map(function(row) {return row[0];});

    colB.forEach(function(value, rowI) {
      if (value === "Yes") {
        sheet.hideRows(rowI + 1, 1);
      }
    });
  }
}

您甚至可以扩展它以隐藏连续的是"(如果经常这样做会使其更快)

You could even expand this to hide continuous rows of "Yes" if that happens often to make it faster

这篇关于Google脚本根据单元格文本隐藏行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！