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

> Query a Google Sheet over REST with filters, group-by and totals, kept in sync with the sheet.

Source: https://datatomcp.com/guides/google-sheets-as-api · By Killian Helf · Updated 2026-10-19

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](https://app.datatomcp.com/login), **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:

```bash
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:

```bash
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):

```bash
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.