Ops filter the sheet to “Status = Ready, Region = East” and click your menu item. They expect it to act on what they can see. getValues() ignores filters completely. getVisibleRowObjects(sheet, headerRow) returns header-keyed objects only for rows that aren’t hidden by a filter (isRowHiddenByFilter) or hidden by hand (isRowHiddenByUser), with _row attached for write-backs.
When to use
- “Run on filtered rows” menu actions
- Exports / emails that should match the current filter
- Letting ops pick a batch with the filter UI instead of editing a Status column
How to use this snippet
/**
* Header-keyed objects for data rows that are currently visible:
* not hidden by a filter and not manually hidden.
*
* @param {GoogleAppsScript.Spreadsheet.Sheet=} sheet default: active sheet
* @param {number=} headerRow default: 1
* @return {Object[]} each with _row (1-based sheet row)
*/
function getVisibleRowObjects(sheet, headerRow) {
sheet = sheet || SpreadsheetApp.getActiveSheet();
headerRow = headerRow || 1;
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
if (lastRow <= headerRow || lastCol < 1) return [];
var headers = sheet.getRange(headerRow, 1, 1, lastCol).getValues()[0];
// Read all data once; only the visibility checks are per row.
var values = sheet.getRange(headerRow + 1, 1, lastRow - headerRow, lastCol).getValues();
var out = [];
values.forEach(function (vals, i) {
var row = headerRow + 1 + i;
// Filtered out, or hidden with right-click → Hide row: skip it.
if (sheet.isRowHiddenByFilter(row) || sheet.isRowHiddenByUser(row)) return;
var obj = { _row: row };
headers.forEach(function (h, c) {
var key = String(h == null ? '' : h).trim();
if (key) obj[key] = vals[c]; // blank headers are ignored
});
out.push(obj);
});
return out;
}
Example
function emailVisibleRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = getVisibleRowObjects(sheet);
var ui = SpreadsheetApp.getUi();
if (!sheet.getFilter()) {
var go = ui.alert('No filter is on, so this will run on every visible row. Continue?', ui.ButtonSet.YES_NO);
if (go !== ui.Button.YES) return;
}
rows.forEach(function (row) {
sendUpdate_(row); // your worker
});
SpreadsheetApp.getActive().toast(rows.length + ' visible row(s) processed', 'Done', 5);
}
Tips:
- Filter views (the personal ones) don’t count. Only the sheet’s main filter hides rows for
isRowHiddenByFilter. If ops use filter views, ask them to use the regular filter for this action. - The visibility checks are one call per row. That’s fine for a few thousand rows; for bigger sheets, filter by a value in code instead.
- Grouped rows that are collapsed count as hidden by the user.
- Twin helpers: getSelectedRowObjects (what’s highlighted) and getActiveRowAsObject (one row).
Tip: NitroGAS Co-Pilot can switch an existing “run on all rows” menu to getVisibleRowObjects so it follows whatever filter ops have on.
Happy Coding!
