--- name: google-sheets-budget-vs-actual description: "On the 8th: last month's actual spend against the budget in your Google Sheet, line by line, with the lines that ran over and by how much. Use when the user asks for this, or when it runs on a schedule." --- # Google Sheets budget vs actual On the 8th: last month's actual spend against the budget in your Google Sheet, line by line, with the lines that ran over and by how much. The budget lives in a spreadsheet because that is where budgets get built, and the actuals land in the next tab once the bookkeeper closes the month. Comparing the two is ten minutes of lookups that somebody postpones until the quarterly review, by which point three months of overspend are baked in. On the 8th the agent lines up the two tabs by line name, works out the variance for last month, and posts total spend against budget with the lines that ran over. If the actuals column is still empty, it says so and stops. ## 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 budget spreadsheet for the agent's Google account or service account, through a Google Sheets connector, an MCP server or the Sheets API. - An Actuals tab that someone fills in each month, for example a QuickBooks P&L export pasted by month. If something is missing, say what and stop; do not invent numbers. ### Steps 1. **Read both tabs** (Google Sheets): Call `spreadsheets.values.batchGet` with ranges `Budget!A1:M60` and `Actuals!A1:M60` and `valueRenderOption=UNFORMATTED_VALUE`. Column A holds the line names and row 1 the months. 2. **Check the month is in**: Find last month's column. If more than two lines have no actual yet, stop and report that the actuals aren't complete instead of computing a misleading variance. 3. **Match the lines**: Join Budget and Actuals on the line name, trimmed and case-insensitive. List any line that exists on one tab only, since that is usually a renamed or new category. 4. **Compute variances**: For each line and the total: actual minus budget, and actual as a percent of budget. Flag lines more than 10% and $1,000 over, and underspends of 30% or more that may only be timing. 5. **Write the report**: Total actual against budget, the lines over with their amounts, the big underspends, and any unmatched lines. ## Route B: the browser ### What you need - Signed in to Google with an account that has at least Viewer access to the budget spreadsheet. 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 both tabs**: Open `https://docs.google.com/spreadsheets/`, open the budget spreadsheet and click the **Budget** tab. Line names are in column A and months across row 1; find last month's column and read rows 1 to 60. Then do the same on the **Actuals** tab. 2. **Check the month is in**: If more than two lines on Actuals have no value in last month's column, stop and report that the actuals are not complete yet rather than computing a misleading variance. 3. **Match the lines**: Join the two tabs on the line name, ignoring case and extra spaces. Note any line that exists on one tab only; it is usually a renamed or new category. 4. **Compute the variances**: For each line and the total: actual minus budget, and actual as a percent of budget. Flag lines more than 10% and $1,000 over, and underspends of 30% or more that may only be timing. Selecting a column shows its sum in the bottom-right corner, which is a quick check on the totals. 5. **Write the report**: Total spend against budget as a percent and in dollars, the lines over with their amounts, the big underspends, and the unmatched lines. Do not edit the sheet. ## The report - **Spent vs budget** for last month, as a percent and in dollars. - Lines over budget by more than 10% and $1,000, largest first. - Big underspends that may only be timing. - Lines found on one tab but not the other. ## 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-budget-vs-actual/", "item_key": "google-sheets-budget-vs-actual", "project": "Acme", "source": "Google Sheets budget vs actual skill", "kind": "metric", "title": "September spend 4% over budget", "metric": { "label": "Spent vs budget", "value": 104, "unit": "%", "better": "lower", "period_start": "", "period_end": "" }, "glance": [ { "type": "Grid", "props": { "columns": 3 }, "children": [ { "type": "Stat", "props": { "label": "Budget", "value": 182000, "prefix": "$" } }, { "type": "Stat", "props": { "label": "Actual", "value": 189280, "prefix": "$" } }, { "type": "Stat", "props": { "label": "Over by", "value": 7280, "prefix": "$", "tone": "warn" } } ] } ], "widget": [ { "type": "Table", "props": { "columns": [ "Line", "Budget", "Actual", "Variance" ], "align": [ "left", "right", "right", "right" ], "rows": [ [ "Contractors", "$18,000", "$22,400", "+$4,400 (24%)" ], [ "Advertising", "$24,000", "$26,900", "+$2,900 (12%)" ], [ "Software", "$9,500", "$11,180", "+$1,680 (18%)" ], [ "Other", "$5,000", "$5,440", "+$440 (9%)" ], [ "Payroll", "$112,000", "$112,000", "$0" ], [ "Rent", "$8,500", "$8,500", "$0" ], [ "Travel", "$5,000", "$2,860", "-$2,140 (43%)" ], [ "Total", "$182,000", "$189,280", "+$7,280 (4%)" ] ] } }, { "type": "Callout", "props": { "text": "Contractors ran $4,400 over for the September design sprint, which isn't in the budget. Travel is 43% under, likely timing: the offsite moved to November.", "tone": "warn" } } ], "size": "md" } JSON ``` Widgets come from the Briefkin catalog (https://briefkin.dev/v1/catalog). Without a token, just give the user the report. Set `period_start` and `period_end` to the window the number covers: the tile names it from them ("Last 7 days", "Today", "This month"). For a count right now (open items, a balance), send `"period_label": ""` so no period shows; for another name, send it as `period_label` (at most 24 characters). ## Run it on a schedule Suggested: 8th of the month at 9:00 (`0 9 8 * *`). From cron, with the agent signed in on that machine: ```bash 0 9 8 * * claude -p "Run the google-sheets-budget-vs-actual 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-budget-vs-actual#schedule Source: https://briefkin.dev/skills/google-sheets-budget-vs-actual