google-workspace-workflow

Use when building multi-step Google Workspace automation pipelines — composing gogcli operations across Gmail, Calendar, Drive, Sheets, and other Workspace services in agency-grade workflows.

Model	Source
sonnet	pack: google-cli

Full Reference

Google Workspace Workflow

Pipeline skill for composing multi-step Google Workspace automation using gogcli. Core pattern: Extract→Transform→Load across Workspace services — pulling data from one service, processing it, and pushing to another.

Mandatory Announcement — FIRST OUTPUT before anything else:

┏━ ⚡ google-workspace-workflow ━━━━━━━━━━━━━━━━━━━┓
┃ [one-line description of workflow being built]   ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛

No exceptions. Box frame first, then work.

The ETL Loop for Workspace

Every Workspace pipeline follows this cycle:

1. EXTRACT

Pull structured data from a Workspace source:

# Gmail leads
gog gmail search "subject:inquiry is:unread" --json | jq '.messages[]'

# Calendar contacts from events
gog calendar events list --calendar primary --json | jq '.events[].attendees[]'

# Form responses (as Sheets export)
gog sheets values get <spreadsheet-id> --range "Form Responses 1!A:Z" --json

2. TRANSFORM

Process data with shell tools (jq, awk, sort, uniq):

# Extract name + email from Gmail messages
gog gmail search "from:@clientdomain.com is:unread" --json \
  | jq '[.messages[] | {name: .from.name, email: .from.address, subject: .subject}]'

# Deduplicate contacts
jq 'unique_by(.email)' leads.json

3. LOAD

Push processed data to a Workspace destination:

# Write to Sheets
gog sheets values append <spreadsheet-id> --range "Leads!A:C" \
  --json '[["John Doe","[email protected]","2026-02-28"]]'

# Create Calendar events from a list
while IFS=, read -r name email; do
  gog calendar events create --title "Onboarding: $name" \
    --attendees "$email" --start "2026-03-01T10:00:00" --end "2026-03-01T10:30:00"
done < contacts.csv

Pipeline Patterns

1. Gmail → Sheets (Lead Capture)

Pull unread leads from Gmail, extract contact info, log to a tracking sheet.

#!/usr/bin/env bash
# Gmail → Sheets lead capture pipeline

SPREADSHEET_ID="your-spreadsheet-id"
SHEET_NAME="Leads"

# Step 1: Extract leads
echo "Pulling leads from Gmail..."
LEADS=$(gog gmail search "subject:inquiry is:unread" --json \
  | jq -r '.messages[] | [.from.name, .from.address, .subject, .date] | @csv')

# Step 2: Log to Sheets
echo "$LEADS" | while IFS=, read -r name email subject date; do
  gog sheets values append "$SPREADSHEET_ID" \
    --range "${SHEET_NAME}!A:D" \
    --json "[[$name, $email, $subject, $date]]"
  echo "Logged: $email"
done

# Step 3: Mark as read
gog gmail search "subject:inquiry is:unread" --json \
  | jq -r '.messages[].id' \
  | xargs -I{} gog gmail messages modify {} --add-labels INBOX --remove-labels UNREAD

echo "Done. Leads logged to Sheets."

2. Contacts → Calendar (Batch Invites)

Pull a contact list, create calendar invites for each.

#!/usr/bin/env bash
# Contacts → Calendar batch invite pipeline

EVENT_TITLE="Q1 Client Review"
EVENT_START="2026-03-15T14:00:00"
EVENT_END="2026-03-15T14:30:00"
EVENT_TZ="America/New_York"

# Step 1: Pull contacts
echo "Fetching contacts..."
CONTACTS=$(gog contacts list --json \
  | jq -r '.contacts[] | select(.email != null) | "\(.name),\(.email)"')

# Step 2: Create invite per contact
echo "$CONTACTS" | while IFS=, read -r name email; do
  gog calendar events create \
    --title "$EVENT_TITLE with $name" \
    --attendees "$email" \
    --start "$EVENT_START" \
    --end "$EVENT_END" \
    --timezone "$EVENT_TZ" \
    --send-notifications
  echo "Invited: $name <$email>"
done

3. Forms → Sheets → Calendar (Full Pipeline)

Form submission triggers data logging and calendar event creation.

#!/usr/bin/env bash
# Forms → Sheets → Calendar pipeline

FORM_SPREADSHEET_ID="your-form-spreadsheet-id"
FORM_RANGE="Form Responses 1!A:E"

# Step 1: Read form responses (Google Forms auto-populates a linked Sheet)
echo "Reading form responses..."
RESPONSES=$(gog sheets values get "$FORM_SPREADSHEET_ID" \
  --range "$FORM_RANGE" --json \
  | jq -r '.values[1:][] | @csv')  # Skip header row

