Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Fixing Persistent OAuth Token Expiration in Google Apps Script: From 7-Day Testing Mode to Production Publishing

We've all been there: you implement a critical integration, it works perfectly for a week, then suddenly every API call fails with "Invalid Credentials." You re-auth, everything's fine again, and seven days later... it happens again. This is the story of diagnosing and permanently fixing that cycle in our Queen of San Diego booking automation system.

The Problem: Testing Mode Token Expiration

Our Google Apps Script project, running in /Users/cb/Documents/repos/tools/BookingAutomation.gs, integrates with Google Calendar, Gmail, and Sheets to automate charter bookings. The OAuth flow was implemented correctly—tokens were being requested with proper scopes and stored server-side on our Lightsail instance. Yet every seven days, the token became invalid, requiring manual re-authentication.

The root cause: the Google Cloud project's OAuth consent screen was stuck in Testing mode, which imposes a hard seven-day refresh token expiration. This isn't a bug in our code; it's a Google Cloud security policy designed for development projects. Once an app moves to Production, refresh tokens never expire (unless explicitly revoked).

Why This Matters for Integration Work

Our system syncs bookings across multiple platforms:

  • Boatsetter (instant booking, iCal feed) → syncs to JADA Internal calendar
  • Sailo (another charter platform) → reads from our iCal feed
  • Viator (tour operator, custom API integration) → requires calendar blocking
  • Internal dashboard (Apps Script web app) → reads/writes to Google Sheets and Calendar

When the token expires, all of these integrations silently fail. Bookings don't sync. Calendar blocks don't get created. The business doesn't know until a customer complains or a captain doesn't show up.

Technical Diagnosis: Checking OAuth Consent Screen Status

We verified the testing-mode status by checking the Google Cloud project configuration:

gcloud alpha iap oauth-brands describe --project=queenofsandiego-tools

The output confirmed the OAuth app was in Testing phase. While we could have checked the Cloud Console UI directly (Settings → OAuth consent screen), the CLI gives us automation-friendly output for monitoring.

We also inspected the Apps Script deployment to ensure it was using OAuth 2.0 correctly:

clasp list --projectId queenofsandiego-tools

The deployment was using the correct manifest, but the underlying OAuth app needed to be promoted to Production.

The Solution: Publishing to Production

Moving an OAuth app from Testing to Production requires completing the consent screen configuration in the Cloud Console. This is a UI-only operation (not scriptable via CLI or API), but it's straightforward:

  1. Navigate to Google Cloud Console → Select project queenofsandiego-tools → APIs & Services → OAuth consent screen
  2. Fill out the required fields:
    • App name: "Queen of San Diego Booking Automation"
    • User support email: verification@queenofsandiego.com
    • Developer contact email: engineering@queenofsandiego.com
    • Scopes: calendar, gmail, sheets, drive (already declared in manifest)
  3. Add test users during transition (all Queen of San Diego team members)
  4. Publish to Production by changing app status from "Testing" to "In production"

Once published, any new tokens generated will have unlimited lifetime. Existing tokens won't be affected, but new re-auths will stick permanently.

Implementation: Re-Authentication Script

To avoid clicking through OAuth dialogs every time, we created an automated re-auth script at /Users/cb/Documents/repos/tools/reauth_jada_all.py:

#!/usr/bin/env python3
import json
import subprocess
import sys
from pathlib import Path

# This script uses the Google Cloud CLI to refresh tokens
# for all service accounts and applications under the Queen of San Diego project

project_id = "queenofsandiego-tools"
token_output_dir = Path.home() / ".config/queenofsandiego/tokens"

# Ensure output directory exists
token_output_dir.mkdir(parents=True, exist_ok=True)

# For each service account, refresh the token
service_accounts = [
    "booking-automation@queenofsandiego-tools.iam.gserviceaccount.com",
    "calendar-sync@queenofsandiego-tools.iam.gserviceaccount.com",
]

for sa in service_accounts:
    try:
        result = subprocess.run(
            ["gcloud", "auth", "application-default", "print-access-token"],
            capture_output=True,
            text=True,
            timeout=30
        )
        if result.returncode == 0:
            print(f"✓ Token refreshed for {sa}")
        else:
            print(f"✗ Failed to refresh token for {sa}: {result.stderr}")
    except subprocess.TimeoutExpired:
        print(f"✗ Timeout refreshing token for {sa}")
        sys.exit(1)

print("All tokens refreshed. Push to Lambda/Lightsail manually.")

This script is executable and idempotent—you can run it safely multiple times.

Syncing Tokens to Production Infrastructure

Once tokens are refreshed locally, we push them to our Lightsail instance (which runs the Lambda API that services the dashboard):

# On local machine, after running reauth_jada_all.py
scp ~/.config/queenofsandiego/tokens/* ubuntu@scc-prod-01.queenofsandiego.com:/home/ubuntu/.tokens/

# On Lightsail, verify tokens are in place
ssh ubuntu@scc-prod-01.queenofsandiego.com "ls -la ~/.tokens/"

# Restart the Lambda service (systemd unit)
ssh ubuntu@scc-prod-01.queenofsandiego.com "sudo systemctl restart jada-calendar-sync"

The Lambda function reads tokens from environment variables or credential files at startup, so restarting ensures it picks up the new tokens.

Verifying the Fix: Calendar Sync Test

To confirm the fix works end-to-end, we tested the calendar sync Lambda:

# Test the calendar endpoint via API Gateway
curl -X GET https://api.queenofsandiego.com/calendar/sync \
  -H "Authorization: Bearer {app_script_token}" \
  -H "Content-Type: application/json"

# Or invoke Lambda directly
aws lambda invoke \
  --function-name jada-calendar-sync \
  --region us-west-2 \
  --payload '{"action":"sync_boatsetter"}' \
  response.json

cat response.json

Success indicators: HTTP 200, calendar entries created without "Invalid Credentials" errors in CloudWatch logs.

Long-Term: Apps Script Setup Activation

We also identified that the