Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

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

The Problem: 7-Day Refresh Token Expiration

We were stuck in a recurring pattern of OAuth token failures every week. The root cause: our Google Cloud project's OAuth 2.0 consent screen was stuck in Testing mode, which hard-caps refresh token lifetime to 7 days. Every time a token expired, we'd have to manually re-authenticate across multiple services—Lambda functions, Apps Script deployments, and CLI tools.

This affected multiple critical systems:

  • Google Calendar API access from Lambda for booking sync
  • Apps Script deployments requiring calendar read/write permissions
  • OAuth token propagation to remote Lightsail servers
  • Calendar synchronization triggers across Boatsetter, Sailo, and Viator platforms

The fix was straightforward conceptually but required careful sequencing: publish the OAuth consent screen to Production, regenerate tokens with indefinite lifetime, and redistribute them across all services.

OAuth Consent Screen Configuration

Google Cloud Console's OAuth consent screen configuration is UI-only—no API exists to automate it. However, the process to move from Testing to Production is deterministic:

Step 1: Verify Current Status

# Check which APIs are enabled and current consent screen status
gcloud services list --enabled --project=sailjada-prod
gcloud iam service-accounts list --project=sailjada-prod

Our project had the following scopes configured:

  • https://www.googleapis.com/auth/calendar (read/write booking events)
  • https://www.googleapis.com/auth/calendar.readonly (secondary read-only for some triggers)
  • https://www.googleapis.com/auth/spreadsheets (Apps Script manifest)

Step 2: Transition Requirements

To move to Production, we needed to:

  1. Add an app privacy policy URL (already hosted in S3)
  2. Define the primary user type (internal: "Organization")
  3. Set data access justifications for each scope
  4. Optionally add Terms of Service URL

The privacy policy was already served from s3://sailjada-docs/privacy.html, so this was a straightforward two-click operation in the Cloud Console.

Token Regeneration and Distribution Strategy

Once the consent screen moved to Production, new tokens would have indefinite lifetime. The challenge was atomically replacing tokens across distributed services without downtime.

Token Generation

We used the existing OAuth flow via reauth_jada_all.py:

# Location: /Users/cb/Documents/repos/tools/reauth_jada_all.py
# This script handles OAuth re-authentication across all scopes
python3 reauth_jada_all.py

The script writes tokens to local filesystem, then we staged them for distribution.

Lightsail Server Token Sync

Our primary server runs on AWS Lightsail. SSH keys were already configured:

# Verify SSH config for Lightsail instance
cat ~/.ssh/config | grep -A5 lightsail-prod

# Sync token files to remote server
rsync -avz --delete \
  ~/.config/sailjada/tokens/ \
  lightsail-prod:/home/ec2-user/.config/sailjada/tokens/

# Verify on remote
ssh lightsail-prod "ls -lah ~/.config/sailjada/tokens/"

All tokens are stored with mode 0600 (user read-only) to prevent unauthorized access.

Lambda Environment Variables Update

Our Lambda functions read tokens from environment variables. We updated them via:

# Push refreshed token to Lambda for calendar sync
aws lambda update-function-configuration \
  --function-name jada-calendar-sync \
  --environment Variables={GOOGLE_CALENDAR_TOKEN=...,SCOPES=calendar,...} \
  --region us-east-1

The Lambda function (`jada-calendar-sync`) is triggered by CloudWatch Events and reads this environment-stored token to access Google Calendar API when syncing Boatsetter bookings.

Apps Script Deployment Updates

Our primary Apps Script project (`BookingAutomation`) had multiple deployments. The OAuth token is embedded in the script's runtime:

# Check all deployments
clasp list

# The main deployment serves the web app at:
# https://script.google.com/macros/d/{DEPLOYMENT_ID}/usercss

We updated the deployment to version 98 with explicit setup actions:

# Login via clasp with unified token
clasp login --no-localhost

# Push code and deploy with setup trigger
clasp push
clasp deploy -d "Production - OAuth refresh enabled"

# Trigger setup functions remotely via scripts.run API
gcloud alpha firebase functions call BookingAutomation \
  --data='{"function":"calendarSyncSetup"}'

The setup functions initialize calendar cache and validate token scopes before the web app handles user requests.

Verification and Testing

Calendar API Access

After token distribution, we validated each service could reach Google Calendar:

# Test Lambda directly
aws lambda invoke \
  --function-name jada-calendar-sync \
  --payload '{"action":"test_calendar_access"}' \
  response.json
cat response.json

# Test via API Gateway endpoint (routed through CloudFront)
curl -X GET \
  https://api.sailjada.com/calendar/events \
  -H "Authorization: Bearer ${TOKEN}" \
  -H "Accept: application/json"

API Gateway routes are configured in /repos/infrastructure/api-gateway.yaml and cached via CloudFront distribution d3xxxxxxxxxx.

Apps Script Calendar Access

We validated the GAS deployment by triggering calendar functions:

# Invoke calendar sync via GAS web endpoint
curl -X POST \
  https://script.google.com/macros/d/{DEPLOYMENT_ID}/usercss \
  -H "Content-Type: application/json" \
  -d '{"action":"syncCalendar"}'

Why This Matters: Downstream Calendar Sync

With OAuth now permanently fixed, the following integrations now work without manual intervention:

  • Boatsetter: iCal feed from JADA Internal calendar syncs automatically via `CalendarSync.gs` trigger (was inactive, now active)
  • Sailo: Same iCal feed; trigger was never activated but is now functional
  • Viator: API integration blocked on their end, but our OAuth token refresh is no longer a bottleneck

The calendar sync trigger in Apps Script runs on a 15-minute interval, automatically pulling new bookings and propagating them to external platforms.

Key Decisions and Trade-offs

  • Testing vs. Production Mode: Moving to Production required adding privacy policy URL and data justifications. This was a