Function reference

Thirteen functions. Twelve never leave the sheet.

Every function below carries in-sheet help, accepts a cell or a range where it makes sense, and returns a spilling array when there is more than one value. Only JSON_IMPORT makes a network request.

Local · unmetered · no sign-in Counts against your monthly requests
Read a value
JSON_VALUE(json, path, [default])

One scalar at a path. Returns the default, or blank, when the path is missing.

JSON_QUERY(json, path)

Whatever the path selects — a scalar, or a spilling column of matches for a wildcard or JMESPath expression.

Inspect
JSON_KEYS(json, [path])

The keys of an object as a spilling column, in source order.

JSON_LENGTH(json, [path])

Item count for an array, key count for an object, character count for a string.

JSON_TYPE(json, [path])

object, array, string, number, boolean, or null — useful for auditing a heterogeneous feed.

JSON_VALID(json)

TRUE or FALSE. Never throws, so it is safe to wrap around a messy column.

JSON_SCHEMA(json, [path], [options])

A two-column map of every discovered path and its inferred type. The fastest way to understand a payload you were handed.

Reshape and convert
JSON_FLATTEN(json, [path], [options])

Two columns: every leaf path and its value. Good for auditing one deep record.

The workhorse. An array of objects becomes a header row plus data rows, with a deterministic union of columns and your chosen array mode.

TABLE_TO_JSON(range, [options])

The reverse trip. A range with headers becomes an array of objects, an object, or row arrays — compact or pretty, with optional nesting from dot-notation headers.

Format
JSON_PRETTY(json, [indent])

Readable, indented JSON in one cell. Two spaces by default.

JSON_MINIFY(json)

Whitespace removed, ready to send somewhere.

Fetch
JSON_IMPORT(url, [path], [options], [refresh_key])

A public GET, spilled into the sheet. GET only, no credentials in arguments — for anything authenticated, use the sidebar. Change refresh_key to force a fresh fetch.

The options string

One argument, semicolon separated, parsed strictly. An unknown key is an error, not a silent no-op.

array=rows;flatten=true;maxRows=1000
Key Default Effect
array join rows, join, or json
delimiter ", " Separator used by join mode
flatten true Nested objects become dot-notation columns
headers true Include the header row in the output
nulls blank blank, or the literal text null
numbers strict strict keeps numeric strings as text; coerce converts
dates text text, or parse ISO-8601 into real dates
maxRows plan limit Fail rather than write more than this
shape array TABLE_TO_JSON only — array, object, or rows
cache 3600 JSON_IMPORT only — TTL in seconds, capped by plan

Conventions worth knowing

Errors are prefixed

Every thrown message starts with JSON for Sheets: so you can tell our errors from Sheets' own.

Output is deterministic

The same input always produces the same columns in the same order. Nothing volatile is used internally, so recalculation does not reshuffle your sheet.

Ranges are batched

Pass a whole column and it is processed in one pass, not one execution per cell.

See a function in full.

JSON_TABLE, with worked examples and every option.

JSON_TABLE reference