Skip to content
Skip to chapter

Chapter 13

Reliable automations

12 min read

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.

By the end

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.

Chapter navigation

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:

  1. Create a new Google Sheet and rename its first tab Requests.

  2. Copy sample.csv below into Requests!A1, select column A, click Data > Split text to columns, and choose Comma as the separator.

  3. Confirm that Requests!A1:E7 holds one header row and six fictional request rows (REQ-201 through REQ-206).

  4. Open Extensions > Apps Script and replace Code.gs with the example source below.

  5. In Project Settings, check Show "appsscript.json" manifest file in editor, replace appsscript.json with the included manifest, and save both files.

sample.csv
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

Download sample.csv
Code.gs
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;
}

Download Code.gs
appsscript.json
{
  "timeZone": "Etc/UTC",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]
}

Download appsscript.json

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:

    Source
    Registered 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.

  • Requests tab: green filter funnels appear on row 1, and rows 4 (REQ-203) and 6 (REQ-205), whose status is Closed, are hidden.

  • Summary tab: a new Summary tab contains a pivot table anchored at A1 listing Facilities (6 Total Hours, 2 Request Count), Finance (13 Total Hours, 2 Request Count), IT (9 Total Hours, 2 Request Count), and Grand Total (28 Total Hours, 6 Request Count).

  • Warning banner: click any cell on Summary and 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, and RequestTable automatically shifts to Requests!B1:F7.

  • Insert a new row between row 3 and row 4, and RequestTable expands to seven data rows (B1:F8).

  • Rename the tab in the UI, and tableRange.getSheet() still returns that Sheet object. resolveOrCreateRequestTable_ checks sheet.getName() === REQUESTS_SHEET_NAME so 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:

  1. No named range with that exact name has been created yet.

  2. 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:

Source
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 RequestColumnMapBase and originInitial value (A1:E7)After inserting a new column A (B1:F7)Where Apps Script expects it
statusIndex0-based offset inside tableRange44Indexing row[columns.statusIndex] from tableRange.getValues()
statusSheetColumn1-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).

tip

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() and namedRange.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 with namedRange.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:

Source
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 calling setVisibleValues(...) on a sheet filter: in Google's FilterCriteriaBuilder reference, setVisibleValues is supported for pivot table filters rather than basic sheet filters.)

  • Boolean criteria: methods such as whenTextEqualTo('Open'), whenNumberGreaterThan(4), whenDateBefore(cutoffDate), whenCellNotEmpty(), and whenFormulaSatisfied('=...') 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.

warning

Basic sheet filters have two platform behaviors that surprise developers who automate shared spreadsheets:

  • A basic Filter changes 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 basic Filter is stored on the Sheet itself. Hiding Closed rows in code hides them immediately on every open browser tab viewing Requests, and built-in SpreadsheetApp has no class for personal Filter Views (creating personal Filter Views requires the Sheets v4 REST API or Advanced Sheets Service AddFilterViewRequest).

  • Range.getValues() returns every row in the range, including rows hidden by a filter or hidden manually. In buildRequestSummaryView, tableRange.getValues() returns all 6 request rows (28 hours), even right after the filter hides REQ-203 and REQ-205. If a script emails, exports, or totals rows from getValues() 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):

  1. If the same script execution created or updated the filter earlier in the run, call SpreadsheetApp.flush() first so Google Sheets evaluates the filter before isRowHiddenByFilter runs.

  2. Pass the 1-based sheet row number (startRow + offset), not the 0-based array index.

  3. Bound the table size (MAX_TABLE_ROWS = 100 in Code.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:

Source
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_ reuses Summary when it already exists (getSheetByName('Summary') || insertSheet('Summary')), removes earlier PivotTable objects on that tab with pivot.remove(), and clears A1:D20. Keeping the same Sheet object preserves summarySheet.getSheetId() (gid), which matters whenever charts, links, or PDF export scripts reference Summary by tab ID instead of deleting and recreating the sheet.

  • Row and column groups. addRowGroup(sourceDataColumn) and addColumnGroup(sourceDataColumn) return a PivotGroup. Calling .sortAscending() (which takes zero arguments) orders departments alphabetically (Facilities, Finance, IT), and .showTotals(false) can hide the Grand Total row when you do not want one.

  • Summarized values. addPivotValue(sourceDataColumn, summarizeFunction) takes a SpreadsheetApp.PivotTableSummarizeFunction constant and returns a PivotValue. Use SUM or AVERAGE for numeric columns (Hours), COUNTA to count non-empty text IDs (Request ID), and COUNTUNIQUE to count distinct values. .setDisplayName(...) replaces the default SUM of Hours header with Total 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:

Source
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:

ModeHow to configure it in Apps ScriptWho can edit the cells
Warning-only protectionprotection.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 restrictionconst 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')]).

note

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:

  1. On the Requests tab, right-click column A and choose Insert 1 column left. Open Data > Named ranges and confirm that RequestTable now reads Requests!B1:F7.

  2. Return to the Apps Script editor and run inspectVisibleRequests:

    Source
    RequestTable 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).

  3. Run buildRequestSummaryView again. Without editing Code.gs, the script reuses Requests!B1:F7, filters Status at column F (6), and rebuilds the Summary pivot table from columns B through F.

  4. Rename Requests!F1 from Hours to Estimate and run inspectVisibleRequests. Confirm it stops with Missing required header "Hours" in RequestTable, then restore the header to Hours.

  5. Run removePracticeStructureAndProtection. Confirm the filter funnels disappear from Requests, RequestTable is removed from Data > Named ranges, and Summary has 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). Call const prior = sheet.getFilter(); if (prior) prior.remove(); before range.createFilter().

  • TypeError: Cannot read properties of null (reading 'getValues') after getRangeByName. The named range was misspelled, never created, or invalidated (#REF!) when someone deleted all of its rows or columns. Guard if (!tableRange) before using the returned value.

  • Pivot table or filter targets the wrong column after moving RequestTable. Check whether the code passed a 0-based array index (headerRow.indexOf('Status')) or 1 + index instead of tableRange.getColumn() + index to setColumnFilterCriteria, addRowGroup, or addPivotValue.

  • isRowHiddenByFilter returns stale false values right after setColumnFilterCriteria. Call SpreadsheetApp.flush() between changing filter criteria and reading sheet.isRowHiddenByFilter(rowNumber) in the same execution.

  • Pivot table overlaps non-empty cells and fails to render. A PivotTable expands downward and rightward as new departments appear. Dedicate a separate Summary tab (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:

  1. Inside rebuildSummaryPivotTab_, build a pivot filter criterion with SpreadsheetApp.newFilterCriteria().whenTextDoesNotEqualTo(HIDDEN_STATUS).build().

  2. Attach it to pivotTable with pivotTable.addFilter(columns.statusSheetColumn, criteria).

  3. Run buildRequestSummaryView and check Summary!A1.

  4. Confirm Facilities drops to 4 hours (1 request), IT drops to 6 hours (1 request), Finance stays at 13 hours (2 requests), and Grand Total shows 23 hours (4 requests).

  5. Run removePracticeStructureAndProtection when 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
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;
}

appsscript.json

Download file
appsscript.json
{
  "timeZone": "Etc/UTC",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]
}

sample.csv

Download file
sample.csv
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