apps-script

name	apps-script
description	Google Apps Script development best practices learned from production. Use when building Apps Script automation, payment API integrations, Google Sheets menu functions, modal dialogs, Poka Yoke quality gates, or running Apps Script functions from CLI. Covers UI dialog philosophy, API field discovery, submission architecture (source_amount vs transfer_amount), clasp CLI logging, run-appscript.sh function execution, and gate flow design.
user-invocable	false

Apps Script Best Practices (Learned Lessons)

NOT generic best practices. Specific philosophies discovered through production experience.

UI Dialog Philosophy

Modal Dialogs Block the Sheet

• ui.alert() and ui.showModalDialog() block user from interacting with the sheet
• NEVER tell user to "go check the sheet, then click YES" — the dialog is blocking them
• Gate checks BEFORE dialogs: If user needs to do something in the sheet first (check a column, fill data), verify it's done BEFORE showing the dialog. If not done, exit with instructions — don't block with a modal

Gate Flow (Correct)

1. Read sheet context
2. Check gate → FAIL EARLY with specific names + column letter
3. Do expensive work (API calls, plan generation)
4. Show results + confirmation

Gate Flow (Wrong)

1. Do expensive work
2. Show blocking modal asking user to check the sheet
3. User can't check sheet because modal is blocking
4. Check gate after user clicks YES — too late

Dialog Content Rules

• Don't show counts without names. "7 missing" is useless — list the actual names
• Don't show information that's inherently obvious (e.g., pre-submission "not yet in Airwallex" for employees about to be submitted)
• Show only actionable information
• Tables with monetary columns MUST include a total row (count + sum). No exceptions — user always needs the total
• Currency formatting: USD → $X.XX, non-USD → CUR X.XX. Never show USD 627.00 — use $627.00. Use a shared formatter (e.g., fmtAmt) to prevent branch-specific inconsistencies

API Amount Fields — Don't Guess Names

Discovery Protocol

When integrating with any payment/transfer API:

1. Log ALL fields from the first API response: Object.keys(response).sort().join(', ')
2. Log amount-related fields specifically: filter keys matching /amount|fee|cost|charge|rate|price|settle/i
3. Use clasp logs to read execution logs from CLI
4. Field names are often surprising (e.g., Airwallex uses amount_payer_pays not source_amount)

Diagnostic Logging Pattern

if (items.length > 0) {
  const sample = items[0];
  Logger.log(`[DIAG] Fields: ${Object.keys(sample).sort().join(', ')}`);
  const amountFields = Object.entries(sample)
    .filter(([k]) => /amount|fee|cost|charge|rate|price|settle/i.test(k))
    .map(([k, v]) => `${k}=${JSON.stringify(v)}`)
    .join(', ');
  Logger.log(`[DIAG] Amount fields: ${amountFields}`);
}

Don't Round-Trip Through GOOGLEFINANCE

• If you convert USD->NGN via GOOGLEFINANCE at submission, converting NGN->USD via GOOGLEFINANCE for verification gives back the original amount. This is circular and useless
• The API response has the actual charged amount. Find that field and use it directly
• GOOGLEFINANCE rate != provider's FX rate — there's always a spread

Submission Architecture — Source Amount vs Transfer Amount

Who Controls the Conversion?

• USD-sourced cross-currency: Let the payment provider handle conversion. Send source_amount (exact USD to pay). Don't care what target currency amount recipient gets
• ILS-sourced (or non-standard source): You DO care because you're depleting a specific balance. Convert via GOOGLEFINANCE, send transfer_amount in target currency
• Same currency: Direct — no conversion needed

Why This Matters

Sending transfer_amount in target currency for USD-sourced transfers:

1. You convert $365 -> NGN 493,326 via GOOGLEFINANCE
2. Provider converts NGN 493,326 -> $370.57 via their rate (with spread)
3. You're charged $370.57, not $365 — hidden cost

Sending source_amount = $365: provider charges exactly $365.

Running Apps Script Functions from CLI

🚨 Run functions yourself — never ask the user to run them from the Apps Script editor.

Use ~/.claude/scripts/run-appscript.sh — calls the Apps Script Execution API directly via REST. Independent of clasp push login (both work simultaneously).

bash ~/.claude/scripts/run-appscript.sh "<SCRIPT_ID>" resetAllTriggers

Script IDs are in each project's .clasp.json (scriptId field).

Auth: ~/.clasprc-run.json

If expired or missing:

clasp login --creds "~\Downloads\client_secret_REDACTED.apps.googleusercontent.com.json" --use-project-scopes
cp ~/.clasprc.json ~/.clasprc-run.json
clasp login  # Restore default login for clasp push

Prerequisites per project

appsscript.json needs "executionApi": { "access": "MYSELF" } and .clasp.json needs "projectId": "personal-461121".

Clasp CLI Logging

clasp logs          # Plain text, most recent first
clasp logs --json   # JSON format for programmatic parsing
clasp push --force  # Deploy changes

• Logs available after execution completes

Formula Testing via Temp Sheet

🚨 When debugging IMPORTRANGE, MATCH, or complex formula failures that MCP reads alone can't diagnose — test formulas programmatically via Apps Script.

Pattern: Write a diagnostic function that creates a temp sheet, writes formula variants, evaluates them server-side, reads results, logs pass/fail, deletes temp sheet. No user interaction needed.

Template:

function diagTestFormulas() {
  var ss = SpreadsheetApp.openById('TARGET_SHEET_ID');
  var tempSheet;
  try {
    var existing = ss.getSheetByName('__DIAG_TEMP__');
    if (existing) ss.deleteSheet(existing);
    tempSheet = ss.insertSheet('__DIAG_TEMP__');

    var tests = [
      '=MATCH("*Pattern*",\'SheetName\'!A1:Z1,0)',
      // Add formula variants to compare...
    ];
    var labels = ['Test description'];

    for (var i = 0; i < tests.length; i++) {
      tempSheet.getRange(i + 1, 1).setFormula(tests[i]); // HARDCODED-CELL-OK: temp diagnostic sheet
    }
    SpreadsheetApp.flush();
    Utilities.sleep(5000); // IMPORTRANGE needs time to resolve

    var results = tempSheet.getRange(1, 1, tests.length, 1).getValues(); // HARDCODED-CELL-OK: origin
    Logger.log('=== FORMULA TEST RESULTS ===');
    for (var j = 0; j < results.length; j++) {
      var val = results[j][0];
      var status = (val === '' || String(val).indexOf('#') === 0) ? 'FAIL' : 'OK';
      Logger.log('Test ' + (j + 1) + ': ' + status + ' -> ' + JSON.stringify(val) + ' -- ' + labels[j]);
    }
    return 'Check logs.';
  } finally {
    if (tempSheet) { try { ss.deleteSheet(tempSheet); } catch (_) {} }
  }
}

Workflow: Write function in project repo → clasp push → run-appscript.sh → clasp logs → interpret results. Clean up diagnostic file after.

When to use: IMPORTRANGE + MATCH failures, cross-sheet reference debugging, formula evaluation that differs between API reads and live sheet, data validation conflicts masking formula errors.

Poka Yoke Gates

• Gates are hard stops — function exits if gate fails
• Check gates before expensive operations (API calls, sheet writes)
• When gate fails: list exactly what needs to be fixed, reference column letter dynamically
• Multiple functions share same gate via shared helper (SSoT)
• Dynamic column letters: resolve from header titles, never hardcode "column P"