--- name: google-sheets-sales-tracker-summary description: "Fridays: deals added and won this week from your sales tracker sheet, by rep, with pipeline added and the rows that need cleaning up. Use when the user asks for this, or when it runs on a schedule." --- # Google Sheets sales tracker summary Fridays: deals added and won this week from your sales tracker sheet, by rep, with pipeline added and the rows that need cleaning up. Before a team buys a CRM, the pipeline lives in a shared sheet: one row per deal, a rep, a stage, an amount. It works until Friday, when someone has to filter by date, sum by rep and work out what was actually won this week. The agent does that pass on the sheet as it stands and posts the week per rep. It also lists the rows that would throw the totals off, like a deal with no amount or a close date in the past, so the tracker gets a little cleaner every week. ## Two ways to get the data Use the API or MCP route when you have an MCP server, a CLI or an API token for the app. Otherwise, if you can drive a browser the user is signed in to (Claude in Chrome, a Playwright or Chrome DevTools MCP server), use the browser route. Say which one you used. ## Route A: API or MCP server ### What you need - Viewer access to the tracker for the agent's Google account or service account, plus the spreadsheet ID; a Google Sheets connector or MCP server is the easiest way in. If something is missing, say what and stop; do not invent numbers. ### Steps 1. **Read the tracker** (Google Sheets): Call `spreadsheets.values.get` on `Deals!A1:J500` with `valueRenderOption=UNFORMATTED_VALUE` and `dateTimeRenderOption=FORMATTED_STRING`. Map the header row: Deal, Company, Rep, Stage, Amount, Created, Close date, Won date. 2. **Pick this week's rows**: Added means Created between Monday and today. Won means Stage is Won (or Closed won) with a Won date in the same range. Lost works the same way. 3. **Total by rep**: For each rep: deals added, pipeline added (the sum of Amount), deals won and won value. Add a team row at the bottom. 4. **Find rows to fix**: Open deals whose close date has passed, new rows with no Amount or no Rep, and stages that don't match the list the other rows use. 5. **Write the summary**: Team totals, the table by rep, and the rows to fix with their row numbers so they are quick to find. ## Route B: the browser ### What you need - Signed in to Google with an account that has at least Viewer access to the tracker; Commenter or Editor is needed to save a filter view, otherwise the agent uses a temporary filter and clears it afterwards. If something is missing, say what and stop; do not invent numbers. ### Steps Start at https://docs.google.com/spreadsheets/. The user is signed in already; never type passwords or codes, and ask if a sign-in page appears. 1. **Open the tracker**: From the Sheets home page, open the sales tracker and click the `Deals` tab at the bottom. Read the header row so the columns are clear: Deal, Company, Rep, Stage, Amount, Created, Close date, Won date. 2. **Filter to this week without disturbing the team**: Use **Data > Create filter view** rather than a plain filter, so other people's view stays as it is. On the Created column choose **Filter by condition > Date is after** and pick last Sunday; read every row, with its row number, rep and amount. 3. **Find the deals won**: Change the filter view: Stage **Filter by values** to `Won` (or `Closed won`), and Won date **Date is after** last Sunday. Note each row's rep and amount. Do the same for Lost if the sheet tracks it. 4. **Look for rows to fix**: Clear the filter view and set Amount to **Is empty**, then Rep to **Is empty**, and read the row numbers. Then filter Stage to everything except Won and Lost and Close date to **Date is before** today for the open deals that have slipped. Scan the Stage column's filter values for spellings that appear once. 5. **Total and write it up**: Add up added deals, pipeline, won deals and won value per rep and for the team, then close the filter view (the X at the top right of the filter bar) so the sheet is left as you found it. Note: Filter views need Commenter access or better to save; with Viewer access the agent can still sort and read the tab, just more slowly. On a tracker with more than a few thousand rows, reading row by row in the browser is slow; the API route is better there. ## The report - **Deals won** this week and their value, and **pipeline added**. - A row per rep: added, pipeline, won, won value. - Rows to fix, with row numbers: missing amount or rep, past close dates, unknown stages. ## Post it to Briefkin When `BRIEFKIN_TOKEN` is set, or `~/.config/briefkin/env` exists (the installer from https://briefkin.dev/docs writes it), also send the result to the user's Briefkin board as one card. Keep the `item_key` the same on every run so the card updates in place, use a new `event_id` per run, and put today's real numbers in place of the example ones. If the request fails, say so once and carry on. ```bash [ -n "${BRIEFKIN_TOKEN:-}" ] || . ~/.config/briefkin/env curl -sS -X POST "${BRIEFKIN_URL:-https://briefkin.dev}/v1/events" \ -H "authorization: Bearer $BRIEFKIN_TOKEN" \ -H "content-type: application/json" \ -d @- <<'JSON' { "v": 1, "event_id": "google-sheets-sales-tracker-summary/", "item_key": "google-sheets-sales-tracker-summary", "project": "Acme", "source": "Google Sheets sales tracker summary skill", "kind": "update", "title": "4 deals won this week for $31,200", "summary": "**14** deals added with **$96,500** in pipeline, **4** won for **$31,200**. Maya closed 2 of them.", "widget": [ { "type": "Table", "props": { "columns": [ "Rep", "Added", "Pipeline", "Won", "Won value" ], "align": [ "left", "right", "right", "right", "right" ], "rows": [ [ "Maya", 5, "$38,000", 2, "$18,400" ], [ "Jordan", 4, "$27,500", 1, "$7,800" ], [ "Priya", 3, "$19,000", 1, "$5,000" ], [ "Sam", 2, "$12,000", 0, "$0" ], [ "Team", 14, "$96,500", 4, "$31,200" ] ] } }, { "type": "Callout", "props": { "text": "Fix in the sheet: rows 212, 218 and 231 have no amount, and 2 open deals have a close date in the past (rows 140 and 177).", "tone": "warn" } } ] } JSON ``` Widgets come from the Briefkin catalog (https://briefkin.dev/v1/catalog). Without a token, just give the user the report. ## Run it on a schedule Suggested: Fridays at 16:00 (`0 16 * * 5`). From cron, with the agent signed in on that machine: ```bash 0 16 * * 5 claude -p "Run the google-sheets-sales-tracker-summary skill" # Codex: codex exec, Gemini CLI: gemini -p, Copilot CLI: copilot -p, OpenCode: opencode run ``` Or use the agent's own scheduler (Claude Code routines, Codex automations, Claude or ChatGPT scheduled tasks): https://briefkin.dev/skills/google-sheets-sales-tracker-summary#schedule Source: https://briefkin.dev/skills/google-sheets-sales-tracker-summary