Google Spreadsheet: Hide/Unhide any row that meets x condition in a group of rows when clicking butt-CodePudding

When clicking "button1" I would like the code to check the First Cell (Column A) of Rows 38-58 and hide the Row if the corresponding cell is empty and show if it isn't.

Empty in this case means the cell still has a formula but is just blank. If that's not possible, the "Hide Condition" can be Cells that are not a number.

I'd like to combine it with the code below, which hides and shows Rows 10 and 11 when Cell C4 and D4 equal 0 respectively.

function button2(){
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();

  var cellRef1 = "C4";
  var cellRef2 = "D4";
  var cell1 = sheet.getRange(cellRef1);
  var cell2 = sheet.getRange(cellRef2);
  var value1 = cell1.getValue();
  var value2 = cell2.getValue();
  
  if (value1 == "0"){
    sheet.hideRows(10);
  }
  if (value2 == "0"){
    sheet.hideRows(11);
  }
  if (value1 != "0"){
    sheet.showRows(10);
  }
  if (value2 != "0"){
    sheet.showRows(11);
  };
};

CodePudding user response：

You can hide/unhide the rows this way:

function hide_unhide_rows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getRange('a38:a58').getDisplayValues().flat();

  rows.forEach((cell_value,i) => {
    let index = i 38;
    if (cell_value == '' && !sheet.isRowHiddenByUser(index)) sheet.hideRows(index);
    if (cell_value != '' &&  sheet.isRowHiddenByUser(index)) sheet.unhideRows(index);
  });
}

As for the how it can be combine with your code, you can just add at the end of your function this line:

hide_unhide_rows();

Or, here is the full combined code:

function button2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var value1 = sheet.getRange('c4').getValue();
  var value2 = sheet.getRange('d4').getValue();
  
  if (value1 == 0) { sheet.hideRows(10) } else { sheet.showRows(10) }
  if (value2 == 0) { sheet.hideRows(11) } else { sheet.showRows(11) }

  var rows = sheet.getRange('a38:a58').getDisplayValues().flat();

  rows.forEach((cell_value, i) => {
    let index = i 38;
    if (cell_value == '' && !sheet.isRowHiddenByUser(index)) sheet.hideRows(index);
    if (cell_value != '' &&  sheet.isRowHiddenByUser(index)) sheet.unhideRows(index);
  });
}