---
name: Google Workspace & API Expert
description: Google Sheets API, Drive API, Gmail API, Calendar API, OAuth, service account, Apps Script, Workspace admin, shared drive, Tasks API, gspread, Vault, DLP, offboarding, Google Groups, IMPORTRANGE, QUERY function, SSO, audit log, workspace security.
---

# Google Workspace & API Expert

**Scope:** Google Workspace administration, API integration, and automation for Sunrise Communities -- a manufactured housing portfolio using Google Workspace as its primary productivity suite. Covers Sheets API v4, Drive API v3, Gmail API v1, Calendar API v3, Tasks API v1, OAuth 2.0, Apps Script, Admin SDK, security, and MCP server integration.

**Important:** This skill provides reference guidance sourced from official Google developer documentation, Google Workspace admin documentation, and web research (March 2026). API quotas, pricing, and feature availability may change -- verify critical decisions against current docs. All administrative changes (user provisioning, security policy, DLP rules) require approval from Brian Spear (Google Workspace admin for SCI domain).

**Cross-references:**
- `Operations/google-mcp-server/` -- Custom MCP server source code (25 tools)
- `Operations/google-mcp-server/MEMORY.md` -- MCP server build history and troubleshooting
- `rentmanager-mcp/credentials/` -- Service account credential files
- `cloudflare-expert/skill.md` -- Cloudflare Workers that consume Google API data
- `rm-accounting-expert/skill.md` -- RentManager data exported to Sheets
- `daily-briefing/skill.md` -- Daily briefing consuming Gmail, Calendar, Tasks, Slack

---

## Table of Contents

1. [Sunrise Google Environment](#1-sunrise-google-environment)
2. [Sheets API v4](#2-sheets-api-v4)
3. [Drive API v3](#3-drive-api-v3)
4. [Gmail API v1](#4-gmail-api-v1)
5. [Calendar API v3](#5-calendar-api-v3)
6. [Tasks API v1](#6-tasks-api-v1)
7. [OAuth 2.0 Patterns](#7-oauth-20-patterns)
8. [Apps Script](#8-apps-script)
9. [Workspace Administration](#9-workspace-administration)
10. [MCP Server Integration](#10-mcp-server-integration)
11. [Financial Sheets Patterns](#11-financial-sheets-patterns)
12. [Security Best Practices](#12-security-best-practices)
13. [Employee Lifecycle](#13-employee-lifecycle)
14. [Decision Trees](#14-decision-trees)
15. [Common Gotchas](#15-common-gotchas)
16. [Procedures](#16-procedures)
17. [Output Formats](#17-output-formats)
18. [References](#18-references)

---


---

## 1. Sunrise Google Environment

### 1.1 Accounts and Domains

| Item | Value |
|------|-------|
| Primary domain | `mysunrisecommunity.com` (singular, NOT communities) |
| Workspace admin | Brian Spear (SCI domain) |
| Workspace tier | Business Standard (verify -- Business Plus adds Vault and advanced DLP) |
| Google Cloud project | `aurora-475203` |
| Service account | `rentmanager-sheets-exporter@aurora-475203.iam.gserviceaccount.com` |
| Service account key | `rentmanager-mcp/credentials/aurora-475203-a7faf206f94e_fixed.json` |
| Key gotcha | The non-`_fixed` version has malformed JSON -- always use the `_fixed` file |

### 1.2 MCP Server (Custom-Built)

| Item | Value |
|------|-------|
| Location | `/Operations/google-mcp-server/` |
| Runtime | Python 3.11+, native (not Docker -- needs macOS Keychain) |
| Framework | FastMCP (`mcp>=1.0.0`) |
| Auth library | `google-auth`, `google-auth-oauthlib`, `google-api-python-client` |
| Token storage | macOS Keychain (`sunrise-google-mcp` service name) |
| Registration | `claude mcp add sunrise-google -s user -- /path/to/run_server.sh` |
| Tool count | 25 tools (Gmail 4, Drive 4, Sheets 4, Tasks 4, Calendar 3, Slack 6) |

### 1.3 Key Spreadsheets

| Spreadsheet | Purpose | Integration |
|-------------|---------|-------------|
| Strategic KPI Dashboard | Monthly KPI tracking | `export_monthly_kpi_dashboard.py` writes via service account |
| Occupancy Dashboard | Lot-level occupancy data | `export_occupancy_dashboard.py` writes via service account |
| Employee Onboarding Form | New hire intake | Monitored by daily briefing skill |
| VTO Request Form | Voluntary time off | Monitored by daily briefing skill |

Onboarding form spreadsheet ID: `1gN9I4DrlfxI6D-nGCqnMpXlME2goTiTAKH1qpSd71IQ`
VTO form spreadsheet ID: `1eQwo0vcQbUFzQpTMQRP76wzLQQvdO3v8NAC81sZ1Tx0`

### 1.4 OAuth Scopes (MCP Server)

Currently authorized scopes for the user-context MCP server:

```
https://www.googleapis.com/auth/gmail.modify
https://www.googleapis.com/auth/gmail.send
https://www.googleapis.com/auth/gmail.labels
https://www.googleapis.com/auth/drive
https://www.googleapis.com/auth/spreadsheets
https://www.googleapis.com/auth/tasks
https://www.googleapis.com/auth/calendar.readonly
```

To add a scope: update `SCOPES` list in `src/auth.py`, then run `python -m src.setup --reauth`.

---


---

## 14. Decision Trees

### 14.1 API vs. Apps Script

```
Need to process data in a Google Sheet?
├── Triggered by user action (edit, open)?
│   └── YES → Apps Script (simple/installable trigger)
├── Triggered by external event (webhook, cron)?
│   └── YES → API (called from Worker/server)
├── Needs external API calls?
│   ├── Simple HTTP → Apps Script (UrlFetchApp, 6-min limit)
│   └── Complex/long-running → API (no time limit)
├── Needs to run >6 minutes?
│   └── YES → API
├── One-off automation for a single sheet?
│   └── YES → Apps Script (simpler, no infra)
└── Part of a data pipeline (RM → Sheets)?
    └── YES → API via service account
```

### 14.2 Service Account vs. User OAuth

```
Who is performing the action?
├── Automated pipeline (no user present)?
│   └── Service Account
├── User-interactive tool (Claude Code MCP)?
│   └── User OAuth (3-legged)
├── Needs to access user-specific data (Gmail, Calendar)?
│   ├── With domain-wide delegation?
│   │   └── Service Account + delegation (careful with scopes)
│   └── Without delegation?
│       └── User OAuth
├── Writing to company-owned Sheets?
│   ├── Shared with service account email?
│   │   └── Service Account
│   └── Not shared?
│       └── Share sheet with service account, then use SA
└── Admin operations (user management)?
    └── Service Account with admin delegation
```

### 14.3 Sheets vs. D1 (Cloudflare Database)

```
Is the data accessed by non-technical users?
├── YES → Google Sheets (familiar interface)
├── NO → Consider D1
Is the data >100K rows?
├── YES → D1 (Sheets slows significantly)
├── NO → Either works
Does data need real-time querying from Workers?
├── YES → D1 (lower latency, SQL queries)
├── NO → Sheets
Is it a KPI dashboard consumed by leadership?
├── YES → Sheets (they already use it)
├── NO → Evaluate based on above
Do multiple systems write to it?
├── YES → D1 (better concurrency)
├── NO → Either works
```

### 14.4 My Drive vs. Shared Drives

```
Is the content team-owned (not personal)?
├── YES → Shared Drive
├── NO → My Drive
Will the content outlast any individual employee?
├── YES → Shared Drive
├── NO → My Drive
Does it need consistent access control per group?
├── YES → Shared Drive (membership-based access)
├── NO → My Drive with sharing
Is it a draft or work-in-progress?
├── YES → My Drive (share when ready)
├── NO → Shared Drive
```

---


---

## 15. Common Gotchas

### 15.1 API Gotchas

| Gotcha | Details | Fix |
|--------|---------|-----|
| **Shared drive invisible** | API calls missing `supportsAllDrives=True` | Add param to ALL Drive API calls |
| **IMPORTRANGE auth** | First use requires manual "Allow access" click | Cannot be automated via API |
| **Sheets 429 errors** | >60 requests/min per user | Implement exponential backoff |
| **Token expiry silent fail** | Token expired but no error surfaced | Check `creds.expired` before API call |
| **Service account no access** | Sheet not shared with SA email | Share spreadsheet with SA email address |
| **Malformed JSON key** | Original key file had JSON errors | Use `_fixed` version of key file |
| **OAuth scope change** | Added scope but user sees old token | Must re-auth: `python -m src.setup --reauth` |
| **Gmail API base64** | Standard base64 vs URL-safe base64 | Use `base64.urlsafe_b64decode()` |
| **Calendar timezone** | Missing timezone produces wrong times | Always specify `timeZone` in event body |
| **Drive MIME types** | Google Docs have special MIME types | Export with `files().export()`, not `get_media()` |
| **Batch update field masks** | Missing `fields` parameter | Always specify which fields to update |
| **gspread version** | v6 changed auth method | Use `gspread.service_account()` not `oauth()` |

### 15.2 Admin Gotchas

| Gotcha | Details | Fix |
|--------|---------|-----|
| **OU inheritance** | Child OUs inherit parent policies | Override explicitly at child level |
| **Group nesting** | Nested groups: limit 100 levels, 150 nests | Keep nesting shallow (<3 levels) |
| **License assignment** | Features tied to license, not just OU | Verify license includes needed features |
| **Vault availability** | Not in Business Starter | Need Business Plus or add-on |
| **DLP latency** | New rules take up to 24 hours | Test in Audit-only mode first |
| **Delegation scope** | SA can impersonate ANY user | Restrict scopes aggressively |
| **SAML cert rotation** | 24-hour propagation delay | Maintain 2 certs during rotation |
| **Account deletion** | Destroys My Drive files permanently | Transfer ownership BEFORE deletion |

### 15.3 Apps Script Gotchas

| Gotcha | Details | Fix |
|--------|---------|-----|
| **6-minute timeout** | Scripts terminated at 6 minutes | Checkpoint with `PropertiesService` |
| **Simple trigger limits** | Cannot use services requiring auth | Use installable trigger instead |
| **onEdit column detection** | `e.range` is the edited cell, not row | Check `e.range.getColumn()` |
| **Timezone mismatches** | Script timezone vs spreadsheet timezone | Set both explicitly |
| **Quota exceeded** | Daily email limits (100 consumer, 1500 workspace) | Batch emails, use Gmail API for higher volume |

---


---

## 18. References

### Official Google Documentation

- [Sheets API v4](https://developers.google.com/workspace/sheets/api/reference/rest/v4) -- REST reference for spreadsheet operations
- [Sheets API usage limits](https://developers.google.com/workspace/sheets/api/limits) -- Quota information (300/min project, 60/min user)
- [Sheets API batchUpdate](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/batchUpdate) -- Formatting and structural changes
- [Drive API v3](https://developers.google.com/workspace/drive/api/reference/rest/v3) -- File management, permissions, sharing
- [Drive sharing guide](https://developers.google.com/workspace/drive/api/guides/manage-sharing) -- Permission model and sharing flows
- [Drive shared drive differences](https://developers.google.com/workspace/drive/api/guides/shared-drives-diffs) -- API differences for shared drives
- [Gmail API v1](https://developers.google.com/workspace/gmail/api/reference/rest) -- Message operations, labels, sending
- [Calendar API v3](https://developers.google.com/workspace/calendar/api/v3/reference) -- Events, scheduling, availability
- [Tasks API v1](https://developers.google.com/workspace/tasks/reference/rest) -- Task and tasklist management
- [OAuth 2.0 scopes](https://developers.google.com/identity/protocols/oauth2/scopes) -- Scope reference for all Google APIs
- [Service account auth](https://developers.google.com/identity/protocols/oauth2/service-account) -- Server-to-server authentication
- [Service account best practices](https://docs.cloud.google.com/iam/docs/best-practices-service-accounts) -- Security recommendations
- [Service account key rotation](https://docs.cloud.google.com/iam/docs/key-rotation) -- 90-day rotation guidance
- [Domain-wide delegation](https://support.google.com/a/answer/162106) -- Setup and security considerations
- [Domain-wide delegation best practices](https://support.google.com/a/answer/14437356) -- Scope restriction and audit guidance
- [Apps Script triggers](https://developers.google.com/apps-script/guides/triggers) -- Simple and installable triggers
- [Apps Script quotas](https://developers.google.com/apps-script/guides/services/quotas) -- Execution limits and daily quotas
- [Admin SDK Directory API](https://developers.google.com/workspace/admin/directory/reference/rest) -- User, group, OU management
- [Drive roles and permissions](https://developers.google.com/workspace/drive/api/guides/ref-roles) -- Permission role reference

### Google Workspace Admin Resources

- [Google Workspace DLP](https://support.google.com/a/answer/9646351) -- Data Loss Prevention setup and rules
- [Gmail DLP GA announcement](https://workspaceupdates.googleblog.com/2025/02/gmail-data-loss-prevention-general-availability.html) -- Feb 2025 launch
- [Google Vault](https://support.google.com/vault/answer/2462365) -- Retention, hold, search, export
- [Vault retention rules](https://support.google.com/vault/answer/2990828) -- How retention works (1-36,500 days)
- [Audit log API enhancements](https://workspaceupdates.googleblog.com/2025/12/google-workspace-audit-log-api.html) -- Dec 2025 updates
- [Enhanced audit log events](https://workspaceupdates.googleblog.com/2025/10/enhanced-admin-audit-log-events.html) -- Oct 2025 enhancements
- [SSO setup](https://support.google.com/a/answer/12032922) -- SAML configuration
- [SAML certificate maintenance](https://support.google.com/a/answer/7394709) -- 5-year certs, rotation process
- [Shared drive best practices](https://support.google.com/a/users/answer/13015138) -- Organization and membership
- [Drive sharing policy change](https://workspaceupdates.googleblog.com/2025/09/upcoming-change-to-drive-sharing.html) -- Sep 2025 restricted access change
- [Employee offboarding](https://support.google.com/a/answer/33314) -- Account suspension and data transfer

### Third-Party Libraries

- [gspread](https://docs.gspread.org/en/latest/) -- High-level Python library for Sheets (v6.1.4, requires Python 3.8+)
- [gspread authentication](https://docs.gspread.org/en/v6.1.2/oauth2.html) -- Service account and OAuth setup
- [google-auth Python library](https://google-auth.readthedocs.io/en/latest/) -- Core auth library (v2.38.0)
- [google-api-python-client](https://github.com/googleapis/google-api-python-client) -- Official Google API client
- [FastMCP](https://github.com/jlowin/fastmcp) -- MCP server framework used by Sunrise MCP server

### Sunrise-Specific Resources

- `/Operations/google-mcp-server/` -- Custom MCP server source code
- `/Operations/google-mcp-server/MEMORY.md` -- Build history and troubleshooting
- `rentmanager-mcp/credentials/aurora-475203-a7faf206f94e_fixed.json` -- Service account key
- `/.claude/skills/daily-briefing/skill.md` -- Daily briefing consuming Google APIs
- `/rentmanager-mcp/export_monthly_kpi_dashboard.py` -- KPI export to Sheets
- `/rentmanager-mcp/export_occupancy_dashboard.py` -- Occupancy export to Sheets

---

*Skill built March 2026. Review quarterly for API changes, quota updates, and Workspace feature releases. Key regulatory dates: August 18, 2026 (enhanced audit log events become mandatory). Verify Google Vault availability and Workspace tier with Brian Spear before implementing retention policies.*

---

## Progressive Reference Loading

Deep reference content has been moved to `references/` for lazy loading. Read only the file you need.

| Topic | Load |
|-------|------|
| Sheets API, Drive API | `references/sheets-drive.md` |
| Gmail, Calendar, Tasks APIs | `references/gmail-calendar-tasks.md` |
| OAuth 2.0, Apps Script | `references/oauth-appscript.md` |
| Workspace Admin, MCP Integration, Employee Lifecycle | `references/admin-lifecycle.md` |
| Financial Sheets Patterns (Sunrise-specific) | `references/financial-sheets.md` |
| Security Best Practices, Procedures, Output Formats | `references/security-procedures.md` |