· Simple Data Foundry Team · Guides · 4 min read
IMPORTJSON in Google Sheets: what to use instead
IMPORTJSON is not a Google Sheets function. It is a community script people paste from unmaintained gists. Here is why it breaks, and the routes that do not.
Short answer: there is no IMPORTJSON function in Google Sheets. Google ships IMPORTDATA, IMPORTXML, IMPORTHTML, IMPORTFEED and IMPORTRANGE — and nothing for JSON. Every IMPORTJSON tutorial is telling you to paste an Apps Script into your spreadsheet, which is why so many people end up searching for unknown function importjson.
Why you get “Unknown function: ‘IMPORTJSON’”
Because the function genuinely does not exist until you add it yourself. The script almost everyone pastes traces back to a gist published years ago. It is widely copied, rarely maintained, and it only exists inside the one spreadsheet you pasted it into.
That last part is what usually bites: copy the sheet, share it with a colleague, or rebuild the report next quarter, and the formula resolves to nothing again.
The four ways it breaks
1. It disappears when the sheet does
The script lives in that spreadsheet’s bound Apps Script project. A copy carries it; an export does not, and neither does a fresh sheet built from the same data.
2. Execution time limits
Apps Script caps custom function execution at roughly 30 seconds. A paginated API, a slow endpoint or a large response blows straight through it and you get an error where a number should be.
3. It cannot hold a secret safely
Most useful APIs need a key. In a custom function that key ends up in the formula itself, in the cell, visible to everyone the sheet is shared with, and carried along in every copy.
4. It only recalculates when Sheets feels like it
Custom functions recalculate on edit and on open, not on a schedule. There is no “refresh at 6am” — which is usually the actual requirement.
What to use instead
If your data is a JSON file at a URL
Use a File URL source. It reads CSV, TSV, JSON, JSONL and Excel over HTTP, handles gzip, zip, bz2 and xz, and re-reads on your schedule. No formula, no script, nothing living inside the spreadsheet.
If your data is behind an API
Use a REST API source. Point it at the endpoint, put the credential in a stored connection rather than in a cell, and set records_path to wherever the rows live in the response — data, results, items, whatever your API uses.
It handles pagination four ways: page, offset, cursor, or none at all. Up to 1,000 records per page, 50 pages per run by default.
One limitation worth stating plainly: cursor pagination reads the next cursor from a path inside the JSON response body. If your API returns its cursor in a Link response header instead — Shopify’s REST Admin API and the GitHub API both do — the cursor cannot be followed and you will get the first page only. Page or offset pagination is unaffected.
Flattening the nested bits
The other half of the JSON problem is shape. APIs return nested objects; a spreadsheet wants flat columns.
A response like {"data": [{"customer": {"name": "...", "email": "..."}}]} needs customer.name and customer.email as their own columns. The JSON flattener does this in your browser — paste a sample response, pick the array holding your rows, and see the flat columns you would get. It is the same step the REST source performs, made visible.
What this looks like once it is set up
- A source — a URL or an API endpoint — with the credential stored, not pasted.
- A destination: Google Sheets, Airtable, or Postgres.
- Column selection, filters and any computed fields.
- A schedule. The cron generator will read one back to you in plain English before you commit to it.
The sheet then updates on its own, and there is no formula in it that a copy can break.
Still want the formula route?
IMPORTDATA genuinely works and needs no script — but only for CSV and TSV at a public URL. If your data is already CSV and genuinely public, it is a reasonable answer. It will not parse JSON, it will not authenticate, and it is capped at 50 IMPORT formulas per spreadsheet.
For anything with a key, a schedule or a nested shape, the formula layer is the wrong layer.
