Function reference
Thirteen functions. Twelve never leave the sheet.
Twelve launch functions run locally and include in-sheet help. Functions return either one value, compact JSON text, or a spilling table according to their documented result. JSON_IMPORT is reserved but disabled until remote formulas have an account-safe credential design.
Local · unmetered · no sign-in
Remote · metered when enabled
| Function | Result | Request cost |
|---|---|---|
| Read a value | ||
| JSON_VALUE(json, path, [default]) | One scalar at a path, or compact JSON when the selected value is an object or array. A missing path returns the supplied default; without one it raises an error. | Local — no request |
| JSON_QUERY(json, path, [options]) | The selected scalar, object, array, wildcard result, or JMESPath result as compact JSON text in one cell. | Local — no request |
| Inspect | ||
| JSON_KEYS(json, [path]) | Object keys as a spilling column, in source order. | Local — no request |
| JSON_LENGTH(json, [path]) | Item count for an array, key count for an object, or character count for a string. | Local — no request |
| JSON_TYPE(json, [path]) | Returns object, array, string, number, boolean, or null. | Local — no request |
| JSON_VALID(json) | Returns TRUE or FALSE without throwing. | Local — no request |
| JSON_SCHEMA(json, [path]) | A three-column table of discovered paths, inferred types, and array counts where available. | Local — no request |
| Reshape and convert | ||
| JSON_FLATTEN(json, [path], [options]) | A spillable table with nested object paths expanded into columns. It currently uses the same table engine as JSON_TABLE. | Local — no request |
| JSON_TABLE(json, [path], [options]) | Turns an array of objects into a header row and structured data rows. | Local — no request |
| TABLE_TO_JSON(range, [options]) | Turns a range with headers into JSON objects or row arrays. | Local — no request |
| Format | ||
| JSON_PRETTY(json, [indent]) | Readable, indented JSON in one cell. | Local — no request |
| JSON_MINIFY(json) | JSON with unnecessary whitespace removed. | Local — no request |
| Fetch | ||
| JSON_IMPORT(url, [path], [options]) | Reserved until remote formulas have an account-safe credential design; use the sidebar today. | Remote request — currently disabled |
Read a value
Inspect
Reshape and convert
Format
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 | json | 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 |
| null | blank | blank, or the literal text null |
| maxRows | 25,000 | Local table ceiling; accepts 1 through 100,000 |
| shape | array | TABLE_TO_JSON only — array, object, or rows |
Key
Default
Effect
array
json
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
null
blank
blank, or the literal text null
maxColumns
250
Local table ceiling; accepts 1 through 1,000
maxCells
1,000,000
Local table ceiling; accepts 1 through 2,000,000
maxRows
25,000
Local table ceiling; accepts 1 through 100,000
shape
array
TABLE_TO_JSON only — array, object, or rows
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.