Mock APIs for Google Sheets — one IMPORTDATA formula, no Apps Script

Google Sheets has no native JSON import. Ask how to pull an API into a sheet and the standard answers are: paste a 200-line ImportJSON Apps Script from a decade-old gist, install an add-on, or copy-paste and hope the data never changes. All of that machinery exists to work around one gap — because the function Sheets does ship, IMPORTDATA, only speaks CSV and TSV.

So serve the API as CSV. Every Mockbird endpoint does that natively: add ?mock_format=csv to any list or single-record GET and you get RFC-4180 CSV with a header row — columns picked server-side, sorting and filtering done in the URL, all the simulation params still available. One formula, zero scripts, zero add-ons, no signup. Useful for prototyping a dashboard before the real backend exists, teaching a spreadsheet class against realistic shared data, or giving formula practice targets that actually change. Every curl below was verified against production before publishing.

1. One formula against the demo API

The public demo project is a seeded e-commerce API. In any sheet, paste:

=IMPORTDATA("https://mockbird.mockbird.workers.dev/m/demo/products?mock_format=csv&select=name,price,category,inStock&limit=100")

The first time a sheet uses an import function, Sheets shows a warning banner (the sheet is about to talk to an external URL) — click Allow access and the table fills in: a header row plus one row per product, spilling down and across from the formula cell. What the sheet received is exactly what curl receives:

curl 'https://mockbird.mockbird.workers.dev/m/demo/products?mock_format=csv&select=name,price,category,inStock&limit=3'
# id,name,price,category,inStock
# 1,Bridge Area Night,358.76,clothing,true
# 2,World Thing System Point,153.79,toys,false
# 3,Field Thing Money,99.19,beauty,false

