Use Sheet.showRows() and Sheet.hideRows() in your onEdit(e) function, like this:
/**
* Simple trigger that runs each time the user edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
if (!e) {
throw new Error('Please do not run the script in the script editor window. It runs automatically when you manually edit the spreadsheet.');
}
hideRowsOnCheckboxClick_(e);
}
/**
* Runs when a checkbox is clicked in the range H1:K1.
* Hides all rows below frozen rows where the checkbox column is blank.
*
* @param {Object} e The onEdit() event object.
*/
function hideRowsOnCheckboxClick_(e) {
// version 1.0, written by --Hyde, 19 September 2022
// - see https://stackoverflow.com/a/73774777/13045193
if (e.value !== 'TRUE' || e.range.rowStart !== 1 || e.range.columnStart < 8 || e.range.columnStart > 11) {
return;
}
const triggerValue = '';
const sheet = e.range.getSheet();
const lastRow = sheet.getLastRow();
const rowStart = sheet.getFrozenRows() + 1;
const numRows = lastRow - rowStart + 1;
sheet.showRows(1, lastRow);
const triggerColumn = sheet.getRange(rowStart, e.range.columnStart, numRows, 1);
const rowsToHide = triggerColumn
.getValues()
.flat()
.map((value, index) => value === triggerValue ? rowStart + index : 0)
.filter(Number);
countConsecutives_(rowsToHide)
.forEach(group => sheet.hideRows(group[0], group[1]));
e.range.setValue(false);
}
/**
* Counts consecutive numbers in an array and returns a 2D array that
* lists the first number of each run and the count of numbers in each run.
* Duplicate values in numbers will give duplicates in result.
*
* The numbers array [1, 2, 3, 5, 8, 9, 11, 12, 13, 5, 4] will get
* the result [[1, 3], [5, 1], [8, 2], [11, 3], [5, 1], [4, 1]].
*
* Typical usage:
* const runLengths = countConsecutives_(numbers.sort((a, b) => a - b));
*
* @param {Number[]} numbers The numbers to group into runs.
* @return {Number[][]} The numbers grouped into runs.
*/
function countConsecutives_(numbers) {
return numbers.reduce(function (acc, value, index) {
if (!index || value !== 1 + numbers[index - 1]) {
acc.push([value]);
}
acc[acc.length - 1][1] = (acc[acc.length - 1][1] || 0) + 1;
return acc;
}, []);
}