Spin up in your Google account

1. VERIFY IDENTITY FIRST (magic link). Since the web app request object exposes only e.parameter/e.parameters/e.postData/e.pathInfo/e.queryString and NO client IP, headers, cookies, or user-agent, the only trustworthy identity anchor is a verified email. Flow: signer enters email on a doGet page -> doPost generates a random token (Utilities.getUuid()), stores {email, token, expiry, documentId} in PropertiesService (script properties) or a Sheet row, and emails a link `?token=...` via MailApp.sendEmail (or GmailApp.sendEmail to send-as a domain address on Workspace). Scopes: `script.send_mail` (MailApp) or `gmail.send` (GmailApp). Clicking the link is the email-possession proof. 2. SERVE THE SIGNING PAGE (doGet). doGet validates the token against PropertiesService, then returns HtmlService.createHtmlOutput / createTemplateFromFile with: the rendered document text (or a link to view it), a consent checkbox ("I agree this electronic signature is legally binding"), a typed-full-name text input, and an HTML5 `<canvas>` signature pad (either hand-rolled pointer/touch event capture or a bundled lib like signature_pad.js inlined — no external CDN dependency needed). On submit, JS calls `canvas.toDataURL('image/png')` to get a base64 PNG and POSTs a form (or google.script.run) with: token, typedName, consentBool, pngDataUrl. 3. COMPUTE THE DOCUMENT HASH client-side or server-side. Hash the exact bytes/text of the document being signed with `Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, documentBytes)` and record the hex. This binds the signature to a specific document version so you can later prove "this signature was for THIS content." 4. RECEIVE + PERSIST (doPost). doPost re-validates the token (single-use: mark consumed in PropertiesService to prevent replay), records server-authoritative attribution: `new Date()` server timestamp (the server clock, NOT client-supplied), the verified email from the token record, the typed name, the consent boolean, the document SHA-256, and the PNG. Strip the `data:image/png;base64,` prefix and decode with `Utilities.base64Decode(...)` -> `Utilities.newBlob(bytes, 'image/png', 'signature.png')`. Wrap the whole write in LockService.getScriptLock() to avoid concurrent-write races if the same web app handles many signers. Append an audit row to a Sheet via SpreadsheetApp (append-only log: timestamp, email, name, hash, consent, token). Scope: `spreadsheets`. 5. STAMP INTO A DOCUMENT TEMPLATE. Copy a DocumentApp template with DriveApp: `DriveApp.getFileById(templateId).makeCopy(name, folder)` (scope `drive` or the narrower `drive.file` if the app created the template). Open with `DocumentApp.openById(copyId)`; use `body.replaceText('{{NAME}}', typedName)`, `replaceText('{{DATE}}', ...)`, `replaceText('{{EMAIL}}', email)`, `replaceText('{{HASH}}', hash)`. Insert the signature image at a placeholder: find the paragraph/element and `paragraph.appendInlineImage(signatureBlob)` (or insert at a positioned InlineImage). Call `doc.saveAndClose()`. Scopes: `documents`, `drive`. 6. EXPORT PDF TO DRIVE. Get the finalized PDF blob: `DriveApp.getFileById(doc.getId()).getAs('application/pdf')` (or `doc.getAs(MimeType.PDF)`), then `folder.createFile(pdfBlob)` to persist the signed PDF in the owner's Drive. Optionally email the PDF to the signer and owner as an attachment via MailApp.sendEmail({attachments:[pdfBlob]}). 7. (OPTIONAL) EXTERNAL TIMESTAMP / NOTARY. For stronger non-repudiation you can UrlFetchApp.fetch() an external RFC-3161 timestamp authority or a trusted-timestamp/anchoring API, POSTing the SHA-256 to get back a signed timestamp token you store alongside the record. This is the only way to get a timestamp that isn't just the owner's own server clock. Scope: external_request (UrlFetch).

- NO IP / UA / HEADERS. The single biggest thing people get wrong: you CANNOT capture the signer's IP address, user-agent, or any HTTP header — the Apps Script request object simply doesn't expose them. Any competitor claiming "we log the signer's IP" cannot be doing it in pure Apps Script. Attribution must be reconstructed from verified email + server timestamp + typed name + drawn image + consent + document hash. Be honest in your UI/audit trail about exactly what was and wasn't captured. - CLIENT TIMESTAMP IS UNTRUSTED. Never record the browser's clock as the signing time — a client can lie. Use `new Date()` inside doPost (server-side). Note this is the OWNER's server clock, still not an independent authority (see external timestamp option). - THE FINAL FILE IS OWNER-EDITABLE. The signed PDF/Doc lives in the owner's Drive and the owner has full edit rights — so it is NOT immutable and NOT tamper-proof. Anyone with edit access could regenerate or alter it. Do not market "forensic" or "tamper-proof." The integrity claim you CAN make is: the SHA-256 in the append-only audit log lets you detect whether a presented document matches what was signed. - MAGIC-LINK REPLAY. Tokens must be single-use and expiring, marked consumed atomically (LockService) — otherwise a leaked link lets someone re-sign or replay. - NO NATIVE RATE LIMITING. A public web app has no built-in throttling; a bot can hammer doGet/doPost. Gate everything behind the verified-email token and add your own PropertiesService/CacheService counters if abuse is a concern. - CANVAS QUALITY. `toDataURL('image/png')` on a small/low-DPR canvas yields a jagged signature; set the canvas backing store to devicePixelRatio and a sane resolution or the embedded image looks bad in the PDF. - BASE64 PAYLOAD SIZE. A high-res PNG data URL can be hundreds of KB in the POST body; fine for postData, but watch total execution and don't stuff raw base64 into a Sheet cell (50k char/cell limit) — store the PNG as a Drive file/blob, put only its fileId in the Sheet. - COLD START. First hit after idle adds ~1-2s; the signing page can feel slow. Pre-warm expectations, don't block the canvas render on server work.

- EMAIL (magic links + PDF delivery): consumer ~100 recipients/day via MailApp/GmailApp; Workspace ~1,500/day. High signing volume on a consumer account will hit the 100/day wall fast — each signer typically costs 1 magic-link email + optionally 1-2 completion emails. Workspace's 15x headroom matters here. - EXECUTION: 6 min hard cap per doPost execution. DocumentApp replaceText + appendInlineImage + PDF export is usually well under that, but batch/bulk signing loops can blow it. - URLFETCH (external timestamp/notary API): consumer ~20k/day, Workspace 100k/day — non-issue at SMB volume. - TRIGGERS: not needed for the core synchronous sign flow, but if you use a time-driven trigger to send reminder emails for unsigned docs, that draws on the ~90 min/day (consumer) / ~6 hr/day (Workspace) trigger runtime budget and the same email quota. - SHEETS-AS-DB: the audit log is append-only rows; Sheets tops out in the tens of thousands of rows, so extremely high signature volume eventually needs archiving/rotation to a new Sheet. - PROPERTIESSERVICE: token store has size limits (500 KB total, 9 KB/value) — store minimal token records, not payloads.

- WHAT YOU CAN HONESTLY CLAIM: Under US ESIGN Act and UETA, an electronic signature is valid when there is (a) intent to sign, (b) consent to do business electronically, (c) association of the signature with the record, and (d) attribution/record retention. This stack can satisfy all four: the consent checkbox + typed name + drawn signature show intent and consent; the verified-email magic link provides attribution; the document SHA-256 + append-only audit log + embedded signature associate the signature with a specific document version and retain the record. That is a genuinely legally-useful e-signature for most SMB agreements. - WHAT YOU CANNOT CLAIM: NOT tamper-proof, NOT immutable, NOT forensic — the signed file sits in an owner-editable Drive, and the 'audit trail' is a Sheet the owner can also edit. You are relying on the owner's good faith plus a detectable hash, not cryptographic custody. Do not imply the record is court-hardened evidence on par with a dedicated e-sign vendor with tamper-evident sealing and independent timestamping. - NOT eIDAS QUALIFIED / NOT ADVANCED. This is a simple/basic electronic signature. It is NOT an eIDAS Advanced or Qualified Electronic Signature (those need a qualified certificate and QSCD) — do not sell it into EU contexts that legally require QES. - NO CAPTURED IP/UA in the audit record — say so plainly, because customers coming from DocuSign/Dropbox Sign expect IP logging and it is impossible here. - STRENGTHEN IF NEEDED: adding an external RFC-3161 trusted timestamp via UrlFetch and, on Workspace, sending magic links + final PDFs from the business's own verified domain (deliverability + a stronger identity signal) meaningfully hardens the attribution story without leaving the stack.