Response content-type is text/csv; charset=utf-8. Sending Accept: text/csv instead of the query param works too (for clients that can set headers — Sheets can't, which is why everything in this guide rides in the URL).

2. Pick columns in the URL, not with formulas

?select= projects the columns server-side: id always comes first, then your fields in the order you listed them. That means column order in the sheet is stable and chosen by you — no QUERY(A:H, "select B, D") reshuffling after import:

# just name + price, in that order after id
curl 'https://mockbird.mockbird.workers.dev/m/demo/products/1?mock_format=csv&select=name,price'
# id,name,price
# 1,Bridge Area Night,358.76

That's a single-record GET — one row plus header, handy for a lookup cell. Nested values (objects/arrays in a record) are JSON-stringified into their cell rather than exploded.

3. Sort, filter, and search before the data hits the sheet

The usual pattern is IMPORTDATA-everything-then-QUERY-it. You can skip the second step: the mock applies filters, sort, and free-text search before generating the CSV, so the import is already the view you want — and smaller imports keep the sheet fast (Google's own guidance is to minimize what import functions pull).

# top 3 by price, descending
curl 'https://mockbird.mockbird.workers.dev/m/demo/products?mock_format=csv&select=name,price&sortBy=price&order=desc&limit=3'
# id,name,price
# 7,Mountain Part Law,926.11
# 17,House Game Year Place Thing,913.03
# 10,Problem Law Line Car Problem,792.85

# range filter: only products ≥ 900
curl 'https://mockbird.mockbird.workers.dev/m/demo/products?mock_format=csv&select=name,price&price_gte=900'
# id,name,price
# 7,Mountain Part Law,926.11
# 17,House Game Year Place Thing,913.03

# substring filter + free-text search work too
…?mock_format=csv&category_like=cloth
…?mock_format=csv&q=law

All three verified above against production. The full toolkit — exact-match field filters, _gte/_lte/_gt/_lt/_ne/_like suffixes, q= search — is in the docs parameter table, and every bit of it composes with mock_format=csv.

Because the URL is the query, you can also build it from cells the spreadsheet way — no code, just string concatenation into IMPORTDATA:

=IMPORTDATA("https://mockbird.mockbird.workers.dev/m/demo/products?mock_format=csv&select=name,price&category=" & B1)

Change the category in B1, the import refetches with the new URL. (One caveat below: the cell you reference can't contain a volatile function like NOW().)

4. More than 100 rows

List endpoints page at up to 100 rows per request (limit ≤ 100, default page size smaller — always pass an explicit limit). For more, add page=2, page=3… in stacked formulas a few rows apart, or in separate tabs. Resources hold up to 1,000 records, so ten formulas covers the worst case. The JSON API exposes the total in an x-total-count response header, but Sheets can't read headers — another reason this guide keeps everything in the URL and the body.

5. Turn your own CSV into the API, then import it back live

The demo is shared and reseeds daily. Your own project takes one curl (or one click), no signup:

# File → Download → CSV in Sheets, then:
curl -s -X POST 'https://mockbird.mockbird.workers.dev/api/projects/import?resource=people' \
  --data-binary @people.csv
# → {"id":"abc123xyz9", "adminKey":"…", ...}

Rows become records with typed fields (CSV import guide), and the collection is immediately a REST API — which your sheet can IMPORTDATA right back, now with server-side filters, a data editor, and the same data feeding Power BI, Grafana, or a teammate's app. Sheet → API → many sheets is a genuinely useful demo-day trick: edit a record in the dashboard, and every sheet importing it picks the change up on its next refresh.

6. Refresh: what actually triggers a refetch

Google documents the rules, and they surprise most people:

Action	Refetches?
Sheet stays open	✔ automatically, roughly every hour
Delete the formula cell and re-add it (or overwrite it with the same formula)	✔ immediately
Close and reopen / reload the sheet	✘ does not trigger a refresh

And the classic cache-buster trick — appending &x=" & NOW() to the URL — is explicitly blocked: import functions may not reference volatile functions (NOW, RAND, RANDARRAY, RANDBETWEEN), and the cell shows #ERROR! if you try. Google's documented exception is TODAY(), which updates at most once a day. The reliable manual refresh is the unglamorous one: cut the formula cell, paste it back.

7. Rehearse the failure case with IFERROR

A sheet that feeds on an external URL will eventually meet that URL failing — and IMPORTDATA surfaces that as an in-cell error that wipes out the imported range. Wrap it before that happens, and use mock_status to prove the wrapper works:

=IFERROR(
  IMPORTDATA("https://mockbird.mockbird.workers.dev/m/demo/products?mock_format=csv&select=name,price&limit=100&mock_status=500"),
  "source down — showing nothing rather than something wrong")

Verified server-side: mock_status=500 makes that URL return HTTP 500 (body: {"error":"simulated 500 error (mock_status)"}), so the import fails and the fallback string renders. Delete &mock_status=500 and the data comes back on the next refetch. That's the whole error-handling test loop for a spreadsheet, run on demand instead of during a real outage.

8. Protected APIs: tokens in the URL, because Sheets can't send headers

IMPORTDATA sends a bare GET — no way to attach an Authorization header. For a project in protected mode, Mockbird accepts the token as a query param exactly for header-less clients like this:

# mint a token (any email/password — it's a mock; max lifetime 7 days)
curl -s -X POST https://mockbird.mockbird.workers.dev/m/YOUR_PROJECT/auth/login \
  -H 'content-type: application/json' \
  -d '{"email":"sheets@example.com","password":"x","expiresIn":604800}'
# → {"token":"eyJ…", ...}

=IMPORTDATA("https://mockbird.mockbird.workers.dev/m/YOUR_PROJECT/products?mock_format=csv&mock_token=eyJ…")

Verified above: without the token the CSV URL returns 401; with ?mock_token= it serves the rows. Honest caveat: tokens expire (7 days max), so a long-lived sheet needs a re-mint — put the token in one cell and reference it from the formula so it's a one-cell fix. If the sheet is shared, remember the URL (token included) is visible to every viewer; for classroom data, an unprotected project is simpler.

Where other tools are still the right answer

Fair's fair. If the data lives in another spreadsheet, use IMPORTRANGE; if it's in BigQuery and you're on Workspace Enterprise, Connected Sheets is the real thing. If you need to sync a third-party JSON API you don't control into Sheets on a schedule, that's Apps Script UrlFetchApp or a connector product — IMPORTDATA can't send headers or parse JSON, and Mockbird only reshapes APIs it hosts. And the hourly refresh cadence means none of this is a realtime feed. Reach for the mock when the point is a live, controllable, realistic dataset in a sheet — prototyping, teaching, testing formulas against data that changes — without writing or trusting any script.

Verification: all demo curls on this page (the CSV list with select=name,price,category,inStock, the single-record CSV, sortBy=price&order=desc top-3, the price_gte=900 range filter, category_like and q= variants, Accept: text/csv content negotiation, limit=100 paging, mock_status=500 returning HTTP 500, and a protected-project 401-without / 200-with-?mock_token= drill against a scratch project, deleted after) were run against production on 20 Sep 2026 before publishing. Demo data reseeds daily, so the literal values in your import will differ from the outputs shown. Google Sheets behavior — IMPORTDATA's CSV/TSV-only scope and syntax, the first-use Allow-access banner, the hourly auto-refresh while a document is open, reload-not-refreshing, the volatile-function restriction with the TODAY exception — is from Google's official Docs Editors documentation as of September 2026. We don't run your Google account; if a step here doesn't work in your sheet, that's a bug: tell us.