hideRowsWhere(sheet, predicate, headerRows) reads the sheet once, then hides every data row for which predicate(row, rowNumber) is true. Contiguous matches are hidden with one hideRows call per run, not a call per row. showAllRows(sheet) unhides the whole grid so you can start a fresh pass.
When to use
- A menu button that hides "Done" or "Closed" rows the way you would filter by hand
- Temporarily hiding test or old rows without deleting them
- Any view you want to toggle, then restore with Show all
How to use this snippet
/**
* Hide data rows where predicate(rowValues, rowNumber) is true.
* headerRows (default 1) are never hidden. Returns the number of rows hidden.
*
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet
* @param {function(Array, number): boolean} predicate
* @param {number} [headerRows=1]
* @return {number}
*/
/**
* Unhide every row on the sheet (including rows hidden by other means).
*
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet
*/
function hideRowsWhere(sheet, predicate, headerRows) {
headerRows = headerRows == null ? 1 : headerRows;
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
if (lastRow <= headerRows || !lastCol) return 0;
var values = sheet.getRange(1, 1, lastRow, lastCol).getValues();
var hidden = 0;
var runStart = null;
function flush(endRow) {
if (runStart == null) return;
var n = endRow - runStart + 1;
sheet.hideRows(runStart, n);
hidden += n;
runStart = null;
}
for (var i = headerRows; i < values.length; i++) {
if (predicate(values[i], i + 1)) {
if (runStart == null) runStart = i + 1;
} else {
flush(i);
}
}
flush(values.length);
return hidden;
}
function showAllRows(sheet) {
var max = sheet.getMaxRows();
if (max) sheet.showRows(1, max);
}
Example
function hideDoneRows() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Tasks');
showAllRows(sheet);
hideRowsWhere(sheet, function (row) { return row[2] === 'Done'; }, 1); // column C
}
function showAllTaskRows() {
showAllRows(SpreadsheetApp.getActive().getSheetByName('Tasks'));
}
Notes
- This hides rows; it does not delete them or add a Filter. Hidden rows stay in the sheet and in
getLastRow(). - Re-running
hideRowsWherewithoutshowAllRowsfirst will not unhide rows that no longer match — callshowAllRowsat the start of a toggle if you want a clean pass. - The predicate receives the row as a values array (and the 1-based row number). Prefer a header lookup over a hardcoded index when you can.
Happy Coding!