1. STORE THE TEMPLATE ONCE. The generated app keeps a Google Doc (or Slides deck) template in the owner's Drive, containing literal tokens like {{customer_name}}, {{invoice_total}}, {{date}}. You keep its fileId in PropertiesService.getScriptProperties(). Scopes touched from here on: https://www.googleapis.com/auth/documents, /auth/drive, and /auth/spreadsheets — the generated appsscript.json must declare these in oauthScopes or the owner's consent screen won't cover them. 2. TRIGGER. Either doPost(e) (a client POSTs form/row data; you read e.parameter / JSON.parse(e.postData.contents)) or a time-driven trigger (ScriptApp.newTrigger('run').timeBased()...) that reads pending rows from the sheet via SpreadsheetApp.openById(id).getSheetByName('Queue').getDataRange().getValues(). 3. COPY THE TEMPLATE PER RECORD. DriveApp.getFileById(TEMPLATE_ID).makeCopy(fileName, destFolder) → returns a new File. You must copy, never mutate the template. Grab the copy's id. 4. OPEN AND REPLACE. For a Doc: DocumentApp.openById(copyId); const body = doc.getBody(); then body.replaceText('\\{\\{customer_name\\}\\}', row.name) for each field — note replaceText takes a REGEX string, so literal braces must be escaped. Call doc.saveAndClose() before exporting or the changes may not be flushed. For images (logo, signature), you can't replaceText an image; you either bake the logo into the template, or use body.findElement / a {{logo}} inline-image placeholder you replace via InlineImage manipulation (fiddlier). Slides equivalent: SlidesApp.openById(id), presentation.replaceAllText('{{customer_name}}', value) then saveAndClose(). 5. EXPORT TO PDF. const pdfBlob = DriveApp.getFileById(copyId).getAs('application/pdf'); (or getAs(MimeType.PDF)). This uses Google's own Doc→PDF renderer, so fonts embedded in the Doc, page size, margins, headers/footers, and page breaks all render exactly as the Doc would print. Set the fileName: pdfBlob.setName(fileName + '.pdf'). 6. FILE IT. Build/find a dated folder: getOrCreate a folder tree like /Invoices/2026-06/ using DriveApp.getFoldersByName + createFolder (there's no mkdir -p; you walk it yourself). Then folder.createFile(pdfBlob) → returns the stored PDF file. Optionally DriveApp.getFileById(copyId).setTrashed(true) to delete the intermediate editable Doc so you don't litter Drive. 7. DELIVER. GmailApp.sendEmail(to, subject, plainBody, { htmlBody, attachments:[pdfBlob], name:'Sender Name' }). On Workspace you can set from: an alias to send-as the business domain. Or share a link instead: pdfFile.getUrl() + set sharing via pdfFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW). 8. MARK DONE. Write the PDF's URL/id and a server timestamp (new Date()) back to the queue row so re-runs are idempotent and a trigger batch can resume where it stopped.

- replaceText IS A REGEX. body.replaceText('{{total}}', x) will silently mis-match because { } are regex quantifiers. Escape them: '\\{\\{total\\}\\}'. This is the single most common bug. - FONTS: the PDF renders with whatever fonts the Doc uses. Google Fonts available in the Docs editor render fine. A brand's proprietary/licensed desktop font is NOT available and will substitute — you cannot upload an arbitrary .ttf into the Docs renderer. Pick a close Google-hosted font (or bake headline art into the template as an image). - LOGOS/IMAGES: text replaceText can't insert an image. Bake fixed branding (logo, letterhead) into the template. Per-record images require InlineImage APIs and are slow/finicky. - saveAndClose() BEFORE getAs(). If you export before the edits flush you get the unmodified template. This is intermittent and maddening to debug. - 6-MINUTE EXECUTION LIMIT IS THE REAL CEILING FOR BATCHES. Each record does: makeCopy (~1-2s, a Drive write), openById+replace+saveAndClose (~1-3s), getAs PDF render (~1-3s, the slowest step). Realistically ~3-6s/record, so ~60-120 PDFs per execution before you hit 6 min and Apps Script kills the run mid-flight. For 500 invoices you MUST chunk: process N rows, write progress back to the sheet, and let a self-rescheduling trigger (or a trigger every minute) pick up the next chunk. Do NOT try to loop 1,000 records in one doPost — it will time out and the user gets a truncated batch with no error surfaced to the browser. - makeCopy + getAs are QUOTA-METERED Drive operations, not free CPU — they count toward daily runtime AND can throttle under burst. - NO CLIENT IP/UA. If this PDF is a "signed" document, remember the web app request exposes only e.parameter/e.postData — you cannot capture the signer's IP, headers, or user-agent. Attribution must come from a verified email, the typed name, and the server timestamp. - COLD START ~1-2s per execution adds up across many trigger fires. - Doc→PDF has no fine print-control (no bleed, no CMYK, no crop marks). It's an office-quality PDF, not a print-shop-ready one.

- 6 min per execution (hard kill); ~90 min/day total trigger runtime on consumer, ~6 hr/day on Workspace — this caps total PDFs/day for automated batches, not just per-run. - Email: MailApp/GmailApp ~100 recipients/day consumer, ~1,500/day Workspace. If you email each PDF, THIS is often the binding limit long before Drive quotas — a 200-invoice consumer batch that emails each one will hit the wall. Workspace's 15x headroom is the direct unlock. - Drive file creation/copy has daily create quotas and burst throttling; makeCopy on thousands of files/day can trip "too many requests" — add Utilities.sleep backoff and idempotent retries. - Sheets-as-queue tops out in the tens of thousands of rows before getValues() reads get slow/memory-heavy; page the queue if you're at that scale. - Chunk size math: budget ~5s/record, leave headroom, so ~50-60 records/execution is a safe chunk; a per-minute trigger then does ~50/min, i.e. thousands/day within the runtime cap on Workspace, far fewer on consumer.

- The PDF and all intermediate Docs live in the OWNER's own Drive — genuinely owned, private, and portable. That's a real trust win over a SaaS that holds their invoices. - But an owner-editable Doc/PDF is NOT tamper-proof or forensic. The owner (and any editor) can alter the template, re-run generation, or edit the output. Do NOT market these as "immutable," "certified," or "legally binding signed originals." For a defensible record you'd need an external notarization/hash service via UrlFetchApp, and even then you're attesting to a hash, not preventing edits. - PDFs emailed as attachments leave Google's boundary; if content is PHI, only Workspace Business Standard+ under a signed Google BAA is HIPAA-eligible, and Gmail-to-external-recipient email is generally outside typical BAA-covered configurations — don't imply consumer Gmail is HIPAA-safe. - "ANYONE_WITH_LINK" sharing on a filed PDF is effectively public to anyone who gets the URL — be explicit with users that link-sharing an invoice/statement is not access-controlled.

