# ADR-024: Google Slides/Sheets Integration Uses Google Apps Script via Self-Hosted Proxy

## Status

Accepted, 2026-06-25.

## Status History

```yaml
status_history:
  - date: 2026-06-25
    status: Proposed
    changed_by: hkl
    reason: Formalising GAS proxy architecture used by recce and installation PWAs
    changed_via: adr-kit (360lm)
  - date: 2026-06-25
    status: Accepted
    changed_by: hkl
    reason: slides-proxy live on port 8768; recce + installation use it; pattern stable
    changed_via: adr-kit (360lm)
```

## Context

Recce and installation PWAs need to auto-generate Google Slides presentations from field submission data (photos, store info, branding observations). The browser cannot call Google Slides API directly — it requires OAuth 2.0 with a service account or user delegation, CORS handling, and complex API calls to create/populate slides. Google Apps Script (GAS) can manipulate Google Slides and Sheets natively without OAuth complexity — a GAS Web App deployment exposes a simple HTTP endpoint. A self-hosted Python proxy (`/slides-proxy/`, port 8768) bridges the PWA to the GAS endpoint: it receives photo uploads, saves them to VPS disk with permanent URLs, then calls the GAS Web App which creates the Slides presentation.

## Decision

All Google Slides and Google Sheets automation uses Google Apps Script (GAS) Web Apps as the automation layer. The GAS Web App URL is stored in PWA settings (IndexedDB or localStorage) and called via the self-hosted `/slides-proxy/` service. The proxy handles: photo persistence to VPS disk (`/var/www/360lm/screenshots/`), GAS Web App HTTP calls, and response forwarding. Direct Google Sheets/Slides API (OAuth service account) is NOT used.

PWAs store the GAS script URL in settings (admin-configurable) — if Google changes or the GAS script is redeployed, only the URL needs updating, not the PWA code.

**Decision Maker:** hkl

## Alternatives Considered

- **Google Sheets/Slides REST API with OAuth service account.** Rejected: requires GCP service account setup, credential management, OAuth token refresh logic, and complex API calls to create slides programmatically — significant implementation burden with no UX benefit over GAS for this use case.
- **Google Sheets/Slides REST API with user OAuth (browser flow).** Rejected: requires every field employee to authorise the app with their Google account — employees do not have individual Google accounts (see ADR-011).
- **Third-party reporting tools (Canva API, custom PDF).** Rejected: Canva API requires design templates in their system; custom PDF generation requires a PDF library and layout code; Google Slides already has the approved presentation templates used by the client team.
- **No auto-generation (manual slide creation).** Rejected: recce agents submit 10–20 store visits per day; manual slide creation would take hours; auto-generation is the primary value proposition of the recce PWA.

## Consequences

**Positive:**
- GAS handles all Google API authentication internally — no OAuth complexity in PWA or proxy.
- GAS Web App URL is configurable per deployment — no code change to point at a different script.
- Photos saved to VPS disk have permanent URLs usable in slides without Google Drive upload.
- One GAS script serves multiple PWAs (recce template, installation template).

**Negative / Trade-offs:**
- GAS has execution quotas (6 min timeout, 20k URL fetch calls/day) — not suitable for bulk generation.
- GAS Web App must be deployed by hkl manually (one-time setup per script version).
- If Google deprecates or changes GAS Web App behaviour, the integration breaks.
- GAS script URL must be kept secret (it provides unauthenticated access to slide generation).

**Risks and mitigations:**
- GAS quota exhausted: mitigated by the low volume of recce submissions per day (< 50); quota resets daily.
- GAS script URL leaked: mitigated by storing URL in admin settings, not hardcoded; URL can be redeployed with a new URL if compromised.
- slides-proxy down: PWA shows error on sync but data is safe in IndexedDB/PostgREST; retry on next open.

## Related Decisions

- ADR-017 (Traefik routing) — slides-proxy exposed via Traefik at `/slides-proxy/`.
- ADR-020 (offline-first IndexedDB) — submission data safe in IDB; Slides sync is a best-effort background step.

## References

- `memory/project_arch.md` — slides proxy description, GAS URL storage
- `memory/infra_vps.md` — slides-proxy container (Python port 8768)
- `pwa_dev_style.md` — Q0.9 Google Slides auto-generation pattern
- `/opt/slides-proxy/gas_slides.gs` — the GAS script (user deploys to Google Apps Script)