Call Apps Script tasks from AppSheet bots
Run standalone Apps Script tasks from AppSheet bots, pass USEREMAIL() explicitly, deduplicate retries by event ID, and return typed JSON objects.
Attach a standalone Apps Script function to an AppSheet bot, validate positional arguments including USEREMAIL(), deduplicate retried bot runs with CacheService and ScriptProperties under a script lock, and return a plain JSON object to later automation steps.
Connect an AppSheet Automation bot to a standalone Apps Script function, pass the signed-in AppSheet user's email address as an explicit argument, deduplicate retried bot runs by event ID, and return a plain JSON object to downstream steps. You need project-type and execution-identity rules from Understand the runtime and permissions, shared locks and durable property records from State across runs with PropertiesService, and text cleaning plus SHA-256 hashing from Extract text with regular expressions.
Compare Call a script tasks and doPost webhooks
AppSheet Automation bots can invoke Apps Script in two ways: through the built-in Call a script task or through an outbound HTTP webhook to a deployed doPost(e) web app (covered in HTTP endpoints and webhooks with doPost).
AppSheet's Call a script task calls Google's Apps Script Execution API (scripts.run) directly on a standalone script file in Google Drive (Call Apps Script from an automation). You do not deploy a web app, manage an /exec URL, or verify a shared HMAC secret.
| Integration aspect | AppSheet Call a script task | doPost(e) webhook endpoint |
|---|---|---|
| Project type | Standalone .gs project in Google Drive required. Container-bound scripts do not appear in AppSheet's Drive picker | Bound or standalone project deployed as a Web app |
| Authentication | Google OAuth grant bound when the AppSheet app owner clicks Authorize. No public URL exposed | Web app /exec URL using ANYONE_ANONYMOUS plus a signed payload, or a Google OAuth bearer token |
| Parameter passing | Positional JavaScript function parameters mapped from AppSheet expressions | Raw HTTP body in e.postData.contents with no inbound HTTP request headers |
| Return-value contract | Return Value toggle parses a primitive, list, or plain JSON Object whose declared keys map to [StepName].[KeyName] | ContentService.TextOutput string served behind a 302 redirect with fixed HTTP 200 status |
| Execution timeout | About 2 minutes of synchronous wait when Return Value is on. Up to the 6-minute Apps Script limit when Return Value is off | Up to 6 minutes in Apps Script, though external webhook callers often time out in 5 to 30 seconds |
Prefer Call a script whenever AppSheet owns the workflow trigger. Reserve doPost(e) for external systems that can send only HTTP requests.
Separate the authorizing app owner from USEREMAIL()
Before linking a script to an AppSheet bot, check which Google account runs the .gs code and where the script file lives.
AppSheet's Call a script task has two platform constraints (Call Apps Script from an automation, bound-script guide):
Execution runs as the authorizing app owner: Clicking Authorize in the AppSheet editor binds the task to the AppSheet app owner's Google account, regardless of which person tapped a button or saved a form in the mobile or web app. Inside
.gscode,Session.getEffectiveUser().getEmail()returns that app owner, andSession.getActiveUser().getEmail()does not identify the AppSheet end user.Container-bound scripts are invisible in the picker: A script created from Extensions > Apps Script inside Google Sheets, Docs, Slides, or Forms lives inside its parent document instead of appearing as a standalone Apps Script item in Google Drive. AppSheet's script picker lists only standalone projects created at
script.google.comor from Google Drive (New > More > Google Apps Script).
Because every bot execution runs with the app owner's Google Workspace permissions (Apps Script authorization), never assume the caller is the owner. Pass AppSheet's USEREMAIL() expression into an explicit requestedByEmail parameter, then enforce two checks inside your function before touching data:
Validate the format: An AppSheet app that allows unauthenticated access or runs a scheduled bot without an active user session evaluates
USEREMAIL()to an empty string (""). Check thatrequestedByEmailis a non-empty string matching an email pattern.Authorize the actor: Verify that the normalized email address is allowed to perform the requested operation, such as belonging to your organization's domain or matching an approved role row, before reading or modifying Workspace resources under the owner's credentials.
Run the standalone approval-routing task
The example project assigns an approval tier and routing code (APR-<DEPT>-<HEX>) for purchase requests without requesting external Google Workspace OAuth scopes ("oauthScopes": []).
/**
* @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<string, unknown>} */ (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<string, unknown>} */ (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'");
}
{
"timeZone": "Etc/UTC",
"runtimeVersion": "V8",
"exceptionLogging": "STACKDRIVER",
"oauthScopes": []
}
Test the task in the Apps Script editor before connecting an AppSheet bot:
Open
script.google.comand create a standalone project namedAppSheet approval router(standalone scripts).Replace
Code.gswith the complete source file above.Open Project Settings, check Show "appsscript.json" manifest file in editor, return to Editor, replace
appsscript.jsonwith the manifest above, and save all files.Select
smokeTestAppSheetTaskin the function menu and click Run.
Because LockService, CacheService, PropertiesService, Utilities, and Logger.log require no Google Drive, Sheets, or Gmail scopes, the run completes without an OAuth consent prompt.
Open Execution log. Each run generates new random APR-FAC-... and APR-FIN-... suffixes while preserving identical tiers, replay checks, and rejection messages:
1. First call: status=routed | tier=Manager review | dept=Facilities | by=avery.quinn@example.com | replayed=false | code=APR-FAC-C14F00BE
2. Replay check: status=routed | replayed=true | sameCode=true | sameTimestamp=true
3. High-value call: status=routed | tier=Finance VP review | dept=Finance | by=morgan.lee@example.com | replayed=false | code=APR-FIN-BD4E941C
4. Malformed input: status=rejected: requestedByEmail must be a valid email address | tier=Rejected | code="" | replayed=false Run smokeTestAppSheetTask rather than selecting assignApprovalRoutingCode directly in the editor toolbar. Clicking Run on a parameterized function from the editor passes undefined for every parameter (compare missing required arguments), so assignApprovalRoutingCode returns a structured rejection for eventId. Helper functions ending with _, such as validateRoutingInput_ and withRoutingLock_, stay private and do not clutter the editor toolbar or AppSheet's Function Name menu.
Attach the task to an AppSheet bot and map return keys
To call assignApprovalRoutingCode from a disposable AppSheet app, create a table named Requests with columns RequestId (Text), RoutingEventId (Text with Initial value set to UNIQUEID("UUID")), Department (Text), AmountCents (Number), RoutingStatus (Text), RoutingCode (Text), and ApprovalTier (Text).
Configure the bot and capture the returned JSON object (Call Apps Script from an automation, Use return values from Apps Script tasks):
In the AppSheet editor, open Automation > Bots, click Create a new Bot, and configure an event on table
Requestsfor Adds only.Add a step named
RouteRequest, choose Run a task, and select Call a script.Next to Apps Script Project, browse Google Drive, select
AppSheet approval router, and click Authorize if prompted.Select
assignApprovalRoutingCodeunder Function Name, then fill the five positional parameter expressions in order:eventId:[RoutingEventId]requestId:[RequestId]department:[Department]amountCents:[AmountCents]requestedByEmail:USEREMAIL()
Turn on Return Value, set Return Type to
Object, and declare the seven returned properties under Type Details:status:TextroutingCode:TextapprovalTier:TextnormalizedDepartment:TextrequestedBy:Textreplayed:Yes/NoprocessedAtUtc:Text
Add a second step after
RouteRequestusing Run a data action > Set row values to write[RouteRequest].[status]intoRoutingStatus,[RouteRequest].[routingCode]intoRoutingCode, and[RouteRequest].[approvalTier]intoApprovalTier.
Enabling Return Value changes both timing and serialization (Use return values from Apps Script tasks, scripts.run reference):
Synchronous wait ceiling (about 2 minutes): When Return Value is enabled, AppSheet holds the bot step open waiting for the response and times out around the 2-minute mark, well before Apps Script's own 6-minute execution limit (Apps Script quotas). Turn Return Value off when firing a multi-minute background job whose output the bot does not read.
Plain JSON return values only:
scripts.runserializes the return value as JSON. Return only plain objects, arrays, strings, finite numbers, booleans, ornull. Never returnundefined,NaN, aDateinstance, aMap, or an Apps Script host object such as aRangeorFile.formatUtcTimestamp_formats timestamps withUtilities.formatDatesoprocessedAtUtccrosses the boundary as an ISO-8601 UTC string.
Every return path in assignApprovalRoutingCode, including rejectedReceipt_, returns the same seven keys with string and boolean values. Keeping a fixed key set lets downstream AppSheet expressions read [RouteRequest].[status] and [RouteRequest].[approvalTier] without null-dereference errors when an input fails validation.
Whenever you edit Code.gs or appsscript.json to add a service that introduces a new OAuth scope, such as SpreadsheetApp or UrlFetchApp (authorization scopes), open the Call a script task in the AppSheet editor, refresh the project metadata, and click Authorize again (Call Apps Script from an automation). Authorizing a manual run inside script.google.com does not update the OAuth token stored by AppSheet, so bot runs fail in the AppSheet Automation Monitor until the app owner reauthorizes the task there.
Deduplicate bot retries with CacheService and a ring buffer
Mobile offline sync retries, transient network timeouts between AppSheet and scripts.run, or overlapping bot triggers can invoke assignApprovalRoutingCode more than once for the same row submission. Without deduplication, Utilities.getUuid() would generate a second routingCode on the retry and overwrite the first code.
Anchor eventId to a column stored on the row ([RoutingEventId] initialized once with UNIQUEID("UUID") when the row is created) rather than writing UNIQUEID("UUID") directly inside the bot task's eventId parameter box. An inline UNIQUEID() expression inside the task configuration generates a new random ID on separate bot executions, which prevents assignApprovalRoutingCode from recognizing a retry.
Inside assignApprovalRoutingCode, input validation runs before lock acquisition so malformed calls never block valid ones or touch storage. Valid calls enter withRoutingLock_, which calls LockService.getScriptLock().tryLock(5000) to serialize concurrent executions for up to 5 seconds and releases the lock in finally:
| Lookup stage | Storage API | Role in deduplication |
|---|---|---|
| 1. Fast cache check | CacheService.getScriptCache() | Returns recent receipts (up to 21,600 seconds, or 6 hours) keyed by book.appsheet-integration.event.v1:<eventId> without consuming daily PropertiesService read quota |
| 2. Durable fallback | PropertiesService.getScriptProperties() | Retains the 12 most recent entries under book.appsheet-integration.receipts.v1 (capped at 7,000 UTF-8 bytes, below the 9 KB per-property limit) in case ScriptCache evicts an entry early (Cache reference) |
| 3. Input-digest check | Utilities.computeDigest | Compares the SHA-256 inputDigest of requestId, normalizedDepartment, amountCents, and requestedBy so a genuine retry returns the original receipt with replayed: true, while accidental reuse of an eventId with different values returns a structured rejection |
When a new receipt pushes the ledger past 12 entries or 7,000 UTF-8 bytes, the while loop shifts the oldest entries in memory and collects their cache keys into evictedCacheKeys. After writing the updated ledger with store.setProperty, removeCachedKeys_ calls cache.removeAll(evictedCacheKeys) once outside the loop so eviction never calls a service inside a loop.
For high-volume production apps that process hundreds of rows per hour and need retention beyond 12 recent events, write receipts as rows in a dedicated audit table or database rather than expanding a single 9 KB Script Property (Properties service guide, Apps Script quotas).
Troubleshoot AppSheet task failures
| Symptom | Cause and fix |
|---|---|
Script function not found in the AppSheet Automation Monitor | AppSheet stores Function Name as a saved string when you configure the step. If you rename assignApprovalRoutingCode, delete it, or nest it inside another function, click the refresh icon next to Function Name in the AppSheet task panel and reselect the top-level function (see Script function not found). |
Parameters arrive as null, "", or shifted into the wrong position | AppSheet passes blank column values as null or "", and changing parameter order in Code.gs does not rewrite existing bot expressions. Refresh the script in the AppSheet task panel and check every positional expression (compare missing required arguments). |
[RouteRequest].[status] begins with rejected: | Read the reason after rejected:. Check whether USEREMAIL() is blank in an unauthenticated view, whether the requester's email domain matches ALLOWED_REQUESTER_DOMAIN (example.com), whether Department matches one of the six supported names, and whether AmountCents is a positive integer. |
| Authorization error in AppSheet after adding a Workspace service | Reopen the Call a script step in the AppSheet editor as the app owner and click Authorize so AppSheet's token includes the updated appsscript.json scopes. |
Step fails with Approval routing lock busy after 5000 ms | Concurrent bot executions held LockService.getScriptLock() for more than 5 seconds. Keep work inside withRoutingLock_ fast, and retry the bot event with the same [RoutingEventId] so the ledger returns or creates a single receipt. |
Exercise
Update Code.gs so Engineering requests above 300000 cents (USD 3,000.00) escalate directly to 'Finance VP review' instead of 'Manager review', while other departments keep the 500,000-cent manager threshold:
Add
normalizedDepartmentas a second parameter oncomputeApprovalTier_(amountCents, normalizedDepartment)and update its JSDoc@paramtag to match.Before checking
amountCents <= MANAGER_REVIEW_LIMIT_CENTS, return'Finance VP review'whennormalizedDepartment === 'Engineering' && amountCents > 300000.Pass
validation.normalizedDepartmentintocomputeApprovalTier_insideassignApprovalRoutingCode.In
smokeTestAppSheetTask, add a call for'REQ-1045'in'Engineering'withamountCentsset to350000, runsmokeTestAppSheetTask, and confirm the log printstier=Finance VP review.Run
clearPracticeRoutingReceiptsin the Apps Script editor to deletebook.appsheet-integration.receipts.v1fromScriptPropertiesand clear its cached entries fromScriptCache.
Next, read Build a personal Workspace add-on.
Project files
Complete source files for this chapter's examples.
Assign idempotent approval routing codes from an AppSheet bot task
Code.gs
Download file/**
* @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<string, unknown>} */ (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<string, unknown>} */ (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'");
}
appsscript.json
Download file{
"timeZone": "Etc/UTC",
"runtimeVersion": "V8",
"exceptionLogging": "STACKDRIVER",
"oauthScopes": []
}