Named ranges, filters, and pivots
Use NamedRange, Filter, PivotTable, and Protection in Google Apps Script to keep Sheet scripts working as columns move, filter rows, and lock summaries.
Register a request table as a named range while checking its headers, filter Closed rows and distinguish visible rows from hidden ones, rebuild a Department pivot table on a dedicated Summary tab, and lock the summary with a warning-only protection that cleans up cleanly.
Track a spreadsheet table through a named range as columns shift, hide closed rows with a sheet filter while checking which rows remain visible, build a Department summary pivot table on its own tab, and guard that summary tab against accidental edits. Before starting, complete Read and write Sheets in batches and Validate, format, and track Sheets edits.
Hardcoded A1 coordinates such as Requests!A1:E7 work on day one and break as soon as a teammate inserts a notes column to the left of the table or adds rows in the middle. Combining a NamedRange with a header-row check lets Google Sheets track structural shifts automatically while your script still verifies that every required column exists before touching filters, pivot tables, or protections.
Set up the disposable Requests workbook
Create an isolated spreadsheet so you can test column insertions, shared filters, pivot rebuilds, and sheet protections without affecting live team data:
Create a new Google Sheet and rename its first tab
Requests.Copy
sample.csvbelow intoRequests!A1, select column A, click Data > Split text to columns, and choose Comma as the separator.Confirm that
Requests!A1:E7holds one header row and six fictional request rows (REQ-201throughREQ-206).Open Extensions > Apps Script and replace
Code.gswith the example source below.In Project Settings, check Show "appsscript.json" manifest file in editor, replace
appsscript.jsonwith the included manifest, and save both files.
Request ID,Department,Priority,Hours,Status
REQ-201,Facilities,High,4,Open
REQ-202,IT,Medium,6,In progress
REQ-203,Facilities,Low,2,Closed
REQ-204,Finance,High,5,Open
REQ-205,IT,High,3,Closed
REQ-206,Finance,Medium,8,In progress
const REQUESTS_SHEET_NAME = 'Requests';
const SUMMARY_SHEET_NAME = 'Summary';
const NAMED_RANGE_NAME = 'RequestTable';
const PROTECTION_DESCRIPTION = 'Request tracker summary';
const HIDDEN_STATUS = 'Closed';
const MAX_TABLE_ROWS = 100;
const REQUIRED_HEADERS = ['Request ID', 'Department', 'Priority', 'Hours', 'Status'];
/**
* @typedef {{
* requestIdIndex: number,
* departmentIndex: number,
* priorityIndex: number,
* hoursIndex: number,
* statusIndex: number,
* requestIdSheetColumn: number,
* departmentSheetColumn: number,
* prioritySheetColumn: number,
* hoursSheetColumn: number,
* statusSheetColumn: number
* }} RequestColumnMap
*/
/**
* Registers or resolves the RequestTable named range, filters out Closed rows on
* Requests, logs all rows versus filter-visible rows, rebuilds the Summary pivot
* table, and applies a warning-only protection on the Summary tab.
*
* @returns {void}
*/
function buildRequestSummaryView() {
const spreadsheet = getBoundSpreadsheet_();
const resolved = resolveOrCreateRequestTable_(spreadsheet);
const requestsSheet = resolved.sheet;
const tableRange = resolved.range;
const columns = resolved.columns;
checkTableValues_(tableRange, columns);
const existingFilter = requestsSheet.getFilter();
if (existingFilter) {
existingFilter.remove();
}
const filter = tableRange.createFilter();
const activeCriteria = SpreadsheetApp.newFilterCriteria()
.setHiddenValues([HIDDEN_STATUS])
.build();
filter.setColumnFilterCriteria(columns.statusSheetColumn, activeCriteria);
// Flush pending filter changes before querying per-row filter visibility.
SpreadsheetApp.flush();
const counts = summarizeVisibleVsAllRows_(requestsSheet, tableRange, columns);
const summarySheet = rebuildSummaryPivotTab_(spreadsheet, tableRange, columns);
const protectionCreated = protectSummarySheetWithWarning_(summarySheet);
Logger.log(
(resolved.createdNamedRange ? 'Registered ' : 'Reused ') +
NAMED_RANGE_NAME +
' at ' +
requestsSheet.getName() +
'!' +
tableRange.getA1Notation() +
'.'
);
Logger.log(
'getValues() returned ' +
counts.totalRows +
' data rows (' +
counts.totalHours +
' hours); filter-visible rows: ' +
counts.visibleRows +
' (' +
counts.visibleHours +
' hours: ' +
counts.visibleIds.join(', ') +
').'
);
Logger.log('Rebuilt Department pivot table on ' + SUMMARY_SHEET_NAME + '!A1.');
Logger.log(
protectionCreated
? 'Added warning-only sheet protection on ' + SUMMARY_SHEET_NAME + '.'
: 'Warning-only sheet protection already present on ' + SUMMARY_SHEET_NAME + '.'
);
}
/**
* Read-only check that resolves RequestTable and compares all rows returned by
* getValues() against rows currently visible through the sheet filter.
*
* @returns {void}
*/
function inspectVisibleRequests() {
const spreadsheet = getBoundSpreadsheet_();
const tableRange = spreadsheet.getRangeByName(NAMED_RANGE_NAME);
if (!tableRange) {
throw new Error(
NAMED_RANGE_NAME +
' named range is missing or was deleted. Run buildRequestSummaryView first.'
);
}
const sheet = tableRange.getSheet();
if (sheet.getName() !== REQUESTS_SHEET_NAME) {
throw new Error(NAMED_RANGE_NAME + ' must point to the ' + REQUESTS_SHEET_NAME + ' tab.');
}
if (tableRange.getNumRows() < 2 || tableRange.getNumRows() > MAX_TABLE_ROWS) {
throw new Error(NAMED_RANGE_NAME + ' must have between 2 and ' + MAX_TABLE_ROWS + ' rows.');
}
const headerRow = tableRange.getValues()[0];
const columns = mapHeaderColumns_(headerRow, tableRange.getColumn());
const counts = summarizeVisibleVsAllRows_(sheet, tableRange, columns);
Logger.log(
NAMED_RANGE_NAME +
' resolves to ' +
sheet.getName() +
'!' +
tableRange.getA1Notation() +
' (Status at sheet column ' +
columns.statusSheetColumn +
').'
);
Logger.log(
'All rows in getValues(): ' +
counts.totalRows +
' (' +
counts.totalHours +
' hours). Visible rows: ' +
counts.visibleRows +
' (' +
counts.visibleHours +
' hours: ' +
counts.visibleIds.join(', ') +
').'
);
}
/**
* Removes this script's named range, Requests sheet filter, Summary pivot table,
* and Summary sheet protection without deleting Request rows.
*
* @returns {void}
*/
function removePracticeStructureAndProtection() {
const spreadsheet = getBoundSpreadsheet_();
if (spreadsheet.getRangeByName(NAMED_RANGE_NAME)) {
spreadsheet.removeNamedRange(NAMED_RANGE_NAME);
}
const requestsSheet = spreadsheet.getSheetByName(REQUESTS_SHEET_NAME);
if (requestsSheet) {
const filter = requestsSheet.getFilter();
if (filter) {
filter.remove();
}
}
const summarySheet = spreadsheet.getSheetByName(SUMMARY_SHEET_NAME);
if (summarySheet) {
summarySheet.getProtections(SpreadsheetApp.ProtectionType.SHEET).forEach(function (protection) {
if (protection.getDescription() === PROTECTION_DESCRIPTION) {
protection.remove();
}
});
summarySheet.getPivotTables().forEach(function (pivot) {
pivot.remove();
});
summarySheet.getRange('A1:D20').clearContent().clearFormat();
}
Logger.log(
'Removed ' +
NAMED_RANGE_NAME +
', cleared the ' +
REQUESTS_SHEET_NAME +
' filter, and cleaned up ' +
SUMMARY_SHEET_NAME +
'.'
);
}
/**
* @returns {SpreadsheetApp.Spreadsheet}
*/
function getBoundSpreadsheet_() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
if (!spreadsheet) {
throw new Error('Open this project from its bound Google Sheet with Extensions > Apps Script.');
}
return spreadsheet;
}
/**
* @param {SpreadsheetApp.Spreadsheet} spreadsheet
* @returns {{
* sheet: SpreadsheetApp.Sheet,
* range: SpreadsheetApp.Range,
* columns: RequestColumnMap,
* createdNamedRange: boolean
* }}
*/
function resolveOrCreateRequestTable_(spreadsheet) {
const existingRange = spreadsheet.getRangeByName(NAMED_RANGE_NAME);
if (existingRange) {
const sheet = existingRange.getSheet();
if (sheet.getName() !== REQUESTS_SHEET_NAME) {
throw new Error(
NAMED_RANGE_NAME +
' points to "' +
sheet.getName() +
'" instead of "' +
REQUESTS_SHEET_NAME +
'".'
);
}
if (existingRange.getNumRows() < 2 || existingRange.getNumRows() > MAX_TABLE_ROWS) {
throw new Error(
NAMED_RANGE_NAME + ' must span between 2 and ' + MAX_TABLE_ROWS + ' rows.'
);
}
const headerRow = existingRange.getValues()[0];
const columns = mapHeaderColumns_(headerRow, existingRange.getColumn());
return {
sheet: sheet,
range: existingRange,
columns: columns,
createdNamedRange: false
};
}
const requestsSheet = spreadsheet.getSheetByName(REQUESTS_SHEET_NAME);
if (!requestsSheet) {
throw new Error(
REQUESTS_SHEET_NAME +
' sheet is missing. Rename the first tab Requests and import sample.csv.'
);
}
const lastRow = requestsSheet.getLastRow();
const lastColumn = requestsSheet.getLastColumn();
if (lastRow < 2 || lastColumn < REQUIRED_HEADERS.length) {
throw new Error(
REQUESTS_SHEET_NAME + ' needs a header row and at least one data row across five columns.'
);
}
if (lastRow > MAX_TABLE_ROWS) {
throw new Error(
REQUESTS_SHEET_NAME + ' has more than ' + MAX_TABLE_ROWS + ' rows. Keep practice data small.'
);
}
const initialRange = requestsSheet.getRange(1, 1, lastRow, lastColumn);
const headerRow = initialRange.getValues()[0];
const columns = mapHeaderColumns_(headerRow, initialRange.getColumn());
spreadsheet.setNamedRange(NAMED_RANGE_NAME, initialRange);
return {
sheet: requestsSheet,
range: initialRange,
columns: columns,
createdNamedRange: true
};
}
/**
* Resolves 0-based array offsets inside the named range and 1-based sheet column
* positions required by Filter and PivotTable methods.
*
* @param {unknown[]} headerRow
* @param {number} rangeStartColumn
* @returns {RequestColumnMap}
*/
function mapHeaderColumns_(headerRow, rangeStartColumn) {
const labels = headerRow.map(function (cell) {
return String(cell).trim();
});
REQUIRED_HEADERS.forEach(function (required) {
if (labels.indexOf(required) === -1) {
throw new Error(
'Missing required header "' +
required +
'" in ' +
NAMED_RANGE_NAME +
'. Found: ' +
labels.join(', ')
);
}
});
const requestIdIndex = labels.indexOf('Request ID');
const departmentIndex = labels.indexOf('Department');
const priorityIndex = labels.indexOf('Priority');
const hoursIndex = labels.indexOf('Hours');
const statusIndex = labels.indexOf('Status');
return {
requestIdIndex: requestIdIndex,
departmentIndex: departmentIndex,
priorityIndex: priorityIndex,
hoursIndex: hoursIndex,
statusIndex: statusIndex,
requestIdSheetColumn: rangeStartColumn + requestIdIndex,
departmentSheetColumn: rangeStartColumn + departmentIndex,
prioritySheetColumn: rangeStartColumn + priorityIndex,
hoursSheetColumn: rangeStartColumn + hoursIndex,
statusSheetColumn: rangeStartColumn + statusIndex
};
}
/**
* Validates every data row in RequestTable before mutating sheet structure.
*
* @param {SpreadsheetApp.Range} tableRange
* @param {RequestColumnMap} columns
* @returns {void}
*/
function checkTableValues_(tableRange, columns) {
const values = tableRange.getValues();
const startRow = tableRange.getRow();
for (let offset = 1; offset < values.length; offset += 1) {
const row = values[offset];
const sheetRow = startRow + offset;
const requestId = String(row[columns.requestIdIndex]).trim();
const department = String(row[columns.departmentIndex]).trim();
const hours = row[columns.hoursIndex];
if (requestId === '' || department === '') {
throw new Error('Sheet row ' + sheetRow + ' must have a Request ID and Department.');
}
if (typeof hours !== 'number' || !Number.isFinite(hours) || hours < 0) {
throw new Error('Sheet row ' + sheetRow + ' Hours must be a non-negative number.');
}
}
}
/**
* Compares all rows returned by Range.getValues() against rows that remain visible
* according to Sheet.isRowHiddenByFilter(rowNumber).
*
* @param {SpreadsheetApp.Sheet} sheet
* @param {SpreadsheetApp.Range} tableRange
* @param {RequestColumnMap} columns
* @returns {{
* totalRows: number,
* totalHours: number,
* visibleRows: number,
* visibleHours: number,
* visibleIds: string[]
* }}
*/
function summarizeVisibleVsAllRows_(sheet, tableRange, columns) {
const values = tableRange.getValues();
const startRow = tableRange.getRow();
let totalRows = 0;
let totalHours = 0;
let visibleRows = 0;
let visibleHours = 0;
/** @type {string[]} */
const visibleIds = [];
for (let offset = 1; offset < values.length; offset += 1) {
const row = values[offset];
const sheetRow = startRow + offset;
const hours = Number(row[columns.hoursIndex]);
totalRows += 1;
totalHours += hours;
if (!sheet.isRowHiddenByFilter(sheetRow)) {
visibleRows += 1;
visibleHours += hours;
visibleIds.push(String(row[columns.requestIdIndex]));
}
}
return {
totalRows: totalRows,
totalHours: totalHours,
visibleRows: visibleRows,
visibleHours: visibleHours,
visibleIds: visibleIds
};
}
/**
* Creates or refreshes the Summary tab with one Department pivot table summing
* Hours and counting Request IDs.
*
* @param {SpreadsheetApp.Spreadsheet} spreadsheet
* @param {SpreadsheetApp.Range} tableRange
* @param {RequestColumnMap} columns
* @returns {SpreadsheetApp.Sheet}
*/
function rebuildSummaryPivotTab_(spreadsheet, tableRange, columns) {
const summarySheet =
spreadsheet.getSheetByName(SUMMARY_SHEET_NAME) || spreadsheet.insertSheet(SUMMARY_SHEET_NAME);
summarySheet.getPivotTables().forEach(function (pivot) {
pivot.remove();
});
summarySheet.getRange('A1:D20').clearContent().clearFormat();
const pivotTable = summarySheet.getRange('A1').createPivotTable(tableRange);
const departmentGroup = pivotTable.addRowGroup(columns.departmentSheetColumn);
departmentGroup.sortAscending();
const totalHoursValue = pivotTable.addPivotValue(
columns.hoursSheetColumn,
SpreadsheetApp.PivotTableSummarizeFunction.SUM
);
totalHoursValue.setDisplayName('Total Hours');
const requestCountValue = pivotTable.addPivotValue(
columns.requestIdSheetColumn,
SpreadsheetApp.PivotTableSummarizeFunction.COUNTA
);
requestCountValue.setDisplayName('Request Count');
return summarySheet;
}
/**
* Adds a warning-only sheet protection to Summary once so reruns do not duplicate it.
*
* @param {SpreadsheetApp.Sheet} summarySheet
* @returns {boolean}
*/
function protectSummarySheetWithWarning_(summarySheet) {
const existing = summarySheet
.getProtections(SpreadsheetApp.ProtectionType.SHEET)
.filter(function (protection) {
return protection.getDescription() === PROTECTION_DESCRIPTION;
});
if (existing.length > 0) {
return false;
}
summarySheet.protect().setDescription(PROTECTION_DESCRIPTION).setWarningOnly(true);
return true;
}
{
"timeZone": "Etc/UTC",
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets.currentonly"
]
}
The manifest requests https://www.googleapis.com/auth/spreadsheets.currentonly, limiting the script to the bound workbook. Select buildRequestSummaryView in the toolbar and click Run, approving access when prompted.
After buildRequestSummaryView finishes, verify four outcomes:
Execution log:
SourceRegistered RequestTable at Requests!A1:E7. getValues() returned 6 data rows (28 hours); filter-visible rows: 4 (23 hours: REQ-201, REQ-202, REQ-204, REQ-206). Rebuilt Department pivot table on Summary!A1. Added warning-only sheet protection on Summary.Requeststab: green filter funnels appear on row 1, and rows 4 (REQ-203) and 6 (REQ-205), whose status isClosed, are hidden.Summarytab: a newSummarytab contains a pivot table anchored atA1listingFacilities(6Total Hours,2Request Count),Finance(13Total Hours,2Request Count),IT(9Total Hours,2Request Count), andGrand Total(28Total Hours,6Request Count).Warning banner: click any cell on
Summaryand type a character. Google Sheets displays a Heads up! confirmation dialog before allowing the edit. Click Cancel so the pivot table stays untouched.
Anchor moving tables with NamedRange and header checks
A named range associates a workbook-unique identifier such as RequestTable with a Range object (Spreadsheet.setNamedRange and NamedRange reference). Once registered, Google Sheets updates the stored coordinates whenever someone inserts or deletes rows or columns inside or before that range, or renames the tab:
Insert a new column A to the left of
Requests!A1:E7, andRequestTableautomatically shifts toRequests!B1:F7.Insert a new row between row 3 and row 4, and
RequestTableexpands to seven data rows (B1:F8).Rename the tab in the UI, and
tableRange.getSheet()still returns thatSheetobject.resolveOrCreateRequestTable_checkssheet.getName() === REQUESTS_SHEET_NAMEso an unexpected tab rename stops the script before it modifies another tab.
Handle deleted named ranges and shifted columns
spreadsheet.getRangeByName(name) returns SpreadsheetApp.Range | null. It returns null in two situations:
No named range with that exact name has been created yet.
Someone deleted every row or every column covered by
RequestTable(leaving#REF!in Data > Named ranges), or removed the named range in the sidebar.
Always check if (!tableRange) before calling .getValues() or .getColumn() on the result (see also null reading getRange). inspectVisibleRequests checks for a missing or deleted named range before reading values:
const tableRange = spreadsheet.getRangeByName(NAMED_RANGE_NAME);
if (!tableRange) {
throw new Error(
NAMED_RANGE_NAME + ' named range is missing or was deleted. Run buildRequestSummaryView first.'
);
} Even when RequestTable resolves to a valid Range, someone can reorder columns inside RequestTable, insert an extra column between Department and Priority, or overwrite a header label. mapHeaderColumns_ reads row 1 of tableRange and computes two column numbers for every field:
Property in RequestColumnMap | Base and origin | Initial value (A1:E7) | After inserting a new column A (B1:F7) | Where Apps Script expects it |
|---|---|---|---|---|
statusIndex | 0-based offset inside tableRange | 4 | 4 | Indexing row[columns.statusIndex] from tableRange.getValues() |
statusSheetColumn | 1-based column on the sheet (rangeStartColumn + statusIndex) | 5 (column E) | 6 (column F) | Filter.setColumnFilterCriteria(...) and PivotTable.addRowGroup(...) or addPivotValue(...) |
Keeping those two coordinate systems explicit prevents a subtle off-by-one bug when RequestTable no longer starts in column A: tableRange.getValues() always starts at index 0 for the leftmost column of tableRange, whereas Filter and PivotTable methods always take the 1-based column position on the underlying sheet (tableRange.getColumn() + index).
Google Sheets expands a NamedRange automatically when someone inserts rows between its first and last row, or inserts columns between its first and last column. Appending a new row immediately below the last row of RequestTable (for example typing into row 8 when RequestTable ends at row 7, or calling sheet.appendRow(...)) does not expand RequestTable. When your workflow appends rows at the bottom of a tab, update the named range explicitly with spreadsheet.setNamedRange('RequestTable', requestsSheet.getRange(1, 1, requestsSheet.getLastRow(), lastCol)) or namedRange.setRange(...) before rebuilding downstream filters or pivot tables.
Inspect or rename NamedRange objects directly
While spreadsheet.getRangeByName('RequestTable') jumps straight to the target Range, calling spreadsheet.getNamedRanges() or sheet.getNamedRanges() returns the NamedRange wrapper objects themselves. Reach for NamedRange instances when your script needs to:
List every named range in a workbook or on one tab (
namedRange.getName()andnamedRange.getRange().getA1Notation()).Rename an existing definition in place with
namedRange.setName(newName)so formulas referencing that named range update automatically.Resize a named range with
namedRange.setRange(newRange)or delete it withnamedRange.remove().
Named range identifiers must begin with a letter or _, contain only letters, digits, _, or periods, must not look like an A1 cell reference such as R1C1 or Q1, and cannot exceed 250 characters.
Filter rows on a sheet and tell visible rows from hidden ones
A basic sheet filter attaches dropdown funnels to a table's header row so rows can be sorted or hidden by column criteria (Filter reference and FilterCriteriaBuilder reference).
Google Sheets permits at most one basic Filter per sheet. Calling tableRange.createFilter() on a tab that already has a filter throws an exception (Exception: You can't create a filter in a sheet that already has a filter.). buildRequestSummaryView checks requestsSheet.getFilter() and removes any prior filter before creating a fresh one covering tableRange:
const existingFilter = requestsSheet.getFilter();
if (existingFilter) {
existingFilter.remove();
}
const filter = tableRange.createFilter();
const activeCriteria = SpreadsheetApp.newFilterCriteria()
.setHiddenValues([HIDDEN_STATUS])
.build();
filter.setColumnFilterCriteria(columns.statusSheetColumn, activeCriteria);
SpreadsheetApp.flush(); SpreadsheetApp.newFilterCriteria() supports two complementary families of rules:
Value exclusion:
setHiddenValues(['Closed', 'Cancelled'])hides exact string matches while keeping every other existing or future status visible. (Avoid callingsetVisibleValues(...)on a sheet filter: in Google'sFilterCriteriaBuilderreference,setVisibleValuesis supported for pivot table filters rather than basic sheet filters.)Boolean criteria: methods such as
whenTextEqualTo('Open'),whenNumberGreaterThan(4),whenDateBefore(cutoffDate),whenCellNotEmpty(), andwhenFormulaSatisfied('=...')filter by condition rather than enumerating hidden strings.
A Filter can also sort its range without scrambling the header row: calling filter.sort(columns.hoursSheetColumn, false) sorts every data row below row 1 of the filtered range in descending order of Hours, whereas calling Range.sort(...) on a range that accidentally includes row 1 sorts the header text right into the data rows.
Basic sheet filters have two platform behaviors that surprise developers who automate shared spreadsheets:
A basic
Filterchanges the live view for every collaborator on that tab. Unlike personal Filter views (Data > Filter views in the Sheets menu, covered in sort and filter help), a sheet's basicFilteris stored on theSheetitself. HidingClosedrows in code hides them immediately on every open browser tab viewingRequests, and built-inSpreadsheetApphas no class for personal Filter Views (creating personal Filter Views requires the Sheets v4 REST API or Advanced Sheets ServiceAddFilterViewRequest).Range.getValues()returns every row in the range, including rows hidden by a filter or hidden manually. InbuildRequestSummaryView,tableRange.getValues()returns all 6 request rows (28hours), even right after the filter hidesREQ-203andREQ-205. If a script emails, exports, or totals rows fromgetValues()assuming the sheet filter already excluded hidden rows, it will include the hidden rows too.
When to use isRowHiddenByFilter versus filtering in JavaScript
When a person has applied ad-hoc filter funnels in the UI and runs a menu command expecting your script to act only on the rows currently visible on screen, pair a single tableRange.getValues() read with sheet.isRowHiddenByFilter(rowPosition) (and sheet.isRowHiddenByUser(rowPosition) if someone also right-clicked rows and chose Hide row):
If the same script execution created or updated the filter earlier in the run, call
SpreadsheetApp.flush()first so Google Sheets evaluates the filter beforeisRowHiddenByFilterruns.Pass the
1-based sheet row number (startRow + offset), not the0-based array index.Bound the table size (
MAX_TABLE_ROWS = 100inCode.gs).sheet.isRowHiddenByFilter(sheetRow)makes one service call per inspected row, which stays fast on 100 rows and risks timing out across thousands.
When your script owns the business rule itself (for example, "sum hours where Status !== 'Closed'"), filter the 2D array from getValues() directly in JavaScript (rows.filter((row) => row[columns.statusIndex] !== 'Closed')). Filtering in memory requires zero per-row service calls, scales to thousands of rows, and produces the same answer even if a collaborator changes or clears the UI filter mid-run.
Build a summary tab with PivotTable
A pivot table aggregates rows from a source Range without writing SUMIFS or COUNTIFS formulas by hand (PivotTable reference). Anchor a new pivot table on a dedicated tab by calling Range.createPivotTable(sourceData) on the top-left cell where the rendered table should start:
const pivotTable = summarySheet.getRange('A1').createPivotTable(tableRange);
const departmentGroup = pivotTable.addRowGroup(columns.departmentSheetColumn);
departmentGroup.sortAscending();
const totalHoursValue = pivotTable.addPivotValue(
columns.hoursSheetColumn,
SpreadsheetApp.PivotTableSummarizeFunction.SUM
);
totalHoursValue.setDisplayName('Total Hours');
const requestCountValue = pivotTable.addPivotValue(
columns.requestIdSheetColumn,
SpreadsheetApp.PivotTableSummarizeFunction.COUNTA
);
requestCountValue.setDisplayName('Request Count'); Examine how each piece works:
Idempotent rebuild without changing the sheet ID.
rebuildSummaryPivotTab_reusesSummarywhen it already exists (getSheetByName('Summary') || insertSheet('Summary')), removes earlierPivotTableobjects on that tab withpivot.remove(), and clearsA1:D20. Keeping the sameSheetobject preservessummarySheet.getSheetId()(gid), which matters whenever charts, links, or PDF export scripts referenceSummaryby tab ID instead of deleting and recreating the sheet.Row and column groups.
addRowGroup(sourceDataColumn)andaddColumnGroup(sourceDataColumn)return aPivotGroup. Calling.sortAscending()(which takes zero arguments) orders departments alphabetically (Facilities,Finance,IT), and.showTotals(false)can hide theGrand Totalrow when you do not want one.Summarized values.
addPivotValue(sourceDataColumn, summarizeFunction)takes aSpreadsheetApp.PivotTableSummarizeFunctionconstant and returns aPivotValue. UseSUMorAVERAGEfor numeric columns (Hours),COUNTAto count non-empty text IDs (Request ID), andCOUNTUNIQUEto count distinct values..setDisplayName(...)replaces the defaultSUM of Hoursheader withTotal Hours.
Sheet filters do not filter a PivotTable automatically
Compare the Summary pivot table (Grand Total: 28 Total Hours, 6 Request Count) with the filtered Requests tab (4 visible rows, 23 hours). Even though Requests hides Closed rows, PivotTable reads the full sourceData range (RequestTable) and includes REQ-203 and REQ-205 unless you attach a pivot filter directly to the PivotTable:
const activePivotCriteria = SpreadsheetApp.newFilterCriteria()
.whenTextDoesNotEqualTo('Closed')
.build();
pivotTable.addFilter(columns.statusSheetColumn, activePivotCriteria); Keeping sheet filters and pivot filters independent is intentional in Google Sheets: someone exploring Requests with temporary filter funnels does not silently alter financial or operational totals on Summary. When Summary should exclude Closed requests permanently, call pivotTable.addFilter(...) in rebuildSummaryPivotTab_.
Protect generated summaries without locking out yourself by mistake
Because buildRequestSummaryView overwrites Summary!A1:D20 on every run, any manual formula or note someone types into Summary will be erased on the next rebuild. Calling summarySheet.protect() (or range.protect() for a specific block of cells) creates a Protection object that signals Summary is script-managed.
Google Sheets supports two distinct modes on a Protection:
| Mode | How to configure it in Apps Script | Who can edit the cells |
|---|---|---|
| Warning-only protection | protection.setDescription(...).setWarningOnly(true) | Anyone with edit access to the spreadsheet can still edit after confirming a Heads up! dialog, including the spreadsheet owner. |
| Editor-list restriction | const p = sheet.protect(); p.removeEditors(p.getEditors()); if (p.canDomainEdit()) p.setDomainEdit(false); | Only the spreadsheet owner (and any editors kept or added with addEditor) can edit. All other collaborators are blocked without a bypass button. |
To protect an entire tab except one small input range (such as a parameter cell in Summary!F2), protect the sheet and pass the editable cells to protection.setUnprotectedRanges([summarySheet.getRange('F2')]).
Google's Protection reference enforces a hard ownership rule: neither a script nor a collaborator can remove the spreadsheet owner from a protection's editor list, and in a shared drive, organizers with full file control retain equivalent access. If you write p.removeEditors(p.getEditors()) in a personal practice sheet that you own and then type into Summary!A1 to test whether the lock works, Sheets lets you edit the pivot table without showing a warning. That often misleads authors into thinking protect() failed. During solo testing in a sheet you own, either use setWarningOnly(true) (which displays the confirmation banner even to the owner) or open the spreadsheet from a second test account with Editor access to verify hard editor-list blocking.
protectSummarySheetWithWarning_ also queries summarySheet.getProtections(SpreadsheetApp.ProtectionType.SHEET) for an existing protection whose getDescription() equals 'Request tracker summary' before calling summarySheet.protect(). Calling summarySheet.protect() unconditionally on a rerun replaces or resets existing sheet protection settings, whereas checking the description leaves an already configured protection intact.
Test column shifts and clean up
Run two verification tests in your practice spreadsheet before cleaning up:
On the
Requeststab, right-click column A and choose Insert 1 column left. Open Data > Named ranges and confirm thatRequestTablenow readsRequests!B1:F7.Return to the Apps Script editor and run
inspectVisibleRequests:SourceRequestTable resolves to Requests!B1:F7 (Status at sheet column 6). All rows in getValues(): 6 (28 hours). Visible rows: 4 (23 hours: REQ-201, REQ-202, REQ-204, REQ-206).Run
buildRequestSummaryViewagain. Without editingCode.gs, the script reusesRequests!B1:F7, filtersStatusat column F (6), and rebuilds theSummarypivot table from columns B through F.Rename
Requests!F1fromHourstoEstimateand runinspectVisibleRequests. Confirm it stops withMissing required header "Hours" in RequestTable, then restore the header toHours.Run
removePracticeStructureAndProtection. Confirm the filter funnels disappear fromRequests,RequestTableis removed from Data > Named ranges, andSummaryhas no pivot table or protection.
Common failures
Exception: You can't create a filter in a sheet that already has a filter.Another filter already exists on that tab (perhaps covering a different range). Callconst prior = sheet.getFilter(); if (prior) prior.remove();beforerange.createFilter().TypeError: Cannot read properties of null (reading 'getValues')aftergetRangeByName. The named range was misspelled, never created, or invalidated (#REF!) when someone deleted all of its rows or columns. Guardif (!tableRange)before using the returned value.Pivot table or filter targets the wrong column after moving
RequestTable. Check whether the code passed a0-based array index (headerRow.indexOf('Status')) or1 + indexinstead oftableRange.getColumn() + indextosetColumnFilterCriteria,addRowGroup, oraddPivotValue.isRowHiddenByFilterreturns stalefalsevalues right aftersetColumnFilterCriteria. CallSpreadsheetApp.flush()between changing filter criteria and readingsheet.isRowHiddenByFilter(rowNumber)in the same execution.Pivot table overlaps non-empty cells and fails to render. A
PivotTableexpands downward and rightward as new departments appear. Dedicate a separateSummarytab (or clear its target block first) rather than anchoring a pivot table directly above manual notes.
Exercise: filter the Summary pivot table to active requests
Update rebuildSummaryPivotTab_ so the Summary pivot table excludes Closed rows to match the Requests filter, then verify the totals:
Inside
rebuildSummaryPivotTab_, build a pivot filter criterion withSpreadsheetApp.newFilterCriteria().whenTextDoesNotEqualTo(HIDDEN_STATUS).build().Attach it to
pivotTablewithpivotTable.addFilter(columns.statusSheetColumn, criteria).Run
buildRequestSummaryViewand checkSummary!A1.Confirm
Facilitiesdrops to4hours (1request),ITdrops to6hours (1request),Financestays at13hours (2requests), andGrand Totalshows23hours (4requests).Run
removePracticeStructureAndProtectionwhen finished, and move the disposable spreadsheet to Trash if you no longer need it.
Next, read Fetch data from web APIs to pull external JSON responses into a spreadsheet.
Project files
Complete source files for this chapter's examples.
Manage named ranges, filters, pivot tables, and summary protection
Code.gs
Download fileconst REQUESTS_SHEET_NAME = 'Requests';
const SUMMARY_SHEET_NAME = 'Summary';
const NAMED_RANGE_NAME = 'RequestTable';
const PROTECTION_DESCRIPTION = 'Request tracker summary';
const HIDDEN_STATUS = 'Closed';
const MAX_TABLE_ROWS = 100;
const REQUIRED_HEADERS = ['Request ID', 'Department', 'Priority', 'Hours', 'Status'];
/**
* @typedef {{
* requestIdIndex: number,
* departmentIndex: number,
* priorityIndex: number,
* hoursIndex: number,
* statusIndex: number,
* requestIdSheetColumn: number,
* departmentSheetColumn: number,
* prioritySheetColumn: number,
* hoursSheetColumn: number,
* statusSheetColumn: number
* }} RequestColumnMap
*/
/**
* Registers or resolves the RequestTable named range, filters out Closed rows on
* Requests, logs all rows versus filter-visible rows, rebuilds the Summary pivot
* table, and applies a warning-only protection on the Summary tab.
*
* @returns {void}
*/
function buildRequestSummaryView() {
const spreadsheet = getBoundSpreadsheet_();
const resolved = resolveOrCreateRequestTable_(spreadsheet);
const requestsSheet = resolved.sheet;
const tableRange = resolved.range;
const columns = resolved.columns;
checkTableValues_(tableRange, columns);
const existingFilter = requestsSheet.getFilter();
if (existingFilter) {
existingFilter.remove();
}
const filter = tableRange.createFilter();
const activeCriteria = SpreadsheetApp.newFilterCriteria()
.setHiddenValues([HIDDEN_STATUS])
.build();
filter.setColumnFilterCriteria(columns.statusSheetColumn, activeCriteria);
// Flush pending filter changes before querying per-row filter visibility.
SpreadsheetApp.flush();
const counts = summarizeVisibleVsAllRows_(requestsSheet, tableRange, columns);
const summarySheet = rebuildSummaryPivotTab_(spreadsheet, tableRange, columns);
const protectionCreated = protectSummarySheetWithWarning_(summarySheet);
Logger.log(
(resolved.createdNamedRange ? 'Registered ' : 'Reused ') +
NAMED_RANGE_NAME +
' at ' +
requestsSheet.getName() +
'!' +
tableRange.getA1Notation() +
'.'
);
Logger.log(
'getValues() returned ' +
counts.totalRows +
' data rows (' +
counts.totalHours +
' hours); filter-visible rows: ' +
counts.visibleRows +
' (' +
counts.visibleHours +
' hours: ' +
counts.visibleIds.join(', ') +
').'
);
Logger.log('Rebuilt Department pivot table on ' + SUMMARY_SHEET_NAME + '!A1.');
Logger.log(
protectionCreated
? 'Added warning-only sheet protection on ' + SUMMARY_SHEET_NAME + '.'
: 'Warning-only sheet protection already present on ' + SUMMARY_SHEET_NAME + '.'
);
}
/**
* Read-only check that resolves RequestTable and compares all rows returned by
* getValues() against rows currently visible through the sheet filter.
*
* @returns {void}
*/
function inspectVisibleRequests() {
const spreadsheet = getBoundSpreadsheet_();
const tableRange = spreadsheet.getRangeByName(NAMED_RANGE_NAME);
if (!tableRange) {
throw new Error(
NAMED_RANGE_NAME +
' named range is missing or was deleted. Run buildRequestSummaryView first.'
);
}
const sheet = tableRange.getSheet();
if (sheet.getName() !== REQUESTS_SHEET_NAME) {
throw new Error(NAMED_RANGE_NAME + ' must point to the ' + REQUESTS_SHEET_NAME + ' tab.');
}
if (tableRange.getNumRows() < 2 || tableRange.getNumRows() > MAX_TABLE_ROWS) {
throw new Error(NAMED_RANGE_NAME + ' must have between 2 and ' + MAX_TABLE_ROWS + ' rows.');
}
const headerRow = tableRange.getValues()[0];
const columns = mapHeaderColumns_(headerRow, tableRange.getColumn());
const counts = summarizeVisibleVsAllRows_(sheet, tableRange, columns);
Logger.log(
NAMED_RANGE_NAME +
' resolves to ' +
sheet.getName() +
'!' +
tableRange.getA1Notation() +
' (Status at sheet column ' +
columns.statusSheetColumn +
').'
);
Logger.log(
'All rows in getValues(): ' +
counts.totalRows +
' (' +
counts.totalHours +
' hours). Visible rows: ' +
counts.visibleRows +
' (' +
counts.visibleHours +
' hours: ' +
counts.visibleIds.join(', ') +
').'
);
}
/**
* Removes this script's named range, Requests sheet filter, Summary pivot table,
* and Summary sheet protection without deleting Request rows.
*
* @returns {void}
*/
function removePracticeStructureAndProtection() {
const spreadsheet = getBoundSpreadsheet_();
if (spreadsheet.getRangeByName(NAMED_RANGE_NAME)) {
spreadsheet.removeNamedRange(NAMED_RANGE_NAME);
}
const requestsSheet = spreadsheet.getSheetByName(REQUESTS_SHEET_NAME);
if (requestsSheet) {
const filter = requestsSheet.getFilter();
if (filter) {
filter.remove();
}
}
const summarySheet = spreadsheet.getSheetByName(SUMMARY_SHEET_NAME);
if (summarySheet) {
summarySheet.getProtections(SpreadsheetApp.ProtectionType.SHEET).forEach(function (protection) {
if (protection.getDescription() === PROTECTION_DESCRIPTION) {
protection.remove();
}
});
summarySheet.getPivotTables().forEach(function (pivot) {
pivot.remove();
});
summarySheet.getRange('A1:D20').clearContent().clearFormat();
}
Logger.log(
'Removed ' +
NAMED_RANGE_NAME +
', cleared the ' +
REQUESTS_SHEET_NAME +
' filter, and cleaned up ' +
SUMMARY_SHEET_NAME +
'.'
);
}
/**
* @returns {SpreadsheetApp.Spreadsheet}
*/
function getBoundSpreadsheet_() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
if (!spreadsheet) {
throw new Error('Open this project from its bound Google Sheet with Extensions > Apps Script.');
}
return spreadsheet;
}
/**
* @param {SpreadsheetApp.Spreadsheet} spreadsheet
* @returns {{
* sheet: SpreadsheetApp.Sheet,
* range: SpreadsheetApp.Range,
* columns: RequestColumnMap,
* createdNamedRange: boolean
* }}
*/
function resolveOrCreateRequestTable_(spreadsheet) {
const existingRange = spreadsheet.getRangeByName(NAMED_RANGE_NAME);
if (existingRange) {
const sheet = existingRange.getSheet();
if (sheet.getName() !== REQUESTS_SHEET_NAME) {
throw new Error(
NAMED_RANGE_NAME +
' points to "' +
sheet.getName() +
'" instead of "' +
REQUESTS_SHEET_NAME +
'".'
);
}
if (existingRange.getNumRows() < 2 || existingRange.getNumRows() > MAX_TABLE_ROWS) {
throw new Error(
NAMED_RANGE_NAME + ' must span between 2 and ' + MAX_TABLE_ROWS + ' rows.'
);
}
const headerRow = existingRange.getValues()[0];
const columns = mapHeaderColumns_(headerRow, existingRange.getColumn());
return {
sheet: sheet,
range: existingRange,
columns: columns,
createdNamedRange: false
};
}
const requestsSheet = spreadsheet.getSheetByName(REQUESTS_SHEET_NAME);
if (!requestsSheet) {
throw new Error(
REQUESTS_SHEET_NAME +
' sheet is missing. Rename the first tab Requests and import sample.csv.'
);
}
const lastRow = requestsSheet.getLastRow();
const lastColumn = requestsSheet.getLastColumn();
if (lastRow < 2 || lastColumn < REQUIRED_HEADERS.length) {
throw new Error(
REQUESTS_SHEET_NAME + ' needs a header row and at least one data row across five columns.'
);
}
if (lastRow > MAX_TABLE_ROWS) {
throw new Error(
REQUESTS_SHEET_NAME + ' has more than ' + MAX_TABLE_ROWS + ' rows. Keep practice data small.'
);
}
const initialRange = requestsSheet.getRange(1, 1, lastRow, lastColumn);
const headerRow = initialRange.getValues()[0];
const columns = mapHeaderColumns_(headerRow, initialRange.getColumn());
spreadsheet.setNamedRange(NAMED_RANGE_NAME, initialRange);
return {
sheet: requestsSheet,
range: initialRange,
columns: columns,
createdNamedRange: true
};
}
/**
* Resolves 0-based array offsets inside the named range and 1-based sheet column
* positions required by Filter and PivotTable methods.
*
* @param {unknown[]} headerRow
* @param {number} rangeStartColumn
* @returns {RequestColumnMap}
*/
function mapHeaderColumns_(headerRow, rangeStartColumn) {
const labels = headerRow.map(function (cell) {
return String(cell).trim();
});
REQUIRED_HEADERS.forEach(function (required) {
if (labels.indexOf(required) === -1) {
throw new Error(
'Missing required header "' +
required +
'" in ' +
NAMED_RANGE_NAME +
'. Found: ' +
labels.join(', ')
);
}
});
const requestIdIndex = labels.indexOf('Request ID');
const departmentIndex = labels.indexOf('Department');
const priorityIndex = labels.indexOf('Priority');
const hoursIndex = labels.indexOf('Hours');
const statusIndex = labels.indexOf('Status');
return {
requestIdIndex: requestIdIndex,
departmentIndex: departmentIndex,
priorityIndex: priorityIndex,
hoursIndex: hoursIndex,
statusIndex: statusIndex,
requestIdSheetColumn: rangeStartColumn + requestIdIndex,
departmentSheetColumn: rangeStartColumn + departmentIndex,
prioritySheetColumn: rangeStartColumn + priorityIndex,
hoursSheetColumn: rangeStartColumn + hoursIndex,
statusSheetColumn: rangeStartColumn + statusIndex
};
}
/**
* Validates every data row in RequestTable before mutating sheet structure.
*
* @param {SpreadsheetApp.Range} tableRange
* @param {RequestColumnMap} columns
* @returns {void}
*/
function checkTableValues_(tableRange, columns) {
const values = tableRange.getValues();
const startRow = tableRange.getRow();
for (let offset = 1; offset < values.length; offset += 1) {
const row = values[offset];
const sheetRow = startRow + offset;
const requestId = String(row[columns.requestIdIndex]).trim();
const department = String(row[columns.departmentIndex]).trim();
const hours = row[columns.hoursIndex];
if (requestId === '' || department === '') {
throw new Error('Sheet row ' + sheetRow + ' must have a Request ID and Department.');
}
if (typeof hours !== 'number' || !Number.isFinite(hours) || hours < 0) {
throw new Error('Sheet row ' + sheetRow + ' Hours must be a non-negative number.');
}
}
}
/**
* Compares all rows returned by Range.getValues() against rows that remain visible
* according to Sheet.isRowHiddenByFilter(rowNumber).
*
* @param {SpreadsheetApp.Sheet} sheet
* @param {SpreadsheetApp.Range} tableRange
* @param {RequestColumnMap} columns
* @returns {{
* totalRows: number,
* totalHours: number,
* visibleRows: number,
* visibleHours: number,
* visibleIds: string[]
* }}
*/
function summarizeVisibleVsAllRows_(sheet, tableRange, columns) {
const values = tableRange.getValues();
const startRow = tableRange.getRow();
let totalRows = 0;
let totalHours = 0;
let visibleRows = 0;
let visibleHours = 0;
/** @type {string[]} */
const visibleIds = [];
for (let offset = 1; offset < values.length; offset += 1) {
const row = values[offset];
const sheetRow = startRow + offset;
const hours = Number(row[columns.hoursIndex]);
totalRows += 1;
totalHours += hours;
if (!sheet.isRowHiddenByFilter(sheetRow)) {
visibleRows += 1;
visibleHours += hours;
visibleIds.push(String(row[columns.requestIdIndex]));
}
}
return {
totalRows: totalRows,
totalHours: totalHours,
visibleRows: visibleRows,
visibleHours: visibleHours,
visibleIds: visibleIds
};
}
/**
* Creates or refreshes the Summary tab with one Department pivot table summing
* Hours and counting Request IDs.
*
* @param {SpreadsheetApp.Spreadsheet} spreadsheet
* @param {SpreadsheetApp.Range} tableRange
* @param {RequestColumnMap} columns
* @returns {SpreadsheetApp.Sheet}
*/
function rebuildSummaryPivotTab_(spreadsheet, tableRange, columns) {
const summarySheet =
spreadsheet.getSheetByName(SUMMARY_SHEET_NAME) || spreadsheet.insertSheet(SUMMARY_SHEET_NAME);
summarySheet.getPivotTables().forEach(function (pivot) {
pivot.remove();
});
summarySheet.getRange('A1:D20').clearContent().clearFormat();
const pivotTable = summarySheet.getRange('A1').createPivotTable(tableRange);
const departmentGroup = pivotTable.addRowGroup(columns.departmentSheetColumn);
departmentGroup.sortAscending();
const totalHoursValue = pivotTable.addPivotValue(
columns.hoursSheetColumn,
SpreadsheetApp.PivotTableSummarizeFunction.SUM
);
totalHoursValue.setDisplayName('Total Hours');
const requestCountValue = pivotTable.addPivotValue(
columns.requestIdSheetColumn,
SpreadsheetApp.PivotTableSummarizeFunction.COUNTA
);
requestCountValue.setDisplayName('Request Count');
return summarySheet;
}
/**
* Adds a warning-only sheet protection to Summary once so reruns do not duplicate it.
*
* @param {SpreadsheetApp.Sheet} summarySheet
* @returns {boolean}
*/
function protectSummarySheetWithWarning_(summarySheet) {
const existing = summarySheet
.getProtections(SpreadsheetApp.ProtectionType.SHEET)
.filter(function (protection) {
return protection.getDescription() === PROTECTION_DESCRIPTION;
});
if (existing.length > 0) {
return false;
}
summarySheet.protect().setDescription(PROTECTION_DESCRIPTION).setWarningOnly(true);
return true;
}
appsscript.json
Download file{
"timeZone": "Etc/UTC",
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets.currentonly"
]
}
sample.csv
Download fileRequest ID,Department,Priority,Hours,Status
REQ-201,Facilities,High,4,Open
REQ-202,IT,Medium,6,In progress
REQ-203,Facilities,Low,2,Closed
REQ-204,Finance,High,5,Open
REQ-205,IT,High,3,Closed
REQ-206,Finance,Medium,8,In progress