Google Sheets adapter

The Google Sheets adapter reads rows from a spreadsheet and queries them in memory. It’s a great low-friction option for small catalogs (up to ~2,000 rows).

Two auth modes:

• API key + public sheet (simplest) — the sheet is shared with “Anyone with the link”.
• Service account (more private) — a bot account with Viewer access to the sheet.

Config shape

API key mode:

{
  "type": "google-sheets",
  "config": {
    "spreadsheetId": "1A2B3C4D5E6F7G8H9I0J",
    "apiKey": "AIzaXXXXXXXXXXXXXXXX"
  },
  "collections": {
    "menu": {
      "source": "Menu Items",
      "searchable_fields": ["Name", "Description"],
      "filterable_fields": ["Category", "Price"],
      "display_fields": ["Name", "Price", "Category"]
    }
  }
}

Service account mode:

{
  "type": "google-sheets",
  "config": {
    "spreadsheetId": "1A2B3C4D5E6F7G8H9I0J",
    "serviceAccount": {
      "clientEmail": "spelo-readonly@proj.iam.gserviceaccount.com",
      "privateKey": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n"
    }
  }
}

Setup (API key mode)

1. Share the sheet

   Open your Google Sheet → Share → change to Anyone with the link — Viewer.

2. Create an API key

   Google Cloud Console → APIs & Services → Library → enable Google Sheets API.

   Then Credentials → Create Credentials → API Key. Copy it.

3. Restrict the API key (important!)

   In the key’s settings:

   • API restrictions → Restrict key → check Google Sheets API only.
   • Application restrictions → IP addresses → add Spelo’s egress IPs.

4. Copy the spreadsheet ID

   From the sheet URL: https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID>/edit — that’s the ID.

5. Paste in the dashboard

   Dashboard → Data → Google Sheets → paste spreadsheet ID, API key → Test connection.

6. Map collections

   source is the tab name (e.g. Menu Items). The adapter reads the first row as headers.

Setup (service account mode)

1. Create a service account with Viewer role on Google Drive (or no roles — you’ll grant sheet access manually).
2. Download the JSON key.
3. Share the sheet with the service account’s email (it looks like ...@proj.iam.gserviceaccount.com). Grant Viewer.
4. Paste the client_email and private_key in the dashboard.

How filtering works

The adapter fetches the whole tab (up to the row cap — 10,000 by default), then applies filters, search, sort, and limit in memory using the same helpers as the JSON adapter. This is fine for small sheets. For 10K+ rows, move to Postgres or Supabase.

Operator	Behavior
eq, neq	Exact match, case-sensitive for strings
gt, gte, lt, lte	Numeric comparison (parses cell as Number)
contains (string)	Case-insensitive substring
contains (array, i.e. comma-separated cell)	Value ∈ split
in	Value ∈ list
free-text query	Case-insensitive substring across all searchable_fields

Headers and types

The first row of each tab is the header row. Columns without a header are ignored. Everything else is read as a string; numeric cells are auto-parsed to numbers. Dates are left as strings — the AI can compare ISO dates lexicographically, or you can emit them as YYYY-MM-DD.

Tip

Keep your sheet lean. Delete empty rows at the bottom, and don’t mix value types within a column — the AI gets confused when column Price has some numbers and some strings like “call for quote”.

Security notes

• API key scoped to Sheets API only + IP-restricted means leaking it gives read access to that one sheet (which you already made public anyway).
• Service account model is more private — sheet stays unshared publicly.
• Adapter never calls any write endpoint.

Troubleshooting

• 403 API key not valid → your key has “Application restrictions” that reject our IP. Add Spelo’s egress IPs, or temporarily remove the restriction for testing.
• 400 Unable to parse range → the tab name in source has a typo, or contains a single quote. Rename the tab to something simple.
• 404 Requested entity was not found → wrong spreadsheetId, or the sheet isn’t shared publicly / with the service account.
• Results look jumbled → your first row isn’t headers, or columns have blank headers.

More: Database connection errors.

Connect a different data source

Overview Postgres MySQL MS SQL MongoDB Supabase Firebase OAuth setup Airtable Shopify WooCommerce REST API Webhook TS adapter Schema introspection