Snapshot your HubSpot pipeline weekly in Google Sheets using a Claude Code skill

mkdir -p .claude/skills/pipeline-snapshot/{templates,references}

.claude/skills/pipeline-snapshot/
├── SKILL.md                              # workflow guidelines + config
├── templates/
│   └── snapshot-sheet-format.md         # expected column layout
└── references/
    └── hubspot-sheets-api.md            # HubSpot + Google Sheets API patterns

---
name: pipeline-snapshot
description: Capture a snapshot of the HubSpot pipeline and append a summary row to Google Sheets with date, total value, deal count, and per-stage breakdowns
disable-model-invocation: true
allowed-tools: Bash, Read
---
 
## Goal
 
Fetch all active deals from HubSpot, calculate pipeline metrics (total value, deal count, per-stage breakdowns), and append a snapshot row to Google Sheets.
 
## Configuration
 
Read these environment variables:
 
- `HUBSPOT_ACCESS_TOKEN` — HubSpot private app token (required)
- `GOOGLE_SERVICE_ACCOUNT_KEY` — path to Google Cloud service account JSON key file (required)
- `GOOGLE_SPREADSHEET_ID` — Google Spreadsheet ID (required)
 
Default pipeline: `default`. The user may request a specific pipeline ID.
 
## Workflow
 
1. Validate that all required env vars are set. If any are missing, print which ones and exit.
2. Fetch pipeline stage definitions from HubSpot to build a stage ID → label map and an ordered list of stages. See `references/hubspot-sheets-api.md` for the endpoint.
3. Search for all active deals in the pipeline. Paginate if needed (max 100 per request). See `references/hubspot-sheets-api.md` for the search endpoint.
4. Calculate metrics: total pipeline value, deal count, and per-stage count + value.
5. Build a snapshot row following the format in `templates/snapshot-sheet-format.md`.
6. Append the row to Google Sheets using the Sheets API v4 append method. See `references/hubspot-sheets-api.md` for the append endpoint.
7. Print a summary of the snapshot: date, total value, deal count, and per-stage breakdowns.
 
## Important notes
 
- Deal amounts may be null or empty. Treat missing amounts as $0.
- The Deals Search API returns max 100 results per request. Use the `after` cursor from `paging.next.after` to paginate.
- Stage IDs in deal properties are internal identifiers — map them to human-readable labels using the pipelines endpoint.
- The Google service account must be shared as an Editor on the target spreadsheet.
- Use `valueInputOption: USER_ENTERED` when appending so numbers are treated as numbers, not text.
- The default pipeline ID is `default` in most HubSpot portals. If the user has renamed it, they may need to provide the pipeline ID.
- Use the `requests` library for HubSpot calls and `google-api-python-client` + `google-auth` for Sheets. Install with pip if needed.

# Snapshot Sheet Format
 
Each snapshot row follows this column layout. The agent should build the row array in this exact order.
 
## Column layout
 
```
Date | Total Value | Deal Count | Stage 1 (Count) | Stage 1 (Value) | Stage 2 (Count) | Stage 2 (Value) | ...
```
 
- **Date**: ISO 8601 format (YYYY-MM-DD)
- **Total Value**: Sum of all deal amounts (numeric, no formatting)
- **Deal Count**: Total number of active deals (integer)
- **Stage columns**: For each pipeline stage in display order, two columns: count (integer) and value (numeric)
 
## Example row
 
```
2026-03-03 | 2400000 | 47 | 12 | 680000 | 8 | 420000 | 15 | 890000 | 7 | 310000 | 5 | 100000
```
 
## Notes
 
- Stages should follow the display order from the HubSpot pipelines API.
- Use `valueInputOption: USER_ENTERED` so Google Sheets parses numbers correctly.
- If no deals exist in a stage, use 0 for both count and value.
- The sheet headers should already exist. The agent appends data below them.
- Format dollar amounts as raw numbers (not strings with $ or commas) — let Google Sheets handle formatting.

# HubSpot + Google Sheets API Reference
 
## Get pipeline stages (HubSpot)
 
Build a stage ID → label map and determine stage order.
 
**Request:**
 
```
GET https://api.hubapi.com/crm/v3/pipelines/deals
Authorization: Bearer <HUBSPOT_ACCESS_TOKEN>
```
 
**Response shape:**
 
```json
{
  "results": [
    {
      "id": "default",
      "label": "Sales Pipeline",
      "stages": [
        { "id": "appointmentscheduled", "label": "Appointment Scheduled" },
        { "id": "qualifiedtobuy", "label": "Qualified to Buy" },
        { "id": "closedwon", "label": "Closed Won" },
        { "id": "closedlost", "label": "Closed Lost" }
      ]
    }
  ]
}
```
 
## Search for deals (HubSpot)
 
**Request:**
 
```
POST https://api.hubapi.com/crm/v3/objects/deals/search
Authorization: Bearer <HUBSPOT_ACCESS_TOKEN>
Content-Type: application/json
```
 
**Body:**
 
```json
{
  "filterGroups": [
    {
      "filters": [
        {
          "propertyName": "pipeline",
          "operator": "EQ",
          "value": "default"
        }
      ]
    }
  ],
  "properties": ["dealname", "amount", "dealstage", "closedate", "createdate"],
  "sorts": [{ "propertyName": "amount", "direction": "DESCENDING" }],
  "limit": 100
}
```
 
- `limit` max is 100. Use the `after` cursor from `paging.next.after` to paginate.
- `amount` may be null or empty for deals without a dollar value set.
- `dealstage` is an internal stage ID — map it using the pipelines endpoint above.
 
**Response shape:**
 
```json
{
  "total": 47,
  "results": [
    {
      "id": "12345",
      "properties": {
        "dealname": "Acme Corp — Enterprise",
        "amount": "150000",
        "dealstage": "qualifiedtobuy",
        "closedate": "2026-04-15T00:00:00.000Z",
        "createdate": "2026-01-10T08:00:00.000Z"
      }
    }
  ],
  "paging": {
    "next": { "after": "100" }
  }
}
```
 
## Append row to Google Sheets
 
Use the Sheets API v4 append method to add a snapshot row.
 
**Authentication:**
 
```python
from google.oauth2 import service_account
from googleapiclient.discovery import build
 
creds = service_account.Credentials.from_service_account_file(
    "<path_to_key_file>",
    scopes=["https://www.googleapis.com/auth/spreadsheets"]
)
service = build("sheets", "v4", credentials=creds)
```
 
**Append request:**
 
```python
result = service.spreadsheets().values().append(
    spreadsheetId="<GOOGLE_SPREADSHEET_ID>",
    range="Sheet1!A1",
    valueInputOption="USER_ENTERED",
    insertDataOption="INSERT_ROWS",
    body={"values": [row]},
).execute()
```
 
- `valueInputOption: USER_ENTERED` means Google Sheets parses values as if typed — numbers stay as numbers.
- `insertDataOption: INSERT_ROWS` appends after the last row with data.
- The `range` parameter (`Sheet1!A1`) tells Sheets where to start looking for data. It appends after the last row, not at A1.
- The service account must be shared as an Editor on the spreadsheet.

/pipeline-snapshot

Fetching pipeline stages... found 6 stages
Fetching deals... found 47 active deals
Calculating metrics...
  Total pipeline: $2,400,000
  Active deals: 47
  Appointment Scheduled: 12 deals ($680,000)
  Qualified to Buy: 8 deals ($420,000)
  Proposal Sent: 15 deals ($890,000)
Appending row to Google Sheets...
Done. Row appended for 2026-03-03.

# Run every Monday at 8 AM
0 8 * * 1 cd /path/to/your/project && claude -p "Run /pipeline-snapshot" --allowedTools 'Bash(*)' 'Read(*)'

name: Weekly Pipeline Snapshot
on:
  schedule:
    - cron: '0 13 * * 1'  # 8 AM ET = 1 PM UTC, Mondays only
  workflow_dispatch: {}
jobs:
  snapshot:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: anthropics/claude-code-action@v1
        with:
          prompt: "Run /pipeline-snapshot"
          allowed_tools: "Bash(*),Read(*)"
        env:
          ANTHROPIC_API_KEY: ${{ secrets.ANTHROPIC_API_KEY }}
          HUBSPOT_ACCESS_TOKEN: ${{ secrets.HUBSPOT_ACCESS_TOKEN }}
          GOOGLE_SERVICE_ACCOUNT_KEY: ${{ secrets.GOOGLE_SERVICE_ACCOUNT_KEY }}
          GOOGLE_SPREADSHEET_ID: ${{ secrets.GOOGLE_SPREADSHEET_ID }}