const SHEET_NAME = 'LineItems'; const HEADERS = [ 'Request ID', 'Item', 'Quantity', 'Unit Cost', 'Line Total', 'Status', 'Reference Paths' ]; const FIRST_DATA_ROW = 2; const MAX_ITEM_ROWS = 200; const BUDGET_LIMIT = 1000; const SUMMARY_LABEL = 'TOTAL'; const ALLOWED_STATUSES = ['Approved', 'Pending', 'Blocked']; const LINK_LABELS = ['Spec', 'Runbook']; const REQUEST_ID_COLUMN = 1; const ITEM_COLUMN = 2; const QUANTITY_COLUMN = 3; const UNIT_COST_COLUMN = 4; const LINE_TOTAL_COLUMN = 5; const STATUS_COLUMN = 6; const REFERENCE_COLUMN = 7; const LINE_TOTAL_R1C1 = '=RC[-2]*RC[-1]'; const SUMMARY_TOTAL_R1C1 = '=SUM(R2C5:R[-1]C5)'; const CURRENCY_FORMAT = '$#,##0.00'; const FORMULA_INJECTION_PREFIX = /^[=+\-@]/; const HTTPS_URL_PATTERN = /^https:\/\/[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)+(?::\d+)?(?:\/[^\s<>|"]*)?$/; const MANAGED_CUSTOM_FORMULAS = [ '=$F2="Blocked"', '=$E2>' + BUDGET_LIMIT, '=$F2="Approved"' ]; /** * @typedef {{ * requestId: string, * urls: string[] * }} ValidatedLineItem */ /** * Validates the LineItems table, writes row-independent R1C1 line-total formulas * and a TOTAL summary row, converts Reference Paths into multi-link RichTextValue * cells, and replaces this script's ordered conditional format rules idempotently. * * @returns {void} */ function applyFormulasAndFormatting() { const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); const sheet = getLineItemsSheet_(spreadsheet); const validated = readAndValidateLineItems_(sheet); const itemCount = validated.items.length; const summaryRow = FIRST_DATA_ROW + itemCount; const lineTotalFormulas = validated.items.map(function () { return [LINE_TOTAL_R1C1]; }); const richTextGrid = validated.items.map(function (item) { return [buildReferenceRichText_(item.requestId, item.urls)]; }); const dataRange = sheet.getRange(FIRST_DATA_ROW, 1, itemCount, HEADERS.length); const lineTotalRange = sheet.getRange(FIRST_DATA_ROW, LINE_TOTAL_COLUMN, itemCount, 1); const referenceRange = sheet.getRange(FIRST_DATA_ROW, REFERENCE_COLUMN, itemCount, 1); const summaryRowRange = sheet.getRange(summaryRow, 1, 1, HEADERS.length); const summaryTotalCell = sheet.getRange(summaryRow, LINE_TOTAL_COLUMN); lineTotalRange.setFormulasR1C1(lineTotalFormulas); referenceRange.setRichTextValues(richTextGrid); summaryRowRange.setValues([[SUMMARY_LABEL, '', '', '', '', '', '']]).setFontWeight('bold'); summaryTotalCell.setFormulaR1C1(SUMMARY_TOTAL_R1C1); sheet .getRange(FIRST_DATA_ROW, UNIT_COST_COLUMN, itemCount + 1, 2) .setNumberFormat(CURRENCY_FORMAT); const ruleCounts = replaceManagedFormatRules_(sheet, dataRange); // Flush pending formula writes so getValue() returns the recalculated summary total. SpreadsheetApp.flush(); const evaluatedSummaryTotal = summaryTotalCell.getValue(); Logger.log( 'Wrote R1C1 formulas to ' + SHEET_NAME + '!' + lineTotalRange.getA1Notation() + ' and summary row ' + summaryRow + ' (evaluated total: ' + evaluatedSummaryTotal + ').' ); Logger.log( 'Built rich-text reference links in ' + SHEET_NAME + '!' + referenceRange.getA1Notation() + '.' ); Logger.log( 'Kept ' + ruleCounts.keptCount + ' unmanaged conditional format rule(s); replaced ' + ruleCounts.replacedCount + ' prior script rule(s) with ' + ruleCounts.appliedCount + ' ordered rule(s) on ' + SHEET_NAME + '!' + dataRange.getA1Notation() + '.' ); } /** * Read-only check that logs evaluated values alongside A1 and R1C1 formulas * in Line Total, plus the current conditional format rule count on LineItems. * * @returns {void} */ function inspectSheetFormulasAndRules() { const sheet = getLineItemsSheet_(SpreadsheetApp.getActiveSpreadsheet()); checkHeaders_(sheet); const lastRow = sheet.getLastRow(); if (lastRow < FIRST_DATA_ROW) { throw new Error(SHEET_NAME + ' has no rows below the header row.'); } const rowCount = lastRow - FIRST_DATA_ROW + 1; const labels = sheet.getRange(FIRST_DATA_ROW, REQUEST_ID_COLUMN, rowCount, 1).getValues(); const totalRange = sheet.getRange(FIRST_DATA_ROW, LINE_TOTAL_COLUMN, rowCount, 1); const evaluatedValues = totalRange.getValues(); const a1Formulas = totalRange.getFormulas(); const r1c1Formulas = totalRange.getFormulasR1C1(); const rules = sheet.getConditionalFormatRules(); for (let i = 0; i < rowCount; i += 1) { const sheetRow = FIRST_DATA_ROW + i; const label = String(labels[i][0]).trim() || '(blank)'; const value = evaluatedValues[i][0]; const a1 = a1Formulas[i][0] || '(plain value)'; const r1c1 = r1c1Formulas[i][0] || '(plain value)'; Logger.log( 'Row ' + sheetRow + ' (' + label + '): value=' + value + ' | A1=' + a1 + ' | R1C1=' + r1c1 ); } Logger.log('Conditional format rules on ' + SHEET_NAME + ': ' + rules.length + '.'); } /** * Removes this script's conditional format rules on LineItems and deletes the * TOTAL summary row while leaving item rows intact. * * @returns {void} */ function resetGeneratedFormatting() { const sheet = getLineItemsSheet_(SpreadsheetApp.getActiveSpreadsheet()); checkHeaders_(sheet); const existingRules = sheet.getConditionalFormatRules(); const keptRules = existingRules.filter(function (rule) { return !isManagedLineItemRule_(rule); }); sheet.setConditionalFormatRules(keptRules); const removedRuleCount = existingRules.length - keptRules.length; let removedSummaryRow = false; const lastRow = sheet.getLastRow(); if (lastRow >= FIRST_DATA_ROW) { const bottomLabel = String(sheet.getRange(lastRow, REQUEST_ID_COLUMN).getValue()).trim(); if (bottomLabel === SUMMARY_LABEL) { sheet.deleteRow(lastRow); removedSummaryRow = true; } } Logger.log( 'Removed ' + removedRuleCount + ' script conditional format rule(s) (kept ' + keptRules.length + ') and ' + (removedSummaryRow ? 'deleted the TOTAL summary row.' : 'found no TOTAL summary row.') ); } /** * @param {SpreadsheetApp.Spreadsheet | null} spreadsheet * @returns {SpreadsheetApp.Sheet} */ function getLineItemsSheet_(spreadsheet) { if (!spreadsheet) { throw new Error('Open this project from its bound Google Sheet with Extensions > Apps Script.'); } const sheet = spreadsheet.getSheetByName(SHEET_NAME); if (!sheet) { throw new Error( SHEET_NAME + ' sheet is missing. Rename the first tab ' + SHEET_NAME + ' and import sample.csv.' ); } return sheet; } /** * @param {SpreadsheetApp.Sheet} sheet * @returns {void} */ function checkHeaders_(sheet) { if (sheet.getLastRow() < 1 || sheet.getLastColumn() < HEADERS.length) { throw new Error( SHEET_NAME + ' must contain all seven columns: ' + HEADERS.join(', ') + '.' ); } const headerRow = sheet.getRange(1, 1, 1, HEADERS.length).getValues()[0]; const matches = HEADERS.every(function (expected, index) { return String(headerRow[index]).trim() === expected; }); if (!matches) { throw new Error(SHEET_NAME + ' row 1 must match: ' + HEADERS.join(', ') + '.'); } } /** * Reads and validates all line-item rows in memory before any sheet write runs. * Recognizes an existing TOTAL summary row so reruns stay idempotent. * * @param {SpreadsheetApp.Sheet} sheet * @returns {{items: ValidatedLineItem[], hasSummaryRow: boolean}} */ function readAndValidateLineItems_(sheet) { checkHeaders_(sheet); const lastRow = sheet.getLastRow(); if (lastRow < FIRST_DATA_ROW) { throw new Error(SHEET_NAME + ' needs at least one line-item row below row 1.'); } const allValues = sheet.getRange(1, 1, lastRow, HEADERS.length).getValues(); const bottomRow = allValues[allValues.length - 1]; const hasSummaryRow = String(bottomRow[REQUEST_ID_COLUMN - 1]).trim() === SUMMARY_LABEL; const itemCount = hasSummaryRow ? allValues.length - 2 : allValues.length - 1; if (itemCount < 1) { throw new Error(SHEET_NAME + ' needs at least one line-item row above TOTAL.'); } if (itemCount > MAX_ITEM_ROWS) { throw new Error( SHEET_NAME + ' has ' + itemCount + ' item rows, exceeding MAX_ITEM_ROWS (' + MAX_ITEM_ROWS + ').' ); } const existingRichTexts = sheet .getRange(FIRST_DATA_ROW, REFERENCE_COLUMN, itemCount, 1) .getRichTextValues(); /** @type {ValidatedLineItem[]} */ const items = []; for (let i = 0; i < itemCount; i += 1) { const row = allValues[i + 1]; const sheetRow = FIRST_DATA_ROW + i; if (String(row[REQUEST_ID_COLUMN - 1]).trim() === SUMMARY_LABEL) { throw new Error( 'Row ' + sheetRow + ' is the TOTAL summary row. Insert new items above TOTAL or run resetGeneratedFormatting first.' ); } const requestId = validateSafeLabel_(row[REQUEST_ID_COLUMN - 1], 'Request ID', sheetRow); validateSafeLabel_(row[ITEM_COLUMN - 1], 'Item', sheetRow); const quantity = row[QUANTITY_COLUMN - 1]; if (typeof quantity !== 'number' || !Number.isFinite(quantity) || quantity < 0) { throw new Error('Row ' + sheetRow + ': Quantity must be a non-negative number.'); } const unitCost = row[UNIT_COST_COLUMN - 1]; if (typeof unitCost !== 'number' || !Number.isFinite(unitCost) || unitCost < 0) { throw new Error('Row ' + sheetRow + ': Unit Cost must be a non-negative number.'); } const status = String(row[STATUS_COLUMN - 1]).trim(); if (ALLOWED_STATUSES.indexOf(status) === -1) { throw new Error( 'Row ' + sheetRow + ': Status "' + status + '" must be one of ' + ALLOWED_STATUSES.join(', ') + '.' ); } const urls = resolveRowReferenceUrls_( String(row[REFERENCE_COLUMN - 1]), existingRichTexts[i][0], sheetRow ); items.push({ requestId: requestId, urls: urls }); } return { items: items, hasSummaryRow: hasSummaryRow }; } /** * Rejects blank text and leading characters (=, +, -, @, tab, CR, LF) that * Google Sheets can interpret as formulas. * * @param {unknown} rawValue * @param {string} fieldName * @param {number} sheetRow * @returns {string} */ function validateSafeLabel_(rawValue, fieldName, sheetRow) { const rawString = String(rawValue); if (/^[\t\r\n]/.test(rawString)) { throw new Error( 'Row ' + sheetRow + ': ' + fieldName + ' cannot start with a tab or line break.' ); } const trimmed = rawString.trim(); if (trimmed === '') { throw new Error('Row ' + sheetRow + ': ' + fieldName + ' cannot be blank.'); } if (FORMULA_INJECTION_PREFIX.test(trimmed)) { throw new Error( 'Row ' + sheetRow + ': ' + fieldName + ' "' + trimmed + '" cannot start with =, +, -, or @.' ); } return trimmed; } /** * Extracts 1 or 2 validated https:// URLs from either raw pipe-separated text * or an existing RichTextValue created by an earlier run. * * @param {string} rawText * @param {SpreadsheetApp.RichTextValue | null} existingRichText * @param {number} sheetRow * @returns {string[]} */ function resolveRowReferenceUrls_(rawText, existingRichText, sheetRow) { const trimmed = rawText.trim(); if (trimmed === '') { throw new Error('Row ' + sheetRow + ': Reference Paths cannot be blank.'); } if (existingRichText && trimmed.indexOf('https://') !== 0 && trimmed.indexOf(' (') !== -1) { /** @type {string[]} */ const existingUrls = []; const runs = existingRichText.getRuns(); for (let i = 0; i < runs.length; i += 1) { const linkUrl = runs[i].getLinkUrl(); if (linkUrl !== null) { validateHttpsUrl_(linkUrl, sheetRow); existingUrls.push(linkUrl); } } if (existingUrls.length > 0 && existingUrls.length <= LINK_LABELS.length) { return existingUrls; } } const parts = trimmed.split('|').map(function (part) { return part.trim(); }); if (parts.length < 1 || parts.length > LINK_LABELS.length) { throw new Error( 'Row ' + sheetRow + ': Reference Paths must list 1 or ' + LINK_LABELS.length + ' pipe-separated https:// URLs.' ); } parts.forEach(function (url) { validateHttpsUrl_(url, sheetRow); }); return parts; } /** * @param {string} url * @param {number} sheetRow * @returns {void} */ function validateHttpsUrl_(url, sheetRow) { if (!HTTPS_URL_PATTERN.test(url)) { throw new Error( 'Row ' + sheetRow + ': Reference URL "' + url + '" must use https:// and a valid host.' ); } } /** * Builds one RichTextValue such as "REQ-201 (Spec | Runbook)" using 0-based * UTF-16 start offsets and exclusive end offsets. * * @param {string} requestId * @param {string[]} urls * @returns {SpreadsheetApp.RichTextValue} */ function buildReferenceRichText_(requestId, urls) { let fullText = requestId + ' ('; /** @type {Array<{start: number, end: number, url: string}>} */ const spans = []; for (let i = 0; i < urls.length; i += 1) { if (i > 0) { fullText += ' | '; } const label = LINK_LABELS[i] || 'Link ' + (i + 1); const startOffset = fullText.length; fullText += label; const endOffset = fullText.length; spans.push({ start: startOffset, end: endOffset, url: urls[i] }); } fullText += ')'; const builder = SpreadsheetApp.newRichTextValue().setText(fullText); for (let i = 0; i < spans.length; i += 1) { builder.setLinkUrl(spans[i].start, spans[i].end, spans[i].url); } return builder.build(); } /** * Builds the three ordered conditional format rules for the item rows: * 1. Blocked rows (red fill, dark red bold text) * 2. Over budget rows where Line Total > 1000 (amber fill, dark amber text) * 3. Approved rows (green fill, dark green text) * * @param {SpreadsheetApp.Range} dataRange * @returns {SpreadsheetApp.ConditionalFormatRule[]} */ function buildManagedFormatRules_(dataRange) { const blockedRule = SpreadsheetApp.newConditionalFormatRule() .whenFormulaSatisfied(MANAGED_CUSTOM_FORMULAS[0]) .setBackground('#fce8e6') .setFontColor('#b3261e') .setBold(true) .setRanges([dataRange]) .build(); const overBudgetRule = SpreadsheetApp.newConditionalFormatRule() .whenFormulaSatisfied(MANAGED_CUSTOM_FORMULAS[1]) .setBackground('#fef7e0') .setFontColor('#b06000') .setRanges([dataRange]) .build(); const approvedRule = SpreadsheetApp.newConditionalFormatRule() .whenFormulaSatisfied(MANAGED_CUSTOM_FORMULAS[2]) .setBackground('#e6f4ea') .setFontColor('#137333') .setRanges([dataRange]) .build(); return [blockedRule, overBudgetRule, approvedRule]; } /** * Replaces conditional format rules targeting the managed LineItems table * while preserving any manual rules targeting other columns or ranges. * * @param {SpreadsheetApp.Sheet} sheet * @param {SpreadsheetApp.Range} dataRange * @returns {{keptCount: number, replacedCount: number, appliedCount: number}} */ function replaceManagedFormatRules_(sheet, dataRange) { const existingRules = sheet.getConditionalFormatRules(); const keptRules = existingRules.filter(function (rule) { return !isManagedLineItemRule_(rule); }); const managedRules = buildManagedFormatRules_(dataRange); sheet.setConditionalFormatRules(keptRules.concat(managedRules)); return { keptCount: keptRules.length, replacedCount: existingRules.length - keptRules.length, appliedCount: managedRules.length }; } /** * Identifies rules owned by this script: custom-formula rules carrying one of * MANAGED_CUSTOM_FORMULAS whose target ranges span columns A:G from row 2 downward. * * @param {SpreadsheetApp.ConditionalFormatRule} rule * @returns {boolean} */ function isManagedLineItemRule_(rule) { const ranges = rule.getRanges(); const spansManagedColumns = ranges.length > 0 && ranges.every(function (range) { return ( range.getRow() >= FIRST_DATA_ROW && range.getColumn() === 1 && range.getNumColumns() === HEADERS.length ); }); if (!spansManagedColumns) { return false; } const booleanCondition = rule.getBooleanCondition(); if (!booleanCondition) { return false; } if (booleanCondition.getCriteriaType() !== SpreadsheetApp.BooleanCriteria.CUSTOM_FORMULA) { return false; } const values = booleanCondition.getCriteriaValues(); return values.length > 0 && MANAGED_CUSTOM_FORMULAS.indexOf(String(values[0])) !== -1; }