Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

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

The Problem: 7-Day Refresh Token Expiration

Our crew scheduling and calendar synchronization system relies on Google Apps Script (GAS) to sync booking data across multiple platforms — Boatsetter, Viator, Sailo, and internal JADA calendars. The system had been working, then mysteriously stopped. Investigation revealed a recurring pattern: every 7 days, OAuth tokens would expire and manual re-authentication was required.

The root cause wasn't a bug in our code or a temporary API issue. It was a fundamental constraint of Google Cloud's OAuth implementation: applications in Testing mode receive refresh tokens with a 7-day expiration window. Every token reauth we'd performed was a band-aid, not a fix.

Why Testing Mode Exists (And Why It Was Blocking Us)

Google enforces Testing mode as a safety guardrail for OAuth applications during development. In Testing mode:

  • Refresh tokens expire after 7 days of inactivity
  • Only accounts explicitly added to the OAuth consent screen's "Test Users" list can authenticate
  • Token rotation happens automatically but the new tokens also expire in 7 days
  • There's no way to extend this — it's a hard limit at the Google Cloud API level

Our application needed to move to Production mode, which grants indefinite refresh token lifetime (or until explicitly revoked). The trade-off: we needed to complete Google's OAuth app verification process, which requires demonstrating legitimate use case, privacy practices, and data handling.

Technical Implementation: Moving to Production

Step 1: Verify Current Project Configuration

First, we confirmed the current state of the Google Cloud project and identified exactly what was deployed:


# Check available gcloud CLI and project access
gcloud config get-value project
gcloud auth list

# List all enabled APIs in the project
gcloud services list --enabled

# Check OAuth consent screen via Cloud Console (API-only partial view)
gcloud oauth-config get-client-secret --project=[PROJECT_ID]

We verified that the following APIs were enabled:

  • Google Calendar API (calendar.googleapis.com)
  • Google Apps Script API (script.googleapis.com)
  • Gmail API (gmail.googleapis.com)

Step 2: OAuth Consent Screen Configuration

The OAuth consent screen configuration is managed exclusively through the Google Cloud Console UI (not via gcloud CLI). We navigated to APIs & Services > OAuth consent screen and configured:

  • App Name: JADA Sailing Crew & Calendar Management
  • User Support Email: Verified Gmail account associated with the project
  • Developer Contact Information: Project admin email and phone
  • Required Scopes:
    • https://www.googleapis.com/auth/calendar — Read/write calendar events
    • https://www.googleapis.com/auth/gmail.readonly — Read booking confirmations from Gmail
    • https://www.googleapis.com/auth/script.external_request — Execute external API calls from GAS

Step 3: OAuth Brand Configuration

Google requires OAuth brand configuration before moving to Production. This includes:

  • Organization name and branding (JADA Sailing)
  • Privacy policy URL (required for Production apps)
  • Terms of service URL (required for Production apps)
  • Homepage URL pointing to the actual service

The brand is created once per Google Cloud project under APIs & Services > Credentials > OAuth 2.0 Client IDs.

Step 4: Moving from Testing to Production

In the OAuth consent screen, there's a toggle between "Testing" and "Production" user types. The process is:

  1. In Cloud Console, navigate to APIs & Services > OAuth consent screen
  2. Click the "Publishing Status" button
  3. Select "Publish app"
  4. Google verifies the configuration and changes the application status to Production

Once in Production mode, all OAuth tokens issued have indefinite refresh token lifetime.

Code-Level Changes in Google Apps Script

Our GAS codebase didn't require functional changes, but we needed to ensure the OAuth flow was correctly configured. The relevant file is /Users/cb/Documents/repos/tools/BookingAutomation.gs (deployed via clasp):


// The doGet() handler manages OAuth callback
function doGet(e) {
  const authCode = e.parameter.code;
  if (authCode) {
    // Exchange auth code for tokens using OAuth2 service
    OAuth2.handleCallback(authCode);
    return HtmlService.createHtmlOutput('Authorization successful. You can close this window.');
  }
}

// Initial authorization trigger — called once after deployment
function calendarDashboardSetup() {
  const oauth = OAuth2.getService();
  if (!oauth.hasAccess()) {
    const authorizationUrl = oauth.getAuthorizationUrl();
    Logger.log('Visit this URL to authorize: ' + authorizationUrl);
  }
  // Subsequent calls use the stored refresh token
}

The key point: once we've completed OAuth setup in Production mode, the refresh token stored in GAS Properties will never expire. The OAuth2 library we use handles token refresh automatically during execution.

Token Storage and Refresh Pipeline

Tokens are stored in several places for different purposes:

  • Google Apps Script Properties (PropertiesService): Stores refresh token and access token for GAS execution
  • AWS Secrets Manager: Stores a copy of the refresh token for use by Lambda functions
  • AWS Lambda Environment Variables: Stores short-lived access tokens synced from GAS before Lambda invocation

The sync pipeline is in /Users/cb/Documents/repos/tools/reauth_jada_all.py:


# This script (run manually or via cron) extracts the current access token from GAS
# and pushes it to Lambda environment variables
import boto3
import requests

def get_gas_token():
    """Fetch current access token from deployed GAS via clasp API"""
    response = requests.post(
        'https://script.googleapis.com/v1/projects/[PROJECT_ID]/run',
        json={'function': 'getAccessToken'},
        headers={'Authorization': f'Bearer [ACCESS_TOKEN]'}
    )
    return response.json()['response']['result']

def push_to_lambda(token):
    """Update Lambda env var with fresh token"""
    lambda_client = boto3.client('lambda', region_name='us-east-1')
    lambda_client.update_function_configuration(
        FunctionName='calendar-sync-lambda',
        Environment={
            'Variables': {
                'GOOGLE_ACCESS_TOKEN': token,
                'TOKEN_REFRESHED_AT': datetime.now().isoformat()
            }
        }
    )

Why This Matters for Our Architecture

Our system has three