Snapshot your HubSpot pipeline weekly in Google Sheets using an agent skill

Date | Total Value | Deal Count | Appointment Scheduled (Count) | Appointment Scheduled (Value) | Qualified (Count) | Qualified (Value) | Proposal (Count) | Proposal (Value) | Closed Won (Count) | Closed Won (Value)

mkdir -p .claude/skills/pipeline-snapshot/scripts

---
name: pipeline-snapshot
description: Captures a snapshot of the HubSpot pipeline and appends a summary row to Google Sheets. Tracks date, total value, deal count, and per-stage breakdowns.
disable-model-invocation: true
allowed-tools: Bash(python *)
---
 
Capture a pipeline snapshot by running the bundled script:
 
1. Run: `python $SKILL_DIR/scripts/snapshot.py`
2. Review the output for any errors
3. Confirm the row was appended to Google Sheets

#!/usr/bin/env python3
"""
Pipeline Snapshot: HubSpot → Google Sheets
Fetches all active deals, calculates summary metrics, appends a row.
"""
import os
import sys
from datetime import datetime, timezone
 
try:
    import requests
    from google.oauth2 import service_account
    from googleapiclient.discovery import build
except ImportError:
    os.system("pip install requests google-api-python-client google-auth -q")
    import requests
    from google.oauth2 import service_account
    from googleapiclient.discovery import build
 
# --- Config ---
HUBSPOT_TOKEN = os.environ.get("HUBSPOT_TOKEN")
SPREADSHEET_ID = os.environ.get("GOOGLE_SPREADSHEET_ID")
GOOGLE_KEY_FILE = os.environ.get("GOOGLE_SERVICE_ACCOUNT_KEY")
 
if not all([HUBSPOT_TOKEN, SPREADSHEET_ID, GOOGLE_KEY_FILE]):
    print("ERROR: Missing env vars: HUBSPOT_TOKEN, GOOGLE_SPREADSHEET_ID, GOOGLE_SERVICE_ACCOUNT_KEY")
    sys.exit(1)
 
HEADERS = {"Authorization": f"Bearer {HUBSPOT_TOKEN}", "Content-Type": "application/json"}
 
# --- Fetch pipeline stages ---
print("Fetching pipeline stages...")
stages_resp = requests.get("https://api.hubapi.com/crm/v3/pipelines/deals", headers=HEADERS)
stages_resp.raise_for_status()
stage_map = {}
stage_order = []
for pipeline in stages_resp.json()["results"]:
    for stage in pipeline["stages"]:
        stage_map[stage["id"]] = stage["label"]
        stage_order.append(stage["label"])
 
print(f"Stages: {', '.join(stage_order)}")
 
# --- Fetch all deals (paginated) ---
print("Fetching deals...")
all_deals = []
after = 0
while True:
    resp = requests.post(
        "https://api.hubapi.com/crm/v3/objects/deals/search",
        headers=HEADERS,
        json={
            "filterGroups": [{"filters": [{"propertyName": "pipeline", "operator": "EQ", "value": "default"}]}],
            "properties": ["dealname", "amount", "dealstage", "closedate", "createdate"],
            "sorts": [{"propertyName": "amount", "direction": "DESCENDING"}],
            "limit": 100,
            "after": after,
        },
    )
    resp.raise_for_status()
    data = resp.json()
    all_deals.extend(data["results"])
    if "paging" in data and "next" in data["paging"]:
        after = data["paging"]["next"]["after"]
    else:
        break
 
print(f"Found {len(all_deals)} deals")
 
# --- Calculate snapshot ---
total_value = 0
by_stage = {}
 
for deal in all_deals:
    props = deal["properties"]
    amount = float(props.get("amount") or 0)
    total_value += amount
 
    stage_id = props.get("dealstage", "unknown")
    stage_name = stage_map.get(stage_id, stage_id)
 
    if stage_name not in by_stage:
        by_stage[stage_name] = {"count": 0, "value": 0}
    by_stage[stage_name]["count"] += 1
    by_stage[stage_name]["value"] += amount
 
today = datetime.now(timezone.utc).strftime("%Y-%m-%d")
row = [today, total_value, len(all_deals)]
for stage_name in stage_order:
    data = by_stage.get(stage_name, {"count": 0, "value": 0})
    row.append(data["count"])
    row.append(data["value"])
 
# Print summary
print(f"\nSnapshot for {today}:")
print(f"  Total pipeline: ${total_value:,.0f}")
print(f"  Deal count: {len(all_deals)}")
for stage_name in stage_order:
    data = by_stage.get(stage_name, {"count": 0, "value": 0})
    print(f"  {stage_name}: {data['count']} deals (${data['value']:,.0f})")
 
# --- Append to Google Sheets ---
print("\nAppending to Google Sheets...")
creds = service_account.Credentials.from_service_account_file(
    GOOGLE_KEY_FILE, scopes=["https://www.googleapis.com/auth/spreadsheets"]
)
service = build("sheets", "v4", credentials=creds)
result = service.spreadsheets().values().append(
    spreadsheetId=SPREADSHEET_ID,
    range="Sheet1!A1",
    valueInputOption="USER_ENTERED",
    insertDataOption="INSERT_ROWS",
    body={"values": [row]},
).execute()
 
updated = result.get("updates", {}).get("updatedRows", 0)
print(f"Done — appended {updated} row(s) to spreadsheet {SPREADSHEET_ID}")

# Claude Code
/pipeline-snapshot
 
# Or run the script directly
python .claude/skills/pipeline-snapshot/scripts/snapshot.py

# crontab -e
0 8 * * 1 cd /path/to/project && python .claude/skills/pipeline-snapshot/scripts/snapshot.py

name: Weekly Pipeline Snapshot
on:
  schedule:
    - cron: '0 13 * * 1'
  workflow_dispatch: {}
jobs:
  snapshot:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: '3.12'
      - run: pip install requests google-api-python-client google-auth
      - run: python .claude/skills/pipeline-snapshot/scripts/snapshot.py
        env:
          HUBSPOT_TOKEN: ${{ secrets.HUBSPOT_TOKEN }}
          GOOGLE_SPREADSHEET_ID: ${{ secrets.GOOGLE_SPREADSHEET_ID }}
          GOOGLE_SERVICE_ACCOUNT_KEY: ${{ secrets.GOOGLE_SERVICE_ACCOUNT_KEY }}