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