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; }