Featured image: “UR16e robot arm” by Auledas, licensed under CC BY-SA 4.0.
The setup is three pieces working together: a small snippet added to your existing Claude API code that reads the token counts out of each response, a Zapier “Catch Hook” trigger that receives that data over a webhook, and a Google Sheets action that appends it as a new row. None of the three pieces is complicated on its own; the steps below cover each one plus the specific mistakes that cause rows to show up blank or costs to come out wrong.
What You’re Actually Building
Every Claude API response includes a usage object with the token counts for that specific call, but it reports tokens, not dollars, and it has no memory of the call before it. The pipeline below closes both gaps: your own code converts tokens to a dollar figure using a pricing table you maintain, and the Google Sheet becomes the running log that the API itself doesn’t keep. Zapier’s role is just the plumbing in between — it doesn’t need to understand what a token is, it only needs to catch a JSON payload and drop it into a spreadsheet row.
Step 1: Create the Catch Hook Trigger in Zapier
In the Zap editor, add a trigger and choose Webhooks by Zapier, then select the Catch Hook event. Zapier generates a unique URL in the form https://hooks.zapier.com/hooks/catch/[your-id]/[hook-id]/ — copy it from the Test tab. This URL is what your application code will send data to; Zapier returns a 200 response by default once it receives a request; appending silent/ to the URL suppresses the response body if your code doesn’t need to read it.
Step 2: Compute Cost From the usage Object
Anthropic’s Messages API returns a usage object on every response with, among other fields, input_tokens, output_tokens, cache_creation_input_tokens, and cache_read_input_tokens. The snippet below turns those counts into a dollar figure using a pricing table you keep in your own code, since the API response itself never includes a price — only Anthropic’s pricing page does, and it changes often enough that hardcoding it once and forgetting about it is the single most common way this kind of logging drifts out of date.
PRICING = {
"claude-sonnet-5": {"input": 2.00, "output": 10.00, "cache_write": 2.50, "cache_read": 0.20},
"claude-opus-5-5": {"input": 4.00, "output": 20.00, "cache_write": 5.00, "cache_read": 0.20},
# Add every model you actually call — prices are per million tokens
}
def compute_cost(model, usage):
rates = PRICING[model]
cache_write = usage.get("cache_creation_input_tokens", 0)
cache_read = usage.get("cache_read_input_tokens", 0)
regular_input = usage["input_tokens"]
output = usage["output_tokens"]
cost = (
regular_input * rates["input"]
+ output * rates["output"]
+ cache_write * rates["cache_write"]
+ cache_read * rates["cache_read"]
) / 1_000_000
return round(cost, 6)
Why each part is there: .get() with a default of 0 on the two cache fields exists because a response that didn’t use caching omits or zeroes them, and a plain usage[“cache_creation_input_tokens”] lookup would throw a KeyError on exactly the calls that cost the least to log. The division by 1,000,000 matches how Anthropic quotes prices — per million tokens — so skipping it is the most common source of a cost column that’s off by six orders of magnitude. For the full mechanics of setting cache_control on a request in the first place, see our working example of prompt caching breakpoints in the Claude API.
Step 3: Send the Webhook After Every API Call
import requests, datetime
response = client.messages.create(model=MODEL, max_tokens=1024, messages=messages)
cost = compute_cost(MODEL, response.usage.model_dump())
requests.post(ZAPIER_WEBHOOK_URL, json={
"timestamp": datetime.datetime.utcnow().isoformat(),
"model": MODEL,
"input_tokens": response.usage.input_tokens,
"output_tokens": response.usage.output_tokens,
"cache_read_tokens": response.usage.cache_read_input_tokens,
"cost_usd": cost,
"request_tag": TAG,
}, timeout=5)
The timeout=5 matters more than it looks: without it, a slow or hung webhook call can block your actual application response while it waits on Zapier, turning a logging feature into a reliability problem for the thing it’s supposed to be monitoring. The request_tag field is optional but worth adding early — a string identifying which feature or endpoint made the call — because splitting cost by feature later, once the sheet has thousands of rows, is far easier than retrofitting that distinction after the fact.
Step 4: Map the Fields to Google Sheets Columns
After your code sends one real request and you click “Test trigger” in the Zap editor, Zapier parses the JSON body into individual fields you can select in later steps. Add a Google Sheets “Create Spreadsheet Row” action, choose the target spreadsheet and worksheet, and map each field — timestamp, model, input_tokens, output_tokens, cache_read_tokens, cost_usd, request_tag — to its own column. Turn the Zap on only after this test row looks right; testing with the Zap already live risks a flood of blank rows from every payload your code sends before the field mapping is finalized.
Common Mistakes and How to Catch Them
- Blank rows appearing in the sheet: usually means the test payload sent before mapping fields had a different shape than later real payloads — re-run “Test trigger” with a real request after any change to the JSON you send, not just once at setup.
- Cost column showing zero for cached requests: check that cache_read_input_tokens is being read from the actual response object, not assumed to equal input_tokens; a cache hit reports a near-zero input_tokens and the real token count under the cache field instead.
- Costs quietly going stale after a model price change: the PRICING dictionary in Step 2 is the single point of failure here — Anthropic’s per-model rates change, most recently with new model releases, and nothing in this pipeline will warn you when your hardcoded numbers fall out of date. Our comparison of rate limits and pricing tiers across major AI APIs is a reasonable place to check current numbers periodically.
- Webhook calls silently failing under load: wrap the requests.post call in a try/except that logs locally rather than raising, so a Zapier outage degrades your cost logging instead of your actual feature.
Treat the Webhook URL as a Secret
Zapier’s Catch Hook URL contains no authentication of its own beyond the URL itself being hard to guess — anyone who obtains it can post arbitrary data into your Google Sheet. Store it the same way you’d store an API key: in an environment variable or secrets manager, never committed to source control, and never logged in plaintext in application logs that a broader team can read. If the Zap gets transferred to a different Zapier account, the hook ID portion of the URL changes, which is also worth knowing before a routine account migration quietly breaks every call this pipeline depends on. There’s no built-in signature verification on the receiving end the way some webhook providers offer, so the practical mitigation is keeping the URL itself confidential rather than trying to validate the sender afterward.
A Worked Example of What Lands in the Sheet
Say a support-ticket summarization feature calls Claude Sonnet 5 with a 3,000-token input and a 400-token output, no caching involved. Using the pricing table from Step 2, the cost works out to (3,000 × $2.00 + 400 × $10.00) ÷ 1,000,000 = $0.01, and that’s the exact figure that lands in the cost_usd column for that row, tagged with whatever request_tag the calling code set — “support-summarizer,” for instance. Multiply that by call volume over a month and the sheet becomes something you can actually pivot on: total cost by model, by day, or by feature, using nothing more than a spreadsheet pivot table against columns that were populated automatically. That’s the entire point of building this instead of checking a billing dashboard that only reports totals, with no way to see which feature or endpoint is driving them.
What Happens When Zapier Is Down or Rate-Limited
Zapier’s free and lower-tier plans cap how many tasks a Zap can run per month, and a Catch Hook trigger that fires on every single API call can burn through that cap faster than expected on a busy integration, especially once Step 6’s batching alternative isn’t in place yet. When the cap is hit, new webhook calls either queue or fail outright depending on the plan, and because the try/except wrapper from the mistakes section above swallows that failure locally, the realistic failure mode isn’t a crash — it’s a silently incomplete spreadsheet that looks fine until someone compares its totals against the real Anthropic billing dashboard and finds a gap. Checking that the row count in the sheet roughly tracks actual request volume, on some regular cadence, is the cheap way to catch this before the gap grows large enough to matter.
Running This on a Schedule Instead of Per-Request
Sending a webhook on every single API call is the simplest version, but it also means a spreadsheet row per call, which gets noisy fast on a high-volume integration. An alternative is to accumulate usage in memory or a local file and send one summarized webhook per hour or per day instead — which pairs naturally with scheduling a recurring Claude API call with cron, since the same cron job pattern that triggers a scheduled API call can just as easily trigger a batched cost-reporting job on its own schedule, separate from the calls it’s reporting on.