1. SETUP (once, at provisioning): Store the Stripe SECRET KEY and the WEBHOOK SIGNING SECRET (whsec_...) in PropertiesService.getScriptProperties().setProperty(). These live in the owner's own script project, not in your infrastructure. The script declares the OAuth scope https://www.googleapis.com/auth/script.external_request (UrlFetchApp) — that is the only scope payments strictly needs; SpreadsheetApp (auth/spreadsheets) is needed for the ledger, and LockService/CacheService need no scope. NOTE: Stripe has no official Apps Script SDK — you hand-roll the REST calls. 2. CREATE THE CHARGE (doGet or a client fetch): The buyer hits your web app. Server-side you call UrlFetchApp.fetch('https://api.stripe.com/v1/checkout/sessions', {method:'post', headers:{Authorization:'Bearer '+secret}, payload:{...}}). CRITICAL: Stripe expects application/x-www-form-urlencoded with bracket notation for nested fields (e.g. 'line_items[0][price]', 'line_items[0][quantity]'), NOT JSON — UrlFetchApp form-encodes a flat payload object for you, but you must flatten the nesting into bracketed keys yourself. Pass your own 'idempotency_key' in the headers (a UUID you generate and store) so a retried CREATE doesn't double-create a session. Parse the JSON response, read session.url. 3. REDIRECT THE BUYER: A web app cannot send a real 302 redirect (ContentService/HtmlService can't set Location headers). You return HtmlService.createHtmlOutput() with a <script>window.top.location.href='<stripe url>'</script> or a meta-refresh, OR return the URL to your front-end and redirect there. This is a genuine limitation — the redirect is client-side JS, not an HTTP redirect. 4. STRIPE PROCESSES PAYMENT on its own hosted page. Your code is not involved. 5. WEBHOOK ARRIVES at doPost(e): Configure the Stripe dashboard webhook endpoint to your deployed /exec URL for events like checkout.session.completed. Stripe POSTs the raw JSON body plus a Stripe-Signature header. 6. VERIFY THE SIGNATURE — this is where most people fail. Apps Script's e object does NOT expose request headers. e.postData.contents gives you the raw body, but there is NO way to read the Stripe-Signature header inside doPost. Therefore native HMAC signature verification of Stripe's header is IMPOSSIBLE on a standard web app. Honest workarounds: (a) put a shared secret in the webhook URL query string (?token=...) and check e.parameter.token — weak, since the URL is a bearer secret; or (b) treat the webhook as untrusted and, on receipt, call BACK to Stripe with UrlFetchApp — GET https://api.stripe.com/v1/checkout/sessions/{id} or /v1/events/{id} using your secret key — and trust only Stripe's authenticated response, not the POST body. Option (b) is the correct, secure pattern: the incoming POST is merely a 'ping', and you re-fetch the truth from Stripe over an authenticated TLS call. Do this before fulfilling. 7. DEDUPE on the Stripe EVENT ID: Cold starts and Stripe's at-least-once delivery mean doPost can run twice concurrently or hours apart for the same event. Acquire LockService.getScriptLock().waitLock(30000). Inside the lock, check whether event.id already exists in your Sheet ledger (a sheet with columns: event_id, session_id, amount, status, server_timestamp). Use CacheService.getScriptCache() as a fast first-pass dedupe (get(event.id)) but NEVER trust cache alone — it expires (max 6h) and can evict; the Sheet is the durable ledger. If event.id is already present and processed, releaseLock and return 200 immediately (idempotent no-op). 8. FULFILL + RECORD atomically-ish: Still holding the lock, append the row (SpreadsheetApp appendRow or Sheets values.append), THEN perform fulfillment (grant access, send receipt via MailApp/GmailApp, etc.), THEN releaseLock(). Order matters: write the ledger row first so a crash mid-fulfillment still leaves a record you can reconcile. Return a 200-ish ContentService.createTextOutput() so Stripe stops retrying. 9. RECONCILE (trigger): Because you can't fully trust webhook delivery, install a time-driven trigger (ScriptApp.newTrigger, e.g. every 6h) that lists recent Stripe sessions/PaymentIntents via UrlFetchApp and back-fills any paid-but-unfulfilled rows. This is your safety net for dropped webhooks.

- SIGNATURE VERIFICATION IS THE #1 TRAP: Because doPost cannot read the Stripe-Signature header (Apps Script exposes only e.parameter/e.parameters/e.postData/e.pathInfo/e.queryString — no headers, no IP, no cookies), you CANNOT do Stripe's documented HMAC signature check. Anyone who learns your /exec URL can POST a fake 'payment succeeded' body. The ONLY safe fix is to re-fetch the event/session from Stripe with your secret key and trust that response — never trust the webhook body. People who skip this ship a free-money exploit. - NO NATIVE IDEMPOTENCY: Apps Script gives you nothing. You must build dedup yourself with LockService + a durable Sheet ledger keyed on event.id. CacheService alone is unsafe (it evicts and expires). - CONCURRENCY + COLD STARTS: A public web app has NO native rate-limiting and can spin up multiple concurrent executions for simultaneous webhook retries. Without LockService.waitLock you get double-fulfillment (two access grants, two receipts, or a double refund). waitLock can itself time out — handle the timeout and let Stripe retry rather than proceeding unlocked. - 6-MINUTE EXECUTION LIMIT: doPost must finish in under 6 minutes. If fulfillment is slow (many emails, external API calls), do the minimal durable write inside the request and offload heavy work to a trigger, or Stripe will see a timeout and retry — compounding the dedup problem. - THE REDIRECT ISN'T A REAL REDIRECT: No Location header is possible; it's client-side JS/meta-refresh. Pop-up blockers and window.top sandboxing (if your form is iframed) can silently break it. - FORM ENCODING, NOT JSON: Stripe's API rejects JSON. You must form-encode with bracket notation. A JSON.stringify payload is a common silent 400. - SECRET IN THE URL (webhook): Since you can't verify the signature, the fallback token lives in the webhook URL. That URL is now a bearer secret — it leaks in logs and Stripe's dashboard. The re-fetch pattern (option b) is safer because even a leaked URL only lets an attacker trigger a re-fetch, not forge a payment. - SHEET AS LEDGER IS OWNER-EDITABLE: The buyer/owner can hand-edit the ledger sheet. It is a reconciliation record, NOT a tamper-proof book of record. Stripe is always the source of truth; the Sheet is a cache of Stripe state. - REFUNDS/DISPUTES: Same webhook fragility applies to charge.refunded and charge.dispute.created — re-fetch and dedupe those too, or your fulfillment state drifts from Stripe.

- UrlFetchApp: ~20,000 calls/day on consumer, 100,000/day on Workspace. Every charge creation, every webhook re-fetch, and every reconciliation-trigger list call counts. The re-fetch-on-webhook pattern roughly DOUBLES your UrlFetch usage per payment (create + verify), plus reconciliation sweeps — budget accordingly; a high-volume seller can hit the consumer cap. - Trigger runtime: ~90 min/day total on consumer, ~6 hr on Workspace, with a 6-min-per-execution ceiling. The reconciliation sweep lives in this budget; keep each run short and paginated. - Email receipts via MailApp/GmailApp: ~100 recipients/day consumer, ~1,500 Workspace. High-volume checkout can exhaust the consumer email quota fast — send receipts via Stripe's own email or defer to a trigger, and note Workspace's 15x headroom. - Sheets-as-DB: tens of thousands of rows before it degrades. A busy payment ledger will outgrow a Sheet within months; plan to archive/rotate or move the ledger to a real DB via UrlFetchApp. - No native web-app rate limiting: a malicious actor can hammer doPost; each hit that triggers a re-fetch burns UrlFetch quota and can DoS your payment path. Add a cheap CacheService-based throttle.

- PCI scope is minimized by design: with Stripe Checkout, card data never touches Apps Script — the buyer enters it on Stripe's hosted page, so you stay in the lightest PCI SAQ tier. Do NOT collect raw card numbers into a Sheet; that would balloon PCI obligations and is a hard no on this stack. Using raw PaymentIntents with a custom card form (Stripe Elements) is fine because the card still tokenizes in the browser, never in your script. - The Stripe secret key and webhook secret sit in the OWNER's PropertiesService, inside their own Google account. Your agency never holds the client's Stripe credentials — good for trust and liability, but it also means YOU cannot centrally rotate or monitor them; document key rotation for the client. - The Sheet ledger is owner-editable and therefore NOT a legal book of record or audit-grade financial log — never market it as 'immutable' or 'forensic'. Stripe's dashboard is the authoritative financial record for accounting, taxes, and disputes. - Money-critical correctness is a real liability surface: a signature/dedup bug means real dollars double-charged, double-refunded, or fulfilled for free. Treat the reconciliation trigger and the Stripe-as-source-of-truth re-fetch as non-negotiable, and reconcile against Stripe payouts before trusting any Sheet-derived number. - Consider Stripe's own idempotency and its dashboard-side fraud tooling (Radar) rather than reimplementing fraud checks in Apps Script, which lacks IP/UA/header signals to score risk.

1. PROVISION (front-end, one-time): the static front-end creates a Sheet + bound Apps Script in the owner's Drive, and the generated script's appsscript.json declares oauthScopes: https://www.googleapis.com/auth/calendar (or the narrower calendar.events) plus script.external_request (UrlFetch for email/webhooks) and script.scriptapp (triggers). The owner authorizes on first run; the web app is deployed "Execute as: Me (owner), Access: Anyone". Config (calendar id, slot length, buffers, business hours per weekday, timezone, max days out) is written to the Sheet or PropertiesService. 2. doGet — RENDER AVAILABILITY. e.parameter carries the requested date-range and, critically, the VISITOR's IANA timezone + UTC offset, which the client page must send (from Intl.DateTimeFormat().resolvedOptions().timeZone) because the request object exposes no headers/locale — e.parameter/e.parameters/e.postData/e.pathInfo/e.queryString are all you get, no IP, no UA. The script calls CalendarApp.getCalendarById(id) then getEvents(start, end) — or better getEvents and inspect, but for pure busy blocks use the Calendar Advanced Service (Calendar.Freebusy.query) which returns busy intervals without reading event details/titles. Generate candidate slots by walking the owner's availability rules in the OWNER's timezone (Session.getScriptTimeZone() or a stored config tz), convert each slot boundary to absolute UTC instants, subtract any busy interval that overlaps (accounting for pre/post BUFFER minutes), then serialize slots as UTC ISO strings. The HtmlService/JSON payload renders them in the VISITOR's tz client-side. Store and compare everything as absolute UTC Date instants; never compare wall-clock strings. 3. doPost — BOOK WITH A LOCK. Visitor posts {slotStartUtc, name, email, tz}. Server: a. LockService.getScriptLock().waitLock(20000) — a script-wide mutex. This is the ONLY thing that prevents a double-booking race across two concurrent cold-start executions, because each request may spin up a fresh instance with no shared memory. b. Inside the lock, RE-QUERY Calendar.Freebusy.query (or getEvents) for that exact slot to confirm it is still free — the availability the visitor saw may be stale. Also re-validate the slot against the rules server-side (never trust the client's slot; recompute that slotStartUtc is a legal boundary, within business hours, within max-days-out, buffers respected). c. If free: CalendarApp.getCalendarById(id).createEvent(title, startUtcDate, endUtcDate, {guests: visitorEmail, sendInvites: true, description}) — this puts the event on the owner's calendar and (with guests) sends a native Google Calendar invite. Capture event.getId(). d. Persist a booking row to the Sheet: eventId, slotUtc, name, email, visitor tz, server timestamp (new Date() on the server), and a status. Use SpreadsheetApp with a header-mapped append. For idempotency, generate a booking token now. e. releaseLock() in a finally block. 4. SIGNED TOKENS for reschedule/cancel. There are no cookies/sessions. Mint an HMAC token = base64url(payload={eventId, exp}) + "." + base64url(Utilities.computeHmacSha256Signature(payload, SECRET)) where SECRET lives in PropertiesService (getScriptProperties). Email confirmation + "Reschedule"/"Cancel" links pointing back at the same web app URL with ?action=cancel&t=TOKEN. On that doGet, recompute the HMAC and constant-time compare, check exp, then look up eventId and CalendarApp...getEventById(eventId).deleteEvent() for cancel, or render a new slot picker for reschedule (delete + recreate under the same lock). This is the honest attribution model: a verified action authenticated by a signed token, not a captured IP. 5. REMINDERS via time-driven trigger. ScriptApp.newTrigger("sendReminders").timeBased().everyHours(1).create() at provision time. The handler scans the Sheet (or Calendar) for bookings ~24h/1h out that haven't been reminded, and sends via MailApp.sendEmail() / GmailApp; mark reminded=true to avoid resends. Note Google's own native event reminders also fire from the invite, so this is mainly for branded/SMS-webhook reminders. UrlFetchApp.fetch() can hit Twilio/an SMS provider or a CRM webhook. 6. CONFIRMATION email: MailApp.sendEmail({to, subject, htmlBody}) with the ICS-style details and the signed reschedule/cancel links. On Workspace with send-as configured, GmailApp can send from the business domain for deliverability.

- TIMEZONE is where naive builds die three ways: (a) don't store wall-clock strings — store absolute UTC instants and convert at render; (b) the OWNER's availability rules ("9–5 Mon–Fri") are in the owner's tz, so generate slots in owner-tz then convert to UTC, while the VISITOR sees them in visitor-tz — three timezones in play; (c) DST: a rule crossing a DST boundary can produce a missing or doubled wall-clock hour. Use the Calendar Advanced Service / real Date arithmetic, not hand-rolled offset math (a fixed +N offset breaks twice a year). Utilities.formatDate(date, tz, fmt) is the correct converter. - DOUBLE-BOOKING is NOT prevented by "check then create" — that's a TOCTOU race. Two visitors hitting the same slot land in two separate execution instances with no shared state; only LockService (a real cross-execution mutex) plus a RE-CHECK inside the lock is correct. Skipping the re-check inside the lock still double-books if the free/busy read happened before the lock. - COLD START ~1–2s means waitLock timeouts must be generous (15–20s); a slot re-query + createEvent + Sheet append can eat a couple seconds. LockService.waitLock throws if it can't acquire — handle it and return "please retry", don't 500. - NEVER TRUST CLIENT SLOTS: the client sends slotStartUtc; recompute legality server-side. Otherwise anyone can POST an off-hours or already-passed slot. - e.parameter is the ENTIRE input surface — no IP, no headers, no user-agent, no cookies. You cannot rate-limit by IP natively and cannot capture the booker's IP. A public web app has NO built-in rate limiting; a bot can spam doPost. Mitigate with a CAPTCHA/turnstile token you verify via UrlFetchApp, a PropertiesService/CacheService counter keyed by email, and the LockService serialization (which at least prevents corruption, not abuse). - Email verification: a typed email is unverified. If you need the booker's email confirmed, add a magic-link step (email a signed token, confirm on click) before writing — otherwise "attribution" is just self-asserted. - The owner's calendar/Sheet is OWNER-EDITABLE — the booking log is real data but NOT tamper-proof or forensic; don't market it as an immutable audit trail. - getEvents leaks event titles/details; prefer Calendar.Freebusy.query so the public page never touches private event content, and so an availability bug can't render someone's meeting titles. - Reschedule race: reschedule = delete old + create new, and the create must run under the same lock + re-check, or you can lose the slot between delete and create. - HTMLService iframe sandbox: the booking UI runs sandboxed; the visitor-tz must be captured in client JS and posted explicitly, and cross-origin embedding on the agency's marketing site needs the web app's XFrameOptions set to ALLOWALL.

- Email: consumer MailApp/GmailApp caps ~100 recipients/day; Workspace ~1,500/day. A busy booking page sending confirmation + 24h + 1h reminders burns 3 emails per booking — ~33 bookings/day exhausts a consumer account, ~500/day on Workspace. This alone is a real reason to steer higher-volume clients to Workspace. - Triggers: ~90 min/day total trigger runtime on consumer (~6 hr Workspace), max 6 min per execution, and only ~20 triggers per script. An hourly reminder-sweep is cheap; don't scan a huge Sheet every minute. - UrlFetch (SMS/webhook/CAPTCHA verify): ~20k/day consumer, 100k Workspace. - CalendarApp/Freebusy reads count against Calendar API quotas but are generous for this scale; the practical ceiling is the Sheet-as-DB — bookings in the tens of thousands of rows is fine, but a single global bookings Sheet degrades well before a real DB would. - No per-request rate limit means a traffic spike or bot can pile up executions and hit the concurrent-execution limit (~30 simultaneous), causing waitLock timeouts — size the lock timeout and surface ret#ries gracefully.

- Data residency/ownership is genuinely strong: the calendar, the event, and the booking Sheet all live in the CLIENT's own Google Drive — no third-party SaaS holds the customer PII. That's the real selling point over Calendly/Cal.com for privacy-sensitive SMBs. - But "owned" is not "compliant by default": the booking log is owner-editable, so make no immutability/forensic/audit-proof claims. For HIPAA-adjacent booking (clinics), the client must be on a Workspace edition with a signed Google BAA (Business Standard+), and you must avoid putting PHI in event titles/descriptions the public page or invited guests could see. - Consent/records: since you cannot capture IP/UA, any "consent record" is a server timestamp + typed name + (ideally) a magic-link-verified email — describe it as exactly that, not as a signed/witnessed legal record. - Sending confirmation email as the client's own domain (Workspace send-as) improves deliverability and trust vs. a generic no-reply, and keeps the whole flow first-party.

1. STORE A SECRET AT PROVISION TIME. When the front-end provisions the script, generate a random 32-byte secret and write it with `PropertiesService.getScriptProperties().setProperty('HMAC_SECRET', ...)`. Script Properties are per-script and not exposed to the public request object, so this is your signing key. (Scope: `script.storage`, implicit — no OAuth prompt for PropertiesService.) 2. REQUEST LINK (doPost). The static front-end POSTs `{email}` to the web app URL. In `doPost(e)` you read `e.parameter.email` (or parse `e.postData.contents` if you sent JSON). Normalize/lowercase it. Optionally check it against an allowlist Sheet (`SpreadsheetApp.openById(...).getSheetByName('Clients')`) so only known clients get links. 3. MINT A SIGNED TOKEN. Build a payload string, e.g. `email + '|' + exp` where `exp = Date.now() + 15*60*1000` (server clock via `new Date()` / `Date.now()`). Compute `var sig = Utilities.computeHmacSha256Signature(payload, secret)` — this returns a `Byte[]`. Base64url-encode both: `Utilities.base64EncodeWebSafe(payload)` and `Utilities.base64EncodeWebSafe(sig)`. The token is `payloadB64 + '.' + sigB64`. Nothing secret is in the token; the signature is what makes it unforgeable. 4. EMAIL THE MAGIC LINK. Send `WEBAPP_URL + '?token=' + token` (or `?p=...&s=...`) via `MailApp.sendEmail(email, subject, body)` (scope `script.send_mail`, no From control) or `GmailApp.sendEmail(...)` if you want the owner's Gmail identity / send-as. On Workspace you can send-as the business domain for deliverability. This is the ONLY delivery channel that proves the person controls the mailbox — that mailbox possession is your entire identity proof. 5. VERIFY (doGet). The link hits `doGet(e)`. Read `e.parameter.token`. Split on `.`, base64url-decode the payload, recompute HMAC over the payload with the same secret, and compare to the provided signature byte-by-byte (constant-time-ish loop — don't `==` the strings lazily; length-check then XOR-accumulate). Reject if mismatch. Then parse `exp` and reject if `Date.now() > exp`. This is stateless verification: no DB lookup needed to know the token is authentic and unexpired. 6. ISSUE A SESSION. On success, generate a fresh random session id (`Utilities.getUuid()`), and store a session record server-side keyed by that id: either `CacheService.getScriptCache().put(sid, JSON.stringify({email, exp}), 21600)` (max 6 h TTL) for speed, and/or append a row to a `Sessions` sheet (`sheet.appendRow([sid, email, created, expires])`) for durability/audit. Return the sid to the browser. Because you have no cookies, the browser must carry the sid itself — store it in `localStorage` (the web app is served in an iframe under `script.googleusercontent.com`, so localStorage works) and send it as a `?sid=` parameter or POST field on every subsequent call. 7. AUTHENTICATE SUBSEQUENT REQUESTS. Each later `doGet`/`doPost` reads `e.parameter.sid`, looks it up in Cache (fast path) then Sheet (fallback), checks expiry, and serves data. Single-use hardening: mark the magic-link token consumed by recording a `jti` (a nonce you put in the payload) in Cache/Sheet on first use and rejecting replays.

- NO COOKIES, NO HEADERS, NO SET-COOKIE. The request object only exposes `e.parameter(s)`, `e.postData`, `e.pathInfo`, `e.queryString`. You cannot set a real HTTP cookie, cannot read `Authorization`, cannot read Referer. So "session cookie" is impossible — the session id MUST travel in the URL/body and be held in browser localStorage. Treat any URL containing a live token/sid as a bearer credential (browser history, proxies, shoulder-surfing all leak it). - NO IP / NO USER-AGENT. You cannot rate-limit by IP or fingerprint the device natively. Someone can hammer `doPost` to blast magic-link emails at arbitrary addresses (email-bomb / your-quota-drain). Mitigate with a Sheet/Cache counter per email+minute and an allowlist — but there is no true per-IP throttle on a public web app. - `computeHmacSha256Signature` RETURNS SIGNED BYTES (`Byte[]`, values -128..127). Don't compare raw byte arrays as strings — base64url-encode before comparing, and do a length check plus constant-time comparison to avoid timing leaks. Naive early-return string equality is the classic mistake here. - CLOCK IS SERVER-SIDE ONLY. `exp` uses `Date.now()` on the owner's script runtime — fine and trustworthy. Never trust an expiry the client sends; it's inside the signed payload precisely so the client can't move it. - "RUNS AS OWNER" ≠ IDENTITY. Deploy the web app as "execute as me (owner), access: anyone". Then `Session.getActiveUser().getEmail()` is EMPTY for anonymous visitors — you get no Google identity for free. Your magic link IS the identity; don't expect Apps Script to tell you who's calling. (If you instead deploy "execute as user accessing", every visitor must have a Google account and OAuth-consent — wrong model for a public client portal.) - OWNER CAN READ EVERYTHING. Sessions sheet, the HMAC secret in Script Properties, and all portal data live in the client-owner's own Drive. This is fine for a single-tenant portal the owner runs, but it means the backend is NOT tamper-proof or zero-knowledge — the owner can forge a session row or read the secret. Don't market this as "we can't see your data." - STATELESS TOKEN vs STATEFUL SESSION. The magic-link token is stateless (verifiable by signature alone) — good, because Cache can evict. The session sid should be stateful (looked up), so you can revoke it. If you make the session itself a bare HMAC token with no server record, you lose revocation and single-use. - CACHE IS NOT DURABLE. `CacheService` entries can be evicted before TTL and are capped (100 KB/value, 6 h max TTL). Use it as a fast layer but back sessions with the Sheet if you need them to survive; otherwise a user gets logged out unpredictably. - CONCURRENCY ON THE SHEET. Two requests appending/reading the Sessions sheet can race. Wrap writes in `LockService.getScriptLock()` with a short `waitLock`. The Sheet-as-DB also degrades past tens of thousands of rows — prune expired sessions on a time-driven trigger. - COLD START ~1-2s. First hit after idle is slow; the magic-link click may feel laggy. Not a correctness issue, but set expectations.

- EMAIL IS THE BINDING QUOTA. `MailApp`/`GmailApp` sends are capped at ~100 recipients/day on consumer Gmail, ~1,500/day on Workspace. Every magic-link request = one email, so a consumer-account portal tops out around ~100 logins/day and is trivially exhaustible by an email-bomb attacker — hard-gate with an allowlist and per-email cooldown. Workspace's 15x headroom materially changes what's viable. - TRIGGER RUNTIME: pruning expired sessions/tokens via a time-driven trigger draws from ~90 min/day total trigger runtime (consumer) vs ~6 h (Workspace); 6 min per execution. Keep the prune job small/batched. - No UrlFetch needed for the core flow (all crypto is local via `Utilities`), so the ~20k/day UrlFetch quota is irrelevant unless you add an external email provider or captcha. - CacheService and PropertiesService reads/writes are effectively free and fast; prefer Cache for the hot session-check path to avoid Sheet read latency.

- ATTRIBUTION HONESTY: you can prove "someone who controls this mailbox clicked this link at this server timestamp" — that's it. You CANNOT capture the signer's IP or user-agent on this stack, so don't claim IP-based audit trails. Attribution = verified email + server `Date.now()` + whatever the user typed. Adequate for portal access; NOT sufficient for high-assurance e-signature or KYC claims. - MAGIC LINKS ARE BEARER TOKENS: anyone who obtains the link (forwarded email, shared screen, corporate email scanner that pre-fetches URLs — a real problem, some scanners GET the link and can burn a single-use token) is authenticated. Single-use + short expiry mitigate but don't eliminate this. Warn users not to forward the link. - DATA RESIDENCY / PRIVACY: all session and client data sit in the OWNER's Google Drive under their Google account, not a third-party server — a genuine privacy selling point for agencies delivering owned backends. On Workspace Business Standard+ this can sit under a BAA (HIPAA-eligible), unlike consumer Gmail. But reiterate: owner-editable ≠ immutable; do not represent the session log as a forensic/tamper-proof record.

1. PROVISION THE CRON (one-time, at setup or from doGet/doPost admin action). In the generated script, call `ScriptApp.newTrigger('drainQueue').timeBased().everyMinutes(5).create()` (choices are everyMinutes(1|5|10|15|30), everyHours(n), everyDays(n), or atHour/nearMinute). This requires the `https://www.googleapis.com/auth/script.scriptapp` scope, which the owner authorizes at install. The trigger runs AS the owner — same identity/quotas as the web app. IMPORTANT: there is no cron-string syntax; you get the fixed intervals above. A trigger can also be created dynamically at the end of a run (`ScriptApp.newTrigger(...).timeBased().after(60*1000).create()`) to self-reschedule for finer control — but you must delete stale triggers or you hit the ~20-trigger-per-script/per-user cap. 2. HOLD A DURABLE QUEUE + CURSOR. The work list (e.g. recipients to email) lives in a Sheet tab via `SpreadsheetApp` (scope `.../auth/spreadsheets`) or serialized in `PropertiesService.getScriptProperties()` (scope `.../auth/script.storage`, 500KB total / 9KB per value cap — so Sheet is the real store for anything sizable). Track a cursor: last processed row index, plus a per-row status column (`QUEUED`/`SENT`/`FAILED`/`timestamp`). PropertiesService holds the small scalar cursor (`lastRow`); the Sheet holds per-row state so a crash mid-batch is recoverable and idempotent. 3. TAKE THE LOCK. First line of `drainQueue`: `const lock = LockService.getScriptLock(); if (!lock.tryLock(1000)) return;` (scope `.../auth/script.locks`). Because a 5-min trigger can fire while the previous run is still grinding through a slow chunk, overlapping executions would double-send. The lock makes runs mutually exclusive; a run that can't grab it exits immediately and waits for the next tick. 4. READ LIVE EMAIL QUOTA. `const budget = MailApp.getRemainingDailyQuota();` returns the integer recipients still allowed today for THIS account (consumer ~100, Workspace ~1,500; it counts recipients, not messages). Scope: `.../auth/script.send_mail` (MailApp) or the broader Gmail scope if using `GmailApp`. If `budget <= 0`, release the lock and return — the queue simply resumes tomorrow when Google resets the counter. 5. CHUNK AGAINST BOTH THE TIME WALL AND THE QUOTA. Record `const start = Date.now()`. Loop over queued rows, and STOP the loop when ANY of: (a) `Date.now() - start > 4.5*60*1000` (leave a safety margin below the hard 6-min `Exception: Exceeded maximum execution time` kill — that exception is uncatchable-for-cleanup in practice, so you must bail early to flush your cursor), (b) `sentThisRun >= budget` (never exceed live remaining quota), or (c) an optional self-imposed per-run cap (e.g. 50) to stagger deliverability and keep runs short. 6. SEND + MARK ATOMICALLY-ISH. For each row: `MailApp.sendEmail({to, subject, htmlBody})` (or `GmailApp.sendEmail` if you need threading/labels/`from` send-as on Workspace). Immediately write the row's status to `SENT` + `new Date()` in the Sheet BEFORE moving on, and advance the PropertiesService cursor. Wrap each send in try/catch; on failure mark `FAILED` with the error string and continue — one bad address must not stall the whole queue. Because you commit status per-row, a mid-chunk timeout only re-attempts unsent rows. 7. FLUSH + RELEASE. After the loop: persist final cursor to PropertiesService, `SpreadsheetApp.flush()` to force pending Sheet writes to disk (Apps Script batches writes — an execution killed at 6 min can otherwise lose un-flushed changes), then `lock.releaseLock()`. The standing 5-min trigger fires again and step 3 onward repeats until no `QUEUED` rows remain. 8. (OPTIONAL) SELF-DISABLE / CLEAN UP. When the queue is empty you can leave the cron running idle (it just takes the lock, sees 0 rows, exits — cheap) or delete it via `ScriptApp.getProjectTriggers().forEach(t => { if (t.getHandlerFunction()==='drainQueue') ScriptApp.deleteTrigger(t); })` to reclaim the trigger slot. EXTERNAL APIs: if sending via a third party instead of Gmail (SendGrid/Postmark/Resend) you swap step 6 for `UrlFetchApp.fetch(apiUrl, {headers:{Authorization:'Bearer '+key}, ...})` (scope `.../auth/script.external_request`, key in PropertiesService). That escapes the Gmail 100/1500 recipient cap entirely but is bounded instead by the UrlFetch quota (~20k/day consumer, 100k Workspace) and your ESP's own rate limits — and you lose `getRemainingDailyQuota()` as your throttle signal, so you must track sends yourself in PropertiesService.

- THE 6-MINUTE WALL IS A HARD KILL, NOT A WARNING. When execution hits ~6 min (Workspace paid tiers historically got 30 min but Google has been normalizing this toward 6 — do not design assuming 30), the runtime throws `Exceeded maximum execution time` and stops. You cannot reliably run cleanup after it. The ONLY safe pattern is: measure elapsed time in the loop and voluntarily exit at ~4.5 min with your cursor flushed. People who "just loop over 5,000 rows" get a partial send and a lost cursor. - FLUSH OR LOSE WRITES. `Range.setValue`/`setValues` are buffered. If the run dies before an implicit or explicit `SpreadsheetApp.flush()`, the last writes vanish and rows get re-sent next tick → duplicate emails. Flush after each chunk (or per-row for max safety, at a throughput cost). - WITHOUT A LOCK YOU WILL DOUBLE-SEND. Triggers can overlap: a 5-min interval firing while the prior run is still inside its 4.5-min window means two executions read the same `QUEUED` rows. `LockService` is mandatory, not optional. - `getRemainingDailyQuota()` COUNTS RECIPIENTS, AND IT'S SHARED. The ~100/1,500 cap is per-account across ALL Apps Script + Gmail-API sending for that user that day, including CC/BCC as separate recipients. It resets on a rolling ~24h Google clock (roughly midnight Pacific), NOT at the user's local midnight. Don't assume a fresh 100 at 12:01am local. - QUOTA EXHAUSTION THROWS, it doesn't return false. If you call `sendEmail` past the limit you get `Exception: Service invoked too many times for one day: email`. Always gate on `getRemainingDailyQuota()` BEFORE sending, and treat the throw as a hard stop that ends the run gracefully. - THE ~90-MIN/DAY (consumer) / 6-HR (Workspace) TOTAL TRIGGER RUNTIME CEILING IS SEPARATE from the per-execution 6 min and from the email cap. Many short runs still accrue against it. A 5-min cron doing near-zero work barely touches it; a cron that legitimately grinds 4.5 min every 5 min will exhaust 90 min/day in ~20 runs (~100 min of wall clock) and then Google silently stops firing your triggers for the rest of the day. Size chunks so total daily compute stays well under the ceiling. - TRIGGER COUNT IS CAPPED (~20 per script per user). The self-rescheduling `.after()` pattern MUST delete the trigger that just fired or you leak slots and hit "This script has too many triggers." - NO SUB-MINUTE CRON, NO GUARANTEED PUNCTUALITY. Minimum interval is 1 minute and Google may fire a "1-minute" trigger anywhere in a fuzzy window, or skip/coalesce under load. Never build anything needing exact-time execution. - YOU CANNOT ATTRIBUTE A SEND TO A REQUEST IP/HEADER. Unrelated but often assumed: the web-app request object exposes only `e.parameter/parameters/postData/pathInfo/queryString` — no IP, headers, cookies, or UA. Any per-recipient audit trail must be built from server `new Date()` timestamps and the row data itself. - TRIGGERS DIE IF THE OWNER LOSES ACCESS / REVOKES OAUTH, and Google auto-disables triggers that error repeatedly, emailing the owner a failure summary. A queue can silently stall; build a "last successful run" heartbeat timestamp the front-end can surface.

Email recipients/day: consumer ~100, Workspace ~1,500 (per account, all Apps Script sending combined, counts every recipient incl. CC/BCC; check live via MailApp.getRemainingDailyQuota(); resets ~midnight Pacific). Per-execution: 6 min hard kill — design to exit ~4.5 min. Total trigger runtime/day: consumer ~90 min, Workspace ~6 hr (accrues across all runs; exhaustion silently halts triggers till reset). Triggers: min 1-min interval, fuzzy timing, ~20/script/user. PropertiesService: 500KB total, 9KB/value — use a Sheet for real queues. UrlFetch (external ESP path): ~20k/day consumer, 100k Workspace, plus the ESP's own limits. Cold start ~1-2s on first run after idle. Sheets-as-queue realistically tops out in the tens of thousands of rows before scan latency and the 6-min wall bite — keep a numeric cursor, don't re-scan.

Sends originate from the OWNER's own Google account and count against the owner's quota and reputation — so a batch blast can consume the client's entire day of business email and get their domain flagged; stagger and cap conservatively, and never let one tenant's queue starve the account. On consumer Gmail, all mail sends from the user's personal address (no send-as the business domain, weaker deliverability); Workspace unlocks `GmailApp` send-as an authorized domain alias plus the 15x cap, which is the honest reason to push agency clients to Workspace for any real volume. Bulk/marketing sends are subject to CAN-SPAM / CASL / GDPR obligations (accurate from-line, physical address, working unsubscribe, consent) — the platform can provide an unsubscribe link + suppression list in the Sheet, but compliance is the sending client's legal responsibility, not something Apps Script enforces. Finally: the queue Sheet lives in the owner's Drive and is owner-editable — it is a real audit log but NOT tamper-proof or immutable, so do not market it as forensic-grade proof of what was sent.

1) SCOPE DECLARATION & CONSENT. The generated script's OAuth manifest (appsscript.json oauthScopes, or auto-inferred from the methods it calls) declares https://www.googleapis.com/auth/gmail.readonly OR the broader https://mail.google.com/ / gmail.modify. GmailApp is coarse: reading a body needs at least gmail.readonly, and gmail.readonly grants read of the ENTIRE mailbox — there is no folder/label-scoped grant. Creating drafts or applying labels needs gmail.modify (or the full mailbox scope). On the OAuth consent screen the user sees 'Read, compose, send, and permanently delete all your email from Gmail' (or 'Read all your email') — these are RESTRICTED scopes: an unverified app is capped at ~100 users and shows a scary warning; going past that requires Google's annual CASA/security assessment (paid, thousands of dollars). This is the single biggest wedge and cost of this primitive. 2) TRIGGER. A time-driven trigger (ScriptApp.newTrigger('triageInbox').timeBased().everyMinutes(5).create(), free cron) fires the run; there is NO native 'on new email' push trigger in Apps Script for arbitrary scripts, so you POLL. (Gmail push/watch via the Advanced Gmail Service + Pub/Sub exists but is heavy and rarely worth it here.) 3) SEARCH. GmailApp.search('label:inbox is:unread newer_than:1d subject:invoice', 0, 50) returns GmailThread[] using standard Gmail query syntax. Page with the start/max args; cap max at ~50-100 threads per run to stay under the 6-min execution limit. Persist a cursor (last processed internalDate or a processed-label) in PropertiesService so each run only touches new mail and you don't reprocess. 4) READ BODIES. For each GmailThread call .getMessages(); per GmailMessage use .getPlainBody() (prefer over .getBody() which is raw HTML), .getFrom(), .getSubject(), .getDate() (server-side Date), .getId(), and .getAttachments() -> Blob for PDFs/images. Note getPlainBody can be empty on HTML-only mail; fall back to stripping .getBody(). 5) EXTRACT. Two paths: (a) REGEX/string parse in-script for cheap, deterministic fields (order numbers, amounts, dates) — no external call, no cost, no data leaves Google; (b) LLM parse via UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {method:'post', headers:{'x-api-key': PropertiesService.getScriptProperties().getProperty('ANTHROPIC_KEY'), 'anthropic-version':'2023-06-01'}, contentType:'application/json', payload: JSON.stringify({model, max_tokens, messages:[{role:'user', content: emailBody + extraction-instructions}]})}) — send the body, ask for JSON, JSON.parse the returned text. The API key lives in PropertiesService (Script Properties), never in code. This ships raw email content to a third-party API — a real trust/compliance event (see legal notes). 6) WRITE TO SHEET. Open the bound/owned sheet with SpreadsheetApp, collect rows in an array, and do ONE sheet.getRange(...).setValues(rows) batch write per run (never setValue in a loop — it's orders of magnitude slower and burns the time budget). Store the Gmail message getId() so you can dedupe and deep-link back (https://mail.google.com/mail/u/0/#all/<id>). 7) ACT — DRAFT vs SEND vs LABEL. To triage without touching the send cap: GmailApp.createDraft(recipient, subject, body) or message.createDraftReply(body) / thread.createDraftReply(body) — DRAFTS DO NOT COUNT against the daily send quota and land in the owner's Drafts for human review/one-click send. To file: thread.addLabel(GmailApp.getUserLabelByName('Triaged') || GmailApp.createLabel('Triaged')), thread.markRead(), thread.moveToArchive(). Only GmailApp.sendEmail / message.reply / draft.send actually SEND and consume the send quota.

- NO LABEL-SCOPED READ: gmail.readonly / gmail.modify grant the WHOLE mailbox. You cannot restrict the grant to one label or one sender; the app is technically able to read everything the moment it's authorized. Do not claim otherwise to a client. - RESTRICTED-SCOPE VERIFICATION WALL: these are the highest-sensitivity Gmail scopes. Unverified = ~100-user cap + interstitial warning; scaling requires Google verification AND an annual CASA Tier-2 security assessment (real money, real time). Because THIS stack provisions into the END USER's OWN account and the script runs AS them, each user is authorizing their own copy — that sidesteps the aggregate-user verification wall in the common self-provisioned case, but the user still sees the full scary consent screen and any shared/central OAuth client is still subject to it. Be precise about which deployment model you're in. - NO PUSH TRIGGER: you poll on a timer; there's inherent latency (whatever your trigger interval is) and you MUST track a cursor or you'll reprocess/duplicate. 'onNewEmail' does not exist for plain Apps Script. - getPlainBody() can be empty for HTML-only senders; .getBody() is HTML you must strip. Quoted-reply text and signatures pollute regex and LLM extraction — trim before parsing. - 6-MINUTE EXECUTION LIMIT (consumer): a large backlog + per-email UrlFetch to Claude will blow the limit. Batch small (e.g. 20-50 threads/run), let the timer catch up over successive runs, and make the run idempotent (label-as-processed) so a mid-run timeout doesn't corrupt state. - Sheet writes: batch setValues once; per-cell writes will time you out. - The mailbox is the owner's live inbox — markRead/archive/label mutate their real mail. Test on a label subset; a bad query is destructive to their workflow. - 'Capture the sender's IP/headers' is impossible here: this is READING existing Gmail messages, and even the raw RFC822 (message.getRawContent()) only gives you the headers the SENDER's path already wrote — you cannot observe anything Gmail didn't record. And the web-app request object never exposes client IP/headers regardless. - LLM extraction is non-deterministic: pin the model, force JSON output, and validate/JSON.parse defensively — a malformed response should skip the row, not crash the whole run.

- Trigger runtime: ~90 min/day total across all triggers (consumer) vs ~6 hr (Workspace); 6 min per single execution (both). Polling every 5 min = 288 runs/day, so keep each run well under a minute of work. - UrlFetch to Claude: ~20,000 calls/day consumer, 100,000 Workspace. One call per email is the natural unit — a busy inbox can approach the consumer ceiling; batch multiple emails into one Claude call to conserve. - SEND cap only matters if you actually send: ~100 recipients/day consumer, ~1,500 Workspace. DRAFTS created via createDraft/createDraftReply are FREE — they never touch the send quota, which is exactly why draft-and-review is the safe default for this primitive. - Sheet as DB: fine for tens of thousands of extracted rows; roll to a new sheet/tab or archive before that. - Anthropic API is your own metered cost (tokens), entirely separate from Google quotas.

- Trust cost is the headline: authorizing this app means granting an app the ability to read (and with modify, alter) the person's ENTIRE Gmail. For an SMB client that's their whole business correspondence. The honest framing to a client is 'this runs inside YOUR Google account, as YOU, and the code is yours' — true and reassuring — but the consent screen still says 'read all your email'. - Sending email bodies to the Claude API (step 5b) exfiltrates potentially sensitive/PII/PHI content to a third party. On consumer Gmail that may violate the client's own obligations. On Workspace with HIPAA in scope, you need a Google BAA (Business Standard+) AND a BAA with Anthropic before any PHI touches the API — otherwise keep extraction to in-script regex only, where nothing leaves Google. - The Sheet of extracted data lives in the owner's Drive and is owner-editable — do NOT market it as immutable, forensic, or tamper-proof. - Google's Limited Use / restricted-scope policy governs Gmail data: you generally cannot use the mailbox content to train models or for unrelated purposes, and human review of Gmail data is tightly restricted — relevant if any support/debugging path could expose a client's email to your team.

1. PROVISION AS AN ADMIN, NOT A REGULAR USER. The generated Apps Script project must be created/owned by (or the web app authorized by) a Google Workspace user who holds a super-admin role (or a custom admin role scoped to the specific privileges: User Management, Groups, Reports, etc.). Consumer Google accounts have no Admin Console, no org units, no domain — so the Admin SDK simply does not exist for them. This is not a quota difference; the API returns no domain to operate on. 2. ENABLE THE ADVANCED SERVICE + UNDERLYING API. In the Apps Script project, enable the "Admin SDK API" advanced service (this exposes the `AdminDirectory` and `AdminReports` globals). Because advanced services are thin wrappers over the REST APIs, the corresponding APIs must also be enabled in the linked Google Cloud project (Admin SDK API). For a standalone-provisioned script this means the script is bound to a real GCP project, not the default hidden one. 3. DECLARE THE EXACT OAUTH SCOPES in appsscript.json `oauthScopes`. Directory writes: `https://www.googleapis.com/auth/admin.directory.user` (create/suspend/delete users, reset passwords), `https://www.googleapis.com/auth/admin.directory.group` and `.../admin.directory.group.member` (group + membership management), `https://www.googleapis.com/auth/admin.directory.orgunit` (move users between OUs), `https://www.googleapis.com/auth/admin.directory.user.security` (revoke tokens/app passwords). License reclaim needs the separate Enterprise License Manager API scope `https://www.googleapis.com/auth/apps.licensing` via `AdminLicenseManager` (or UrlFetch to the Licensing REST endpoint). Audit pull: `https://www.googleapis.com/auth/admin.reports.audit.readonly` and `.../admin.reports.usage.readonly`. 4. ADMIN CONSENTS. When the admin first runs the script (or the web app on their behalf), the OAuth consent screen lists these high-privilege scopes; the admin must authorize. The token that results carries admin authority — everything the script does thereafter runs AS that admin. On restricted/sensitive scopes, the Cloud project usually needs to be verified or the app marked Internal (published to the same Workspace) to avoid the "unverified app" block. 5. DIRECTORY WRITES. Create a user: `AdminDirectory.Users.insert({primaryEmail, name:{givenName,familyName}, password, changePasswordAtNextLogin:true})`. Suspend/offboard: `AdminDirectory.Users.update({suspended:true}, email)`. Move OU: same `update` with `orgUnitPath`. Add to group: `AdminDirectory.Members.insert({email, role:'MEMBER'}, groupKey)`. All are synchronous REST round-trips wrapped in `UrlFetch`-style calls under the hood; each counts against Directory quotas. 6. LICENSE RECLAIM. On offboarding, after suspend, call `AdminLicenseManager.LicenseAssignments.remove(productId, skuId, userId)` to free the paid seat (or the equivalent Licensing REST DELETE via `UrlFetchApp`). Suspending a user does NOT auto-free the SKU — you must explicitly delete the license assignment. 7. AUDIT / REPORTS PULL. `AdminReports.Activities.list(userKey='all', applicationName='login'|'drive'|'admin'|'token', {startTime, maxResults})` returns paginated activity events; `AdminReports.CustomerUsageReports.get(date)` returns aggregate usage. Reports are eventually consistent — Google documents ingestion lag (login events ~hours, some apps up to ~2-3 days), so "real-time audit" is a lie on this API. 8. TRIGGER THE WORK. Wire it as either (a) a public web app `doPost` that an internal admin dashboard (your static front-end) calls with a shared secret in PropertiesService — but note the web app exposes no client IP/headers, so authenticate the caller by a secret token in `e.parameter`/`e.postData`, not by network identity — or (b) a time-driven trigger (`ScriptApp.newTrigger('nightlyReclaim').timeBased().everyHours(6)`) that runs offboarding/reclaim/audit-sync on Google's free cron. Store the admin's authorization by having the admin be the trigger owner. Secrets (shared API token, Cloud project creds) live in `PropertiesService.getScriptProperties()`.

- IMPOSSIBLE ON CONSUMER, FULL STOP. No Admin Console, no domain, no OU, no Admin SDK. There is no partial/degraded version — a @gmail.com account cannot create users or read a directory of anyone but itself. Do not design a "works on consumer, better on Workspace" story here; it is Workspace-exclusive. - THE SCRIPT RUNS AS A SUPER-ADMIN — THIS IS THE WHOLE RISK. A public `doPost` web app that wraps Directory.Users.insert is, functionally, a user-creation and account-takeover endpoint exposed to the internet. Apps Script gives you NO native rate limiting and NO IP allowlist on a public web app. If the shared secret leaks (it will end up in your static front-end's network calls / browser), an attacker can create admin users or suspend the CEO. You MUST: keep the endpoint access-controlled at the token layer, prefer time-driven triggers over public web endpoints for destructive ops, and scope the authorizing admin to a CUSTOM admin role with only the needed privileges rather than full super-admin. - CUSTOM ADMIN ROLE, NOT SUPER-ADMIN. Naive builders authorize with a super-admin. Correct design: create a custom admin role granting only User Management / Groups / Reports as needed, assign it to a dedicated service-owner account, and have THAT account own the script. Blast radius shrinks accordingly. - "RUN AS OWNER" MEANS ONE HARDCODED IDENTITY. A web-app-deployed script executes as its owner, not the caller. So multi-admin delegation, per-admin audit trails, and "who actually clicked suspend" are NOT captured by Google's admin audit as distinct humans — the Admin console will show the script-owner account doing everything. You lose per-operator attribution unless you log the calling operator yourself (and that self-log is owner-editable, so not tamper-proof). - DOMAIN-WIDE DELEGATION IS A DIFFERENT, HEAVIER MECHANISM. If you need to impersonate arbitrary users (act as them, not just administer them), that requires a service account with domain-wide delegation configured in the Admin Console — which Apps Script advanced services do NOT give you by default; you'd hand-roll JWT auth via `UrlFetchApp`. Admin SDK administration ≠ user impersonation. - CONSISTENCY/PROPAGATION LAG. Directory writes are not instantly globally consistent; a user just created may 404 on immediate read. Reports API lags hours-to-days. Build for eventual consistency; don't assert real-time. - 6-MINUTE EXECUTION CEILING. Bulk operations (onboard 500 users, page through months of audit logs) will blow the 6-min/execution limit. You must checkpoint progress in PropertiesService and continue on the next trigger fire; a single run cannot process a large directory. - ENABLING THE API IS TWO STEPS. Turning on the advanced service in the editor is not enough on a standalone-provisioned project — the bound GCP project must also have the Admin SDK / Licensing APIs enabled, or calls fail with a permission error that looks like a scope problem but isn't.

- Admin SDK Directory API default quota is on the order of ~2,400 queries/min/account (and a daily cap) — generous for SMB directories but a bulk backfill paging thousands of users can still throttle; implement exponential backoff on 403 rateLimitExceeded / 429. - Reports API has its own lower per-project/per-user query limits and, more importantly, DATA LATENCY (login events surface in hours; some app activity up to ~2-3 days) — this is a freshness constraint, not just a rate one. - Apps Script platform limits still apply on top: 6 min per execution, ~90 min/day total trigger runtime on consumer but this is Workspace so ~6 hr/day, and UrlFetch ~100k/day on Workspace if you bypass advanced services and hit REST directly. - License reclaim (Licensing API) has modest write quotas; batch offboarding of many seats should be spread across trigger runs.

- This primitive hands your generated code the keys to the client's entire org: creating users, suspending accounts, reading who-did-what audit logs. That is a materially different trust posture than a Sheet-backed form. Sell it as such and get explicit admin sign-off on the exact privileges. - Admin Console audit will attribute all script actions to the single script-owner identity, so YOUR change log is the only per-operator record — and because it lives in the owner's own Drive/Sheet it is owner-editable and therefore NOT forensic/immutable. Do not market it as a tamper-proof audit trail. - On Business Standard+ this can fall under a signed BAA if it touches PHI in Reports/Directory data; confirm the specific SKU is HIPAA-eligible and that Admin SDK usage is in scope before making any compliance claim. - Least-privilege is a legal/trust obligation here, not a nicety: authorize via a scoped custom admin role, store the shared secret only in PropertiesService, and never let the destructive endpoint be reachable with a secret that ships to the browser. A leaked token on this primitive is an org-compromise incident.