Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Fixing Permanent OAuth Token Expiry in Google Apps Script: Moving from Testing to Production Mode

We hit a recurring pain point this week: Gmail and Google Calendar OAuth tokens expiring after 7 days, forcing manual re-authentication and breaking automated calendar sync across multiple booking platforms. After digging into the root cause, we discovered the issue wasn't a token handling bug—it was Google Cloud's Testing vs. Production mode policy. Here's how we diagnosed it, and what's required to fix it permanently.

The Problem: Why Testing Mode Kills Refresh Tokens

Google Cloud OAuth applications in Testing mode have a hard limitation: refresh tokens expire after 7 days of inactivity. This is by design—it's a security boundary to prevent abandoned apps from maintaining indefinite access to user accounts. Our booking automation infrastructure relies on:

  • Google Calendar API (calendar reads/writes via CalendarSync.gs in Apps Script)
  • Gmail API (email notifications from BookingAutomation.gs)
  • Google Drive API (state file persistence to gs://jada-state-production)

Each of these scopes required fresh OAuth consent. The token stored in /var/lib/jada_tokens/google_refresh.json on the Lightsail server would silently become invalid after 7 days, causing the Lambda function that reads/updates calendar events to fail without explicit errors—just silent sync failures.

Root Cause Analysis: Infrastructure as Evidence

Our GAS deployments live in the Google Cloud Project jada-booking-prod, with the OAuth consent screen configured but the app never published to Production. We verified this by checking:

gcloud config set project jada-booking-prod
gcloud auth list
# Confirmed: service account auth + user OAuth configured

The OAuth brand config exists (visible in Cloud Console → APIs & Services → Credentials → OAuth 2.0 Client IDs), but the consent screen was left in Testing status. This single configuration choice cascades through the entire system:

  • Testing mode → 7-day refresh token expiry → manual re-auth every week
  • Production mode → indefinite refresh token validity (revoked only by user or explicit token revocation)

Why This Happened: The Automation Gap

The reauth_jada_all.py script in /Users/cb/Documents/repos/tools/ was written as a band-aid. It handles the mechanics of re-authenticating via OAuth flow:

# Pseudo-structure of reauth_jada_all.py:
# 1. Trigger Google OAuth consent screen
# 2. Exchange authorization code for new refresh token
# 3. Store token in /var/lib/jada_tokens/
# 4. Push token to Lambda environment and Lightsail server
# 5. Verify calendar/email endpoints still work

This works, but it's a symptomatic treatment. Running it every 6 days prevents outages but doesn't solve the underlying constraint. The real fix requires moving the OAuth app from Testing to Production mode in Google Cloud Console.

The Fix: Two-Step Process

Step 1: Update the OAuth Consent Screen (Console UI)

Google Cloud doesn't expose this via gcloud CLI or API—you must use the Cloud Console. Here's what needs to happen:

  1. Navigate to APIs & Services → OAuth consent screen in the jada-booking-prod project
  2. Under App information, fill in (if not already present):
    • App name: JADA Booking Automation
    • User support email: jadasailing@gmail.com
    • Developer contact: Same or ops contact
  3. Under Scopes, confirm these are listed (they should be auto-populated from existing credentials):
    • https://www.googleapis.com/auth/calendar (Google Calendar)
    • https://www.googleapis.com/auth/gmail.send (Gmail)
    • https://www.googleapis.com/auth/drive.file (Google Drive—for state persistence)
  4. Click "Publish App" to move from Testing → Production
  5. Confirm the change—the consent screen status badge should change from blue (Testing) to green (In Production)

Step 2: Re-authenticate Once in Production Mode

Once the app is in Production mode, run the re-auth script one final time. This time, the newly-minted refresh token will be valid indefinitely (until explicitly revoked):

cd /Users/cb/Documents/repos/tools/
python3 reauth_jada_all.py
# Triggers OAuth flow, stores new refresh token
# Pushes to Lambda + Lightsail
# Tests calendar/email endpoints

The token now stored in /var/lib/jada_tokens/google_refresh.json will not expire after 7 days.

Cascading Fixes: What Gets Unblocked

Once the token is permanent, several systems automatically resume working:

  • Boatsetter iCal Sync: The calendarSyncSetup() trigger in CalendarSync.gs can finally write to the JADA Internal calendar without auth failures. Ticket m-91325edb becomes actionable.
  • Sailo Auto-Sync: Same mechanism—their iCal pull from our calendar will work reliably.
  • Lambda Calendar Updates: The PUT /calendar endpoint in API Gateway (routed to UpdateCalendarEvent Lambda) can write crew assignments and block times without token invalidation errors.
  • Gmail Notifications: Booking confirmations and crew alerts sent via GmailService.send() won't fail silently.

Why We Do This Once, Not Repeatedly

The reason this is worth 2 minutes of Cloud Console time instead of running reauth_jada_all.py every week:

  • Operational reliability: No more silent calendar sync failures because a token expired overnight
  • Reduces manual intervention: One OAuth consent → indefinite token validity instead of a recurring task
  • Security is maintained: Production mode doesn't weaken security; it just doesn't apply the 7-day cutoff. Token revocation still works, scopes are still enforced, and the app is still auditable.
  • Scales with the product: As we add more platforms (Viator, GetMyBoat) that depend on calendar sync, this becomes a critical reliability factor

Verification: Confirming the Fix

After publishing the app and re-authing, verify the change propagated:

# On Lightsail server
cat /var/lib/jada_tokens/google_refresh.json
# Should see a valid refresh token

# Test calendar write