/** * @typedef {'Auto-approved' | 'Manager review' | 'Finance VP review' | 'Rejected'} ApprovalTier */ /** * @typedef {{ * status: string, * routingCode: string, * approvalTier: ApprovalTier, * normalizedDepartment: string, * requestedBy: string, * replayed: boolean, * processedAtUtc: string * }} ApprovalRoutingReceipt */ /** * @typedef {{ * eventId: string, * inputDigest: string, * receipt: ApprovalRoutingReceipt * }} StoredRoutingEntry_ */ /** * @typedef {{ * version: number, * entries: StoredRoutingEntry_[] * }} RoutingReceiptLedger_ */ /** * @typedef {{ * ok: true, * eventId: string, * requestId: string, * normalizedDepartment: string, * departmentCode: string, * amountCents: number, * requestedBy: string * } | { * ok: false, * reason: string * }} ValidatedRoutingInput_ */ const RECEIPT_LEDGER_KEY = 'book.appsheet-integration.receipts.v1'; const RECEIPT_CACHE_PREFIX = 'book.appsheet-integration.event.v1:'; const CACHE_TTL_SECONDS = 21600; const LOCK_WAIT_MILLIS = 5000; const MAX_STORED_RECEIPTS = 12; const MAX_LEDGER_BYTES = 7000; const MAX_AMOUNT_CENTS = 100000000; const AUTO_APPROVE_LIMIT_CENTS = 50000; const MANAGER_REVIEW_LIMIT_CENTS = 500000; const ALLOWED_REQUESTER_DOMAIN = 'example.com'; const ZERO_WIDTH_CHARACTERS = /[\u200b-\u200d\ufeff]/g; const EVENT_ID_PATTERN = /^[A-Za-z0-9._:-]{4,80}$/; const REQUEST_ID_PATTERN = /^REQ-\d{4}$/; const EMAIL_TOKEN_PATTERN = /^[A-Za-z0-9._%+-]+@([A-Za-z0-9.-]+\.[A-Za-z]{2,})$/; /** @type {ReadonlyArray<{ name: string, code: string }>} */ const SUPPORTED_DEPARTMENTS = [ { name: 'Engineering', code: 'ENG' }, { name: 'Facilities', code: 'FAC' }, { name: 'Finance', code: 'FIN' }, { name: 'HR', code: 'HR' }, { name: 'Legal', code: 'LEG' }, { name: 'Operations', code: 'OPS' }, ]; /** * Assigns an approval tier and routing code for an AppSheet "Call a script" bot task * with "Return Value" enabled. Deduplicates AppSheet retries by eventId across * ScriptCache and a bounded ScriptProperties ring buffer under a script lock. * * @param {unknown} eventId Row-anchored event identifier from AppSheet. * @param {unknown} requestId Request identifier matching REQ-dddd. * @param {unknown} department Department name from the AppSheet row. * @param {unknown} amountCents Positive integer request amount in cents. * @param {unknown} requestedByEmail Signed-in AppSheet user email from USEREMAIL(). * @returns {ApprovalRoutingReceipt} */ function assignApprovalRoutingCode( eventId, requestId, department, amountCents, requestedByEmail, ) { const nowUtc = formatUtcTimestamp_(new Date()); const validation = validateRoutingInput_( eventId, requestId, department, amountCents, requestedByEmail, ); if (!validation.ok) { return rejectedReceipt_(validation.reason, nowUtc); } const inputDigest = sha256Hex_( [ validation.requestId, validation.normalizedDepartment, String(validation.amountCents), validation.requestedBy, ].join('|'), ); const cacheKey = RECEIPT_CACHE_PREFIX + validation.eventId; return withRoutingLock_(() => { const cache = CacheService.getScriptCache(); const cachedEntry = readCachedEntry_(cache, cacheKey); if (cachedEntry !== null && cachedEntry.eventId === validation.eventId) { if (cachedEntry.inputDigest !== inputDigest) { return rejectedReceipt_( 'eventId was already used for different request parameters', nowUtc, ); } return replayReceipt_(cachedEntry.receipt); } const store = PropertiesService.getScriptProperties(); const ledger = readReceiptLedger_(store); for (const entry of ledger.entries) { if (entry.eventId === validation.eventId) { writeCachedEntry_(cache, cacheKey, entry); if (entry.inputDigest !== inputDigest) { return rejectedReceipt_( 'eventId was already used for different request parameters', nowUtc, ); } return replayReceipt_(entry.receipt); } } const approvalTier = computeApprovalTier_(validation.amountCents); const randomSuffix = Utilities.getUuid().replace(/-/g, '').slice(0, 8).toUpperCase(); /** @type {ApprovalRoutingReceipt} */ const freshReceipt = { status: 'routed', routingCode: 'APR-' + validation.departmentCode + '-' + randomSuffix, approvalTier: approvalTier, normalizedDepartment: validation.normalizedDepartment, requestedBy: validation.requestedBy, replayed: false, processedAtUtc: nowUtc, }; /** @type {StoredRoutingEntry_} */ const newEntry = { eventId: validation.eventId, inputDigest: inputDigest, receipt: freshReceipt, }; ledger.entries.push(newEntry); /** @type {string[]} */ const evictedCacheKeys = []; while ( ledger.entries.length > MAX_STORED_RECEIPTS || utf8ByteLength_(JSON.stringify(ledger)) > MAX_LEDGER_BYTES ) { const oldest = ledger.entries.shift(); if (!oldest) { break; } evictedCacheKeys.push(RECEIPT_CACHE_PREFIX + oldest.eventId); } store.setProperty(RECEIPT_LEDGER_KEY, JSON.stringify(ledger)); if (evictedCacheKeys.length > 0) { removeCachedKeys_(cache, evictedCacheKeys); } writeCachedEntry_(cache, cacheKey, newEntry); return freshReceipt; }); } /** * Editor entrypoint that exercises a first-time call, a duplicate eventId replay, * a high-value Finance VP review tier, and a structured rejection on malformed input. * * @returns {void} */ function smokeTestAppSheetTask() { const runTag = Utilities.getUuid().slice(0, 8); const firstEventId = 'EVT-1042-' + runTag; const highValueEventId = 'EVT-1043-' + runTag; const invalidEventId = 'EVT-1044-' + runTag; const first = assignApprovalRoutingCode( firstEventId, 'REQ-1042', ' facilities\u200b ', 180000, 'Avery.Quinn@Example.com', ); Logger.log( '1. First call: status=' + first.status + ' | tier=' + first.approvalTier + ' | dept=' + first.normalizedDepartment + ' | by=' + first.requestedBy + ' | replayed=' + String(first.replayed) + ' | code=' + first.routingCode, ); const replay = assignApprovalRoutingCode( firstEventId, 'REQ-1042', 'Facilities', 180000, 'avery.quinn@example.com', ); Logger.log( '2. Replay check: status=' + replay.status + ' | replayed=' + String(replay.replayed) + ' | sameCode=' + String(replay.routingCode === first.routingCode) + ' | sameTimestamp=' + String(replay.processedAtUtc === first.processedAtUtc), ); const vpReview = assignApprovalRoutingCode( highValueEventId, 'REQ-1043', 'Finance', 750000, 'morgan.lee@example.com', ); Logger.log( '3. High-value call: status=' + vpReview.status + ' | tier=' + vpReview.approvalTier + ' | dept=' + vpReview.normalizedDepartment + ' | by=' + vpReview.requestedBy + ' | replayed=' + String(vpReview.replayed) + ' | code=' + vpReview.routingCode, ); const rejected = assignApprovalRoutingCode( invalidEventId, 'REQ-1044', 'Legal', 25000, '', ); Logger.log( '4. Malformed input: status=' + rejected.status + ' | tier=' + rejected.approvalTier + ' | code="' + rejected.routingCode + '" | replayed=' + String(rejected.replayed), ); } /** * Deletes the practice receipt ledger from ScriptProperties and removes its cached * event entries from ScriptCache. * * @returns {void} */ function clearPracticeRoutingReceipts() { withRoutingLock_(() => { const store = PropertiesService.getScriptProperties(); const cache = CacheService.getScriptCache(); const rawLedger = store.getProperty(RECEIPT_LEDGER_KEY); /** @type {string[]} */ const cacheKeys = []; if (rawLedger !== null) { try { const parsed = JSON.parse(rawLedger); if (parsed && typeof parsed === 'object' && Array.isArray(parsed.entries)) { for (const item of parsed.entries) { if (item && typeof item === 'object' && typeof item.eventId === 'string') { cacheKeys.push(RECEIPT_CACHE_PREFIX + item.eventId); } } } } catch (error) { Logger.log('Stored receipt ledger was not valid JSON; deleting property directly.'); } } store.deleteProperty(RECEIPT_LEDGER_KEY); if (cacheKeys.length > 0) { removeCachedKeys_(cache, cacheKeys); } Logger.log('Cleared practice routing receipts from ScriptProperties and ScriptCache.'); }); } /** * Validates and normalizes the five positional arguments supplied by AppSheet. * * @param {unknown} eventId * @param {unknown} requestId * @param {unknown} department * @param {unknown} amountCents * @param {unknown} requestedByEmail * @returns {ValidatedRoutingInput_} */ function validateRoutingInput_( eventId, requestId, department, amountCents, requestedByEmail, ) { if (typeof eventId !== 'string') { return { ok: false, reason: 'eventId is required and must be a string' }; } const cleanedEventId = cleanSingleLineText_(eventId); if (!EVENT_ID_PATTERN.test(cleanedEventId)) { return { ok: false, reason: 'eventId must be 4 to 80 ASCII letters, digits, dots, colons, underscores, or hyphens', }; } if (typeof requestId !== 'string') { return { ok: false, reason: 'requestId is required and must match REQ-dddd' }; } const cleanedRequestId = cleanSingleLineText_(requestId).toUpperCase(); if (!REQUEST_ID_PATTERN.test(cleanedRequestId)) { return { ok: false, reason: 'requestId must match REQ-dddd' }; } if (typeof department !== 'string') { return { ok: false, reason: 'department is required' }; } const cleanedDepartment = cleanSingleLineText_(department).toLowerCase(); let matchedDepartment = null; for (const candidate of SUPPORTED_DEPARTMENTS) { if (candidate.name.toLowerCase() === cleanedDepartment) { matchedDepartment = candidate; break; } } if (matchedDepartment === null) { return { ok: false, reason: 'department must be Engineering, Facilities, Finance, HR, Legal, or Operations', }; } if ( typeof amountCents !== 'number' || !Number.isInteger(amountCents) || amountCents < 1 || amountCents > MAX_AMOUNT_CENTS ) { return { ok: false, reason: 'amountCents must be a positive integer up to ' + String(MAX_AMOUNT_CENTS), }; } if (typeof requestedByEmail !== 'string') { return { ok: false, reason: 'requestedByEmail must be a valid email address' }; } const cleanedEmail = cleanSingleLineText_(requestedByEmail).toLowerCase(); const emailMatch = EMAIL_TOKEN_PATTERN.exec(cleanedEmail); if (!emailMatch) { return { ok: false, reason: 'requestedByEmail must be a valid email address' }; } const emailDomain = emailMatch[1]; if (emailDomain !== ALLOWED_REQUESTER_DOMAIN) { return { ok: false, reason: 'requestedByEmail must belong to ' + ALLOWED_REQUESTER_DOMAIN, }; } return { ok: true, eventId: cleanedEventId, requestId: cleanedRequestId, normalizedDepartment: matchedDepartment.name, departmentCode: matchedDepartment.code, amountCents: amountCents, requestedBy: cleanedEmail, }; } /** * Computes the deterministic approval tier from an integer cent amount. * * @param {number} amountCents * @returns {ApprovalTier} */ function computeApprovalTier_(amountCents) { if (amountCents <= AUTO_APPROVE_LIMIT_CENTS) { return 'Auto-approved'; } if (amountCents <= MANAGER_REVIEW_LIMIT_CENTS) { return 'Manager review'; } return 'Finance VP review'; } /** * Builds a plain JSON-serializable rejection receipt with fixed keys. * * @param {string} reason * @param {string} processedAtUtc * @returns {ApprovalRoutingReceipt} */ function rejectedReceipt_(reason, processedAtUtc) { return { status: 'rejected: ' + reason, routingCode: '', approvalTier: 'Rejected', normalizedDepartment: '', requestedBy: '', replayed: false, processedAtUtc: processedAtUtc, }; } /** * Clones a stored receipt with replayed set to true. * * @param {ApprovalRoutingReceipt} original * @returns {ApprovalRoutingReceipt} */ function replayReceipt_(original) { return { status: original.status, routingCode: original.routingCode, approvalTier: original.approvalTier, normalizedDepartment: original.normalizedDepartment, requestedBy: original.requestedBy, replayed: true, processedAtUtc: original.processedAtUtc, }; } /** * Runs an operation under the project-wide script lock. * * @template T * @param {() => T} operation * @returns {T} */ function withRoutingLock_(operation) { const lock = LockService.getScriptLock(); if (!lock.tryLock(LOCK_WAIT_MILLIS)) { throw new Error('Approval routing lock busy after 5000 ms; retry with the same eventId.'); } try { return operation(); } finally { lock.releaseLock(); } } /** * Reads and validates the bounded receipt ledger from ScriptProperties. * * @param {PropertiesService.Properties} store * @returns {RoutingReceiptLedger_} */ function readReceiptLedger_(store) { const raw = store.getProperty(RECEIPT_LEDGER_KEY); if (raw === null) { return { version: 1, entries: [] }; } if (raw.length === 0 || utf8ByteLength_(raw) > MAX_LEDGER_BYTES) { throw new Error('Stored routing receipt ledger exceeds size bounds; inspect ScriptProperties.'); } let parsed; try { parsed = JSON.parse(raw); } catch (error) { throw new Error('Stored routing receipt ledger is not valid JSON; inspect ScriptProperties.'); } if ( !hasExactKeys_(parsed, ['version', 'entries']) || parsed.version !== 1 || !Array.isArray(parsed.entries) || parsed.entries.length > MAX_STORED_RECEIPTS ) { throw new Error('Stored routing receipt ledger has an unsupported version or shape.'); } /** @type {StoredRoutingEntry_[]} */ const validatedEntries = []; for (const candidate of parsed.entries) { const decoded = decodeStoredEntry_(candidate); if (decoded === null) { throw new Error('Stored routing receipt ledger contains a malformed entry.'); } validatedEntries.push(decoded); } return { version: 1, entries: validatedEntries }; } /** * Reads a single cached routing entry from ScriptCache, returning null on miss or error. * * @param {CacheService.Cache} cache * @param {string} cacheKey * @returns {StoredRoutingEntry_ | null} */ function readCachedEntry_(cache, cacheKey) { let raw = null; try { raw = cache.get(cacheKey); } catch (error) { return null; } if (raw === null || raw.length > 2048) { return null; } try { return decodeStoredEntry_(JSON.parse(raw)); } catch (error) { return null; } } /** * Writes a single routing entry to ScriptCache on a best-effort basis. * * @param {CacheService.Cache} cache * @param {string} cacheKey * @param {StoredRoutingEntry_} entry * @returns {void} */ function writeCachedEntry_(cache, cacheKey, entry) { try { cache.put(cacheKey, JSON.stringify(entry), CACHE_TTL_SECONDS); } catch (error) { Logger.log('ScriptCache write unavailable; ScriptProperties retains the durable receipt.'); } } /** * Removes evicted or cleared keys from ScriptCache in a single batch call. * * @param {CacheService.Cache} cache * @param {string[]} cacheKeys * @returns {void} */ function removeCachedKeys_(cache, cacheKeys) { try { cache.removeAll(cacheKeys); } catch (error) { Logger.log('ScriptCache removal unavailable; cached entries expire automatically.'); } } /** * Validates a parsed JSON value as a StoredRoutingEntry_. * * @param {unknown} candidate * @returns {StoredRoutingEntry_ | null} */ function decodeStoredEntry_(candidate) { if (!hasExactKeys_(candidate, ['eventId', 'inputDigest', 'receipt'])) { return null; } const record = /** @type {Record} */ (candidate); if ( typeof record.eventId !== 'string' || !EVENT_ID_PATTERN.test(record.eventId) || typeof record.inputDigest !== 'string' || !/^[a-f0-9]{64}$/.test(record.inputDigest) || !isApprovalReceipt_(record.receipt) ) { return null; } return { eventId: record.eventId, inputDigest: record.inputDigest, receipt: record.receipt, }; } /** * Checks whether an unknown value matches the ApprovalRoutingReceipt schema. * * @param {unknown} value * @returns {value is ApprovalRoutingReceipt} */ function isApprovalReceipt_(value) { if ( !hasExactKeys_(value, [ 'status', 'routingCode', 'approvalTier', 'normalizedDepartment', 'requestedBy', 'replayed', 'processedAtUtc', ]) ) { return false; } const rec = /** @type {Record} */ (value); return ( typeof rec.status === 'string' && typeof rec.routingCode === 'string' && (rec.approvalTier === 'Auto-approved' || rec.approvalTier === 'Manager review' || rec.approvalTier === 'Finance VP review' || rec.approvalTier === 'Rejected') && typeof rec.normalizedDepartment === 'string' && typeof rec.requestedBy === 'string' && typeof rec.replayed === 'boolean' && typeof rec.processedAtUtc === 'string' ); } /** * Verifies that an unknown value is a non-array object with an exact set of keys. * * @param {unknown} value * @param {string[]} expectedKeys * @returns {boolean} */ function hasExactKeys_(value, expectedKeys) { if (!value || typeof value !== 'object' || Array.isArray(value)) { return false; } const actualKeys = Object.keys(value); if (actualKeys.length !== expectedKeys.length) { return false; } for (const key of actualKeys) { if (!expectedKeys.includes(key)) { return false; } } return true; } /** * Normalizes Unicode composition to NFC, removes zero-width formatting characters, * trims outer whitespace, and collapses interior whitespace runs. * * @param {string} rawText * @returns {string} */ function cleanSingleLineText_(rawText) { return rawText .normalize('NFC') .replace(ZERO_WIDTH_CHARACTERS, '') .trim() .replace(/\s+/g, ' '); } /** * Computes a lowercase 64-character SHA-256 hex digest for UTF-8 text. * * @param {string} text * @returns {string} */ function sha256Hex_(text) { const signedBytes = Utilities.computeDigest( Utilities.DigestAlgorithm.SHA_256, text, Utilities.Charset.UTF_8, ); return signedBytes.map((byte) => (byte & 0xff).toString(16).padStart(2, '0')).join(''); } /** * Measures the UTF-8 byte length of a string. * * @param {string} text * @returns {number} */ function utf8ByteLength_(text) { return Utilities.newBlob(text).getBytes().length; } /** * Formats a Date as an ISO-8601 UTC timestamp string so return values stay plain JSON. * * @param {Date} instant * @returns {string} */ function formatUtcTimestamp_(instant) { return Utilities.formatDate(instant, 'Etc/UTC', "yyyy-MM-dd'T'HH:mm:ss'Z'"); }