Documentation

Install

Install the add-on from the Google Workspace Marketplace and accept the three permissions (see privacy).
Open a spreadsheet. The functions work as soon as you type them, and the menu appears under Extensions → JSON Importer.

People who open a shared sheet without the add-on installed may see #NAME? in the cells that use its functions.

Install from Google Workspace Marketplace

The Marketplace listing is going through review. The install link will appear here once it is published.

Request builder

Open Extensions → JSON Importer → Request builder. Choose the method (GET, POST, PUT, PATCH or DELETE), the URL, query parameters, headers and a JSON body. Choose a path syntax (ImportJSON paths or JSONPath) and options, press Preview to see the first rows, and Insert at selected cell to write the result.

Saved requests are kept per spreadsheet (up to 3 in the free version). Refresh all saved requests and Refresh request at selected cell in the menu run them again.

Authentication

The builder supports an API key in a header, an API key in the query string, and a Bearer token. The key is stored in your user properties in your Google account, never in the sheet and never on our servers. The formulas have no way to carry a secret, so use the builder for APIs that need a key. Putting a key inside a formula URL would leave it visible in the sheet.

IMPORTJSON

=IMPORTJSON(url, [query], [options])

url: the address of the JSON, starting with http:// or https://.
query (optional): paths to import, separated by commas, such as "/data/items,/meta". Empty imports everything.
options (optional): comma separated, see options.

Headers come from the paths (title-cased unless rawHeaders), each object in an array becomes a row, and empty cells inherit the value of the row above unless noInherit.

Paths match by prefix. /state also matches /state_reason. Choose paths that no other field starts with, or use the full path.

IMPORTJSONPATH

=IMPORTJSONPATH(url, jsonpath, [options])

Extracts data with a JSONPath expression. Objects become one column per key (with a header row); scalar values become a single column headed Value. Supported subset:

Syntax	Meaning
`$`	The root of the document
`.key` or `['key']`	A field
`[n]`, `[-n]`	An array item (negative counts from the end)
`[]`, `.`	All items or fields
`..key`	The field at any depth
`[?(@.k==v)]`	Simple filter. Operators: == != < > <= >=. Literals: numbers, 'text', "text", true, false, null

Not supported: slices, unions and script expressions. An invalid path gives an error that names the problem.

Options

Option	Effect
`noInherit`	IMPORTJSON: leave inherited cells empty instead of repeating the value above
`noTruncate`	Do not cut values to 256 characters
`rawHeaders`	IMPORTJSON: headers are the raw paths (`/a/b`)
`noHeaders`	Do not output the header row
`allHeaders`	IMPORTJSON: columns follow the order of the paths in the query
`debugLocation`	IMPORTJSON: add a first row with the URL and the query
`cache=N`	Seconds to reuse the response, 0 to 21600. Default 300. GET only
`maxCells=N`	Cut the output at N cells (default 50,000) and add a warning row

=IMPORTJSON("https://example.com/api/items", "/id,/name", "noHeaders,cache=60")

Cache

Responses are cached for 5 minutes by default so that recalculations do not use your URL fetch quota. Use cache=0 to always fetch. Responses over about 90,000 characters are not cached, because Google's cache service limits each value to 100 KB.

Apps Script limits

Custom functions stop after 30 seconds; other executions after 6 minutes.
A response can be up to 50 MB. Values are cut to 256 characters unless noTruncate.
Google limits URL fetches per day (about 20,000 for consumer accounts and 100,000 for Workspace). The cache helps.
Requests come from Google's servers, so private addresses such as localhost or an intranet cannot be reached.
Google Sheets limits the total number of cells in a spreadsheet; the add-on cuts output at 50,000 cells by default.

Errors and fixes

In a formula an error shows as #ERROR! with a message in your language (English, Spanish, Portuguese, French or German). The message says what to do next.

Error	Meaning	What to do
HTTP 401 / 403	The API needs a key or refused it.	Add or fix the key in the request builder.
HTTP 404	Nothing at that address.	Check the URL for typos.
HTTP 429	The API is rate limiting you.	Wait a minute or use a larger cache= value.
HTTP 5xx	The API server failed.	Usually temporary: try again later.
Not valid JSON	The response is not JSON (often an HTML page).	Open the URL in a browser to see what it returns.
No results for the path	The path matched nothing.	Check the path, or remove it to import everything.
URL fetch quota	Google's daily limit was reached.	Use a larger cache= value or try tomorrow.
Took too long	Apps Script stops custom functions after 30 seconds.	Ask the API for less data.
Larger than 50 MB	The response is too big.	Use a query that returns less data.
Invalid URL	The URL must start with http:// or https://.	Correct the URL.
Unknown option	An option name is wrong.	Valid: noInherit, noTruncate, rawHeaders, noHeaders, allHeaders, debugLocation, cache=0-21600, maxCells=N.
Output cut	The result exceeded maxCells.	Use a narrower path or raise maxCells=N.

Migrate from the ImportJSON script

Install the add-on. Syntax and options are the same, so existing formulas keep working.
Delete or rename the old ImportJSON function in the sheet's Apps Script project, so two functions do not share a name.
Differences: 0 and false are kept as values, cache and maxCells are new options, and results are cached for 5 minutes by default.
Not included: ImportJSONViaPost and ImportJSONAdvanced. Use the request builder for POST requests and headers.

Pro (coming soon)

The free add-on stays free. Planned for Pro: scheduled refresh, automatic pagination, unlimited saved requests and OAuth 2.0. Join the waitlist.