Google Spreadsheet: Hide/Unhide any row that meets x condition in a group of rows when clicking button?

user_13228809 0
Viewed 56

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);
  };
};
google-apps-script google-sheets javascript
1 Answers

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

Update

If you have several ranges to process, you have to change several lines in the code this way:

function button(){
  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 list = ['a27:a29','a41:a60','a66:a85']; // <----------------- here
  var rangeList = sheet.getRangeList(list).getRanges(); // <------- here
  for (var range in rangeList){
    var rows = rangeList[range].getDisplayValues().flat();
    var shift = +list[range].split(':')[0].slice(1); // <---------- here

    rows.forEach((cell_value, i) => {
      let index = (i + shift); // <-------------------------------- here
      if (cell_value == '' && !sheet.isRowHiddenByUser(index)) sheet.hideRows(index);
      if (cell_value != '' &&  sheet.isRowHiddenByUser(index)) sheet.showRows(index);
    
    });
  }
}

If you have too many ranges the code can work slowly. There is a way to improve it a bit. But the hide/unhide operation is sluggish by design. So the code will be quite slow anyway.

user_142654690
Related