# Step 2: Process each response
echo "$RESPONSES" | while IFS=, read -r timestamp name email service_date notes; do
  # Trim quotes from CSV parsing
  email=$(echo "$email" | tr -d '"')
  name=$(echo "$name" | tr -d '"')
  service_date=$(echo "$service_date" | tr -d '"')

  # Step 3: Create calendar event
  gog calendar events create \
    --title "Appointment: $name" \
    --attendees "$email" \
    --start "${service_date}T09:00:00" \
    --end "${service_date}T09:30:00" \
    --description "Booked via form on $timestamp"

  echo "Created appointment for $name on $service_date"
done

4. Drive File Organization (Batch Ops)

Move files matching a pattern into organized folders.

#!/usr/bin/env bash
# Drive organization: move invoices into year/month folders

YEAR="2026"

# Step 1: Find all invoice files
echo "Finding invoice files..."
gog drive search "name contains 'Invoice' and mimeType = 'application/pdf'" --json \
  | jq -r '.files[] | "\(.id),\(.name),\(.createdTime)"' \
  | while IFS=, read -r file_id name created; do
    # Extract month from createdTime (2026-02-15T...)
    MONTH=$(echo "$created" | cut -c6-7)
    FOLDER_NAME="${YEAR}-${MONTH}"

    # Step 2: Find or create target folder
    FOLDER_ID=$(gog drive search "name = '$FOLDER_NAME' and mimeType = 'application/vnd.google-apps.folder'" --json \
      | jq -r '.files[0].id // empty')

    if [ -z "$FOLDER_ID" ]; then
      FOLDER_ID=$(gog drive mkdir "$FOLDER_NAME" --json | jq -r '.id')
      echo "Created folder: $FOLDER_NAME"
    fi

    # Step 3: Move file to folder
    gog drive move "$file_id" --parent "$FOLDER_ID"
    echo "Moved $name → $FOLDER_NAME/"
  done

5. Sheets → Gmail (Mail Merge)

Send personalized emails from a Sheets data source.

#!/usr/bin/env bash
# Sheets → Gmail mail merge pipeline

SPREADSHEET_ID="your-contacts-sheet-id"
RANGE="Contacts!A:C"  # name, email, company

# Step 1: Pull contact list
echo "Reading contacts from Sheets..."
gog sheets values get "$SPREADSHEET_ID" --range "$RANGE" --json \
  | jq -r '.values[1:][] | @csv' \
  | while IFS=, read -r name email company; do
    name=$(echo "$name" | tr -d '"')
    email=$(echo "$email" | tr -d '"')
    company=$(echo "$company" | tr -d '"')

    # Step 2: Compose and send personalized email
    gog gmail send \
      --to "$email" \
      --subject "Your Q1 Report is Ready, $name" \
      --body "Hi $name,

Your Q1 performance report for $company is ready for review.

Click here to access your dashboard: https://dashboard.example.com

Best,
The Team" \
      --track

    echo "Sent to: $name <$email>"
    sleep 1  # Rate limit courtesy
  done

Error Handling Patterns

Error	Recovery
gog: token expired	gog auth add <account> to re-authenticate, then retry
gog: rate limit exceeded	Add sleep 1 between iterations, use --plain to reduce response size
gog: insufficient scope	gog auth add --force-consent --services <scope>
jq: parse error	Check JSON output with `gog … —json
Sheets API quota	Space writes with sleep 0.5, batch with values:batchUpdate
Empty search results	Add `
Calendar conflict	Check gog calendar freebusy before creating events in batch

Multi-Account Patterns

Run the same pipeline across multiple Google accounts:

# Run Gmail → Sheets across all managed accounts
for ACCOUNT in [email protected] [email protected] [email protected]; do
  echo "Processing account: $ACCOUNT"
  GOG_ACCOUNT="$ACCOUNT" gog gmail search "is:unread subject:inquiry" --json \
    | jq -r '.messages[] | [.from.address, .subject, .date] | @csv' \
    | while IFS=, read -r email subject date; do
      gog sheets values append "$TRACKING_SHEET" --range "!A:D" \
        --json "[[$ACCOUNT, $email, $subject, $date]]"
    done
done

Workflow Design Rules

1. Extract first, transform in jq, load last — never mix extraction and loading logic
2. Log every operation — echo "Processing: $item" before each mutation
3. Rate limit writes — sleep 0.5 between Sheets appends, sleep 1 between Gmail sends
4. Dry-run by default — test the extract/transform steps without the load step first
5. Handle empty results — every gog ... --json | jq chain needs a // empty or // [] fallback
6. Use --plain for shell processing — when piping to awk/cut, --plain (TSV) is more reliable than JSON parsing
7. Auth before pipelines — verify gog auth list --check for all accounts before starting a multi-account run

Integration

Need	Skill
gogcli CLI reference	gogcli (gmail.md, calendar.md, drive.md, workspace.md)
Multi-account auth setup	gogcli → reference/setup.md
Google Ads pipeline	google-ads-workflow
Shell scripting help	General — not an armadillo skill