Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

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

Last week, we hit a recurring pain point: Google OAuth tokens expiring every 7 days, forcing manual re-authentication across our calendar sync, booking automation, and crew dispatch systems. We'd "fixed" this before by simply refreshing tokens, but that was a band-aid. The root cause was architectural: our Google Cloud project was stuck in Testing mode, which intentionally hard-caps refresh token lifetime at 7 days as a security measure for development apps.

This post walks through the permanent fix: publishing the OAuth consent screen to Production, which removes that cap and allows refresh tokens to live indefinitely (or until the user explicitly revokes access).

What Was Done

  • Diagnosed the root cause: Google Cloud Console OAuth consent screen in Testing mode
  • Verified all required scopes and API enablement across the project
  • Prepared OAuth consent screen for Production publication
  • Executed the publication workflow
  • Generated new refresh tokens with extended lifetime
  • Deployed updated tokens to AWS Lambda and Google Apps Script environments

Technical Details: OAuth Token Lifecycle in Testing vs. Production

Google's OAuth 2.0 implementation has a critical distinction for developers:

  • Testing mode: Refresh tokens expire after 7 days of inactivity. This is intentional—Google wants developers to test token refresh logic. Every week you're not using the app, your refresh token dies.
  • Production mode: Refresh tokens are long-lived (effectively indefinite until user revocation). This is appropriate for applications where end users expect persistent, uninterrupted access.

Our setup spanned three systems, all hitting this ceiling:

  1. AWS Lambda calendar sync (/tmp/scc-lambda/lambda_function.py): Pulls JADA's master calendar and syncs to multiple booking platforms (Boatsetter, Viator, Sailo). Runs daily via EventBridge.
  2. Google Apps Script deployment (BookingAutomation v98): Web app at https://script.google.com/macros/d/.../userweb handles real-time booking ingestion and crew dispatch updates. Uses clasp for CI/CD.
  3. Lightsail credential store (/root/.oauth/tokens/): Centralized token storage synced to Lambda environment via S3.

Every 7 days, one of these would fail silently or throw an auth error, and we'd have to manually run the reauth script (/Users/cb/Documents/repos/tools/reauth_jada_all.py) to refresh credentials.

Diagnosing the Issue

The command sequence was:

gcloud auth list
gcloud config list
gcloud projects describe PROJECT_ID --format="value(projectId)"

This confirmed the GCP project was accessible and authed. Next, we checked the OAuth consent screen status:

gcloud compute project-info describe PROJECT_ID \
  --format="value(commonInstanceMetadata.items[google-cloud-enable-oslogin])"

Unfortunately, the OAuth consent screen state is UI-only in Cloud Console — no gcloud API directly exposes it. But we already knew the problem from prior context: the project's OAuth app was Publishing Status = Testing.

Infrastructure: What Needs to Move to Production

Publishing an OAuth app to Production requires:

  • User-facing privacy policy URL: Ours is hosted on the main site; confirmed it exists and is accessible.
  • Application name and logo: Already configured as "JADA Sailing Calendar Automation".
  • OAuth scopes: Must be reviewed and justified. Our app requests:
    • https://www.googleapis.com/auth/calendar — read/write event sync
    • https://www.googleapis.com/auth/gmail.readonly — read booking confirmation emails
    • https://www.googleapis.com/auth/script.external_request — make outbound API calls to booking platforms
    • https://www.googleapis.com/auth/spreadsheets — update crew dispatch sheet (deferred, currently unused)
  • Authorized domains: Must include all callback hosts:
    • localhost:8080 (local dev reauth)
    • queenofsandiego.com (main site, booking confirmation redirects)
    • script.google.com (GAS web app deployment)
  • Test users: For Testing mode, you whitelist email addresses that can auth. For Production, there's no whitelist—any user can attempt login (though they'll be prompted to consent).

The Fix: Two-Click Publication

The actual fix happens entirely in Google Cloud Console (no CLI, no API—just UI):

  1. Navigate to APIs & Services → OAuth consent screen.
  2. Review the current app details (all were already correct).
  3. Click Publish App (the button at the top right).
  4. Confirm the prompt: "Are you sure you want to move to Production? This app will be available to all Google accounts."
  5. Publishing status changes from Testing to In Production.

That's it. No code changes, no redeployment. But now all future refresh tokens issued to this app will be long-lived.

Reauth and Deployment

After publishing, we needed new tokens:

# On local machine (or Lightsail terminal)
cd /Users/cb/Documents/repos/tools
python3 reauth_jada_all.py

This script uses the Google OAuth authorization code flow to generate fresh credentials. With the app now in Production, the resulting refresh tokens no longer expire.

The new tokens were deployed via:

  1. Lambda environment: Upload token files to S3 and trigger an update to the Lambda function's environment variables.
  2. Apps Script: The GAS project uses ScriptApp.getOAuthToken(), which automatically uses the account's current session token. No explicit deployment needed—just ensure the Apps Script project owner has authed with the new credentials.
  3. Lightsail server: SSH into the Lightsail instance and sync token files to /root/.oauth/tokens/, making them available to cron jobs and manual Lambda invocations.

Key Decisions and Rationale

  • Why not use service accounts? We initially considered using a Google Service Account (which doesn't need OAuth, only a private key) for the calendar sync. However, service accounts have no access to the JADA Gmail account for reading booking confirmation emails, and they can't impersonate user-owned calendars in the same way. User OAuth is the right tool here.
  • Why centralize tokens on Lightsail? Rather than