CODEWITHSUNDEEP
google-sheets
Unfortunate
Unfortunate

Reputation: 15

Google script hide rows based on cell text

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

Upvotes: 1

Views: 2439

Answers (1)

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

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

Upvotes: 2

Related Questions