datatomcp

Use Google Sheets as a read-only API with filters and aggregates

By Killian Helf, founder · Updated 19 October 2026 · Markdown

Google Sheets is a fine place to maintain data and an awkward place to query it from code. The Sheets API returns cell ranges; you fetch everything and filter in your app. Sheet-to-API tools fix the format but usually still return all rows. This guide gives you a read-only API with filters, group-by and totals computed server-side, kept in sync with the sheet.

When this is the right tool

  • A website or app reads a catalog, price list or directory maintained in a sheet.
  • An automation needs “rows where status is open” or “revenue per rep”, not the whole tab.
  • Several tools or agents should read the same, current copy.

If you need to write back to the sheet, use the Sheets API or a read/write tool instead. datatomcp is deliberately read-only.

1. Connect the sheet

Sign in at app.datatomcp.com, Add data → Google Sheet, pick the spreadsheet. Values are read unformatted, so $1,200.00 arrives as 1200 and dates as dates. The import report flags columns that mix types before anything is published.

2. Get an API key

Account → API keys → Create key. Use it as Authorization: Bearer dtm_….

3. Query

Rows matching a filter:

curl https://app.datatomcp.com/v1/datasets/ds_abc123/query \
  -H "Authorization: Bearer $DATATOMCP_KEY" \
  -H "Content-Type: application/json" \
  -d '{"where":[{"column":"status","op":"eq","value":"open"}],"limit":50}'

Totals per group:

curl https://app.datatomcp.com/v1/datasets/ds_abc123/query \
  -H "Authorization: Bearer $DATATOMCP_KEY" \
  -H "Content-Type: application/json" \
  -d '{"group_by":["owner"],"metrics":[{"fn":"count","as":"deals"},{"fn":"sum","column":"amount","as":"pipeline"}]}'

The distinct values of a column (useful for building filter dropdowns):

curl https://app.datatomcp.com/v1/datasets/ds_abc123/values/status \
  -H "Authorization: Bearer $DATATOMCP_KEY"

Keeping it in sync

  • Refresh now in the app, or POST /v1/datasets/:id/refresh from a script or workflow.
  • On Pro, datatomcp re-reads the sheet every hour and publishes a new version only when the content changed.
  • Every response includes meta.version and meta.refreshed_at, so consumers can tell how fresh the data is.
  • If a refresh fails (the sheet was deleted or access removed), the API keeps serving the last good version and you get an email.

Limits

Free: 3 datasets, 100,000 rows each, 1,000 queries a day. Pro ($12 or €12 a month): 25 datasets, 2 million rows each, 50,000 queries a day and hourly sync.