Zapier: sum and filter a big Google Sheet in a Zap

By Killian, founder · Updated 27 October 2026 · Markdown

Zapier’s Google Sheets actions find rows; they don’t add them up. Lookup Spreadsheet Row returns one match, Lookup Spreadsheet Rows (Advanced) returns up to 500, and Get Many Spreadsheet Rows (Advanced) up to 1,500. To get a filtered total or a group-by over a bigger sheet in a Zap, send the question to an API that queries the sheet and returns only the answer. This guide does that with one Webhooks by Zapier → Custom Request step and datatomcp, which keeps a synced, queryable copy of your sheet or CSV.

When you need this

“Total revenue for this customer” when the customer has 2,000 order rows.
A daily Slack message with sales per country from a 50,000-row sheet.
Counting rows that match several conditions (status is “open” and the date is last week).

If you only need one row by an exact value, Zapier’s built-in lookup is enough.

1. Make the sheet queryable

Sign in at app.datatomcp.com, open Add data → Google Sheet and pick the sheet. Private sheets work: datatomcp gets access to that file only. A CSV upload or CSV link works too.
On the dataset page, note the dataset id (ds_…) and the column ids in the columns table.
Under Account → API keys, create a key named zapier. It starts with dtm_. Copy it now; it won’t be shown again.

2. Add a Custom Request step

Add an action step, choose Webhooks by Zapier and the Custom Request event. Webhooks by Zapier is a premium app, so it needs a paid Zapier plan.

Field	Value
Method	POST
URL	`https://app.datatomcp.com/v1/datasets/ds_YOUR_ID/query`
Data	the JSON query (below)
Unflatten	No
Headers	`Authorization`: `Bearer dtm_…` and `Content-Type`: `application/json`

Leave Basic Auth empty.

3. Write the query

Orders and revenue for one customer, with the email mapped from the trigger:

{
  "where": [
    { "column": "customer_email", "op": "eq", "value": "{{trigger email}}" }
  ],
  "metrics": [
    { "fn": "count", "as": "orders" },
    { "fn": "sum", "column": "total", "as": "revenue" }
  ]
}

Replace {{trigger email}} by inserting the field from your trigger step in the Data box. Keep the quotes around text values.

Revenue per country last week, for a scheduled Zap:

{
  "where": [
    { "column": "order_date", "op": "between", "value": ["2026-10-05", "2026-10-11"] }
  ],
  "group_by": ["country"],
  "metrics": [{ "fn": "sum", "column": "total", "as": "revenue" }],
  "order_by": [{ "column": "revenue", "dir": "desc" }]
}

Filter ops: eq, neq, gt, gte, lt, lte, in, not_in, contains, starts_with, between, is_null, not_null. Metrics: count, count_distinct, sum, avg, min, max. For rows instead of totals, use select, order_by and limit (up to 1,000).

4. Use the result

Test the step. The response has rows (the answer) and meta (rows_matched, truncated and the dataset version). Map the orders and revenue fields under rows into the next step, such as a Slack message, an email or a CRM update. For a group-by, Zapier shows each row’s fields as a list you can format with Formatter by Zapier or loop over with Looping by Zapier.

Keep the data current

Click Refresh now on the dataset page after the sheet changes, or add another Custom Request step with method POST to https://app.datatomcp.com/v1/datasets/ds_YOUR_ID/refresh before the query. On Pro, linked sheets sync every hour.

What about Zapier’s MCP Client?

Zapier has a beta MCP Client integration that runs tools on a remote MCP server inside a Zap, signing in with OAuth or a bearer token. datatomcp’s MCP endpoint is built for chat and coding agents that sign in with your account (Claude, ChatGPT, Cursor). For Zaps, the REST request above is the route we support and test. (Zapier MCP, the product with the similar name, works the other way round: it lets Claude or ChatGPT run your Zapier actions.)

To ask the same data questions from a chat app, see how to add a remote MCP server to any AI client.

Troubleshooting

401 “Invalid or revoked API key”. Check the header name is Authorization and the value is Bearer plus the key, with no line break.
400 “Invalid query”. The message names the problem, often an unknown column id. Ids are on the dataset page and can differ from the sheet’s header text. A broken JSON body (a missing quote around a mapped value) also gives a 400.
429. Daily query limit reached (1,000 on the free plan). It resets at 00:00 UTC.
Totals look wrong for text values. Filters match exactly. Get the real values with a GET to https://app.datatomcp.com/v1/datasets/ds_YOUR_ID/values/country.

FAQ

Can Zapier sum a column in Google Sheets? Not with the Google Sheets actions alone. You can sum up to 1,500 returned rows with Formatter or Code steps, or send a query like the one above and get the sum back.

Does it change my sheet? No. The API is read-only. Use Zapier’s Google Sheets actions to write rows.

Is it free? datatomcp’s free plan covers 3 datasets of up to 100,000 rows and 1,000 queries a day. Webhooks by Zapier needs a paid Zapier plan.