JSON
Lido provides formulas for creating, querying, and transforming JSON data directly in the spreadsheet. These are non-action formulas — they execute immediately and return computed values.
JQ
Queries a JSON string using jq syntax. Returns one or more values based on the query.
Syntax:
=JQ(json, command)
Parameters:
| Parameter | Description | Required |
|---|---|---|
json | A JSON string to query | Yes |
command | A jq query expression | Yes |
Examples:
=JQ('{"name": "Alice", "age": 30}', ".name")
→ "Alice"
=JQ('[1, 2, 3]', ".[1]")
→ 2
=JQ('{"users": [{"name": "A"}, {"name": "B"}]}', ".users[].name")
→ Returns multiple rows: "A", "B"
JQ is especially useful with FETCH for parsing API responses:
=JQ(FETCH("https://api.example.com/data", "GET"), ".results[].id")
CREATEJSONOBJECT
Creates a JSON object from arrays of keys and values.
Syntax:
=CREATEJSONOBJECT(keys, values)
Parameters:
| Parameter | Description | Required |
|---|---|---|
keys | Array of key names (e.g., a column range) | Yes |
values | Array of corresponding values | Yes |
Example:
=CREATEJSONOBJECT({"name", "age", "city"}, {"Alice", 30, "NYC"})
→ '{"name":"Alice","age":30,"city":"NYC"}'
Values are automatically typed: numbers stay as numbers, booleans as booleans, and strings that parse as JSON are embedded as JSON.
MERGEJSONOBJECTS
Merges multiple JSON objects into one. Later keys overwrite earlier ones.
Syntax:
=MERGEJSONOBJECTS(json1, json2, ...)
Parameters:
| Parameter | Description | Required |
|---|---|---|
json1, json2, ... | JSON strings to merge | Yes (at least one) |
Example:
=MERGEJSONOBJECTS('{"a":1}', '{"b":2}', '{"a":3}')
→ '{"a":3,"b":2}'
UNFOLDJSONOBJECTS
Expands JSON objects into a table (rows and columns). Each object becomes a row; each unique key becomes a column header.
Syntax:
=UNFOLDJSONOBJECTS(json1, json2, ...)
=UNFOLDJSONOBJECTS(json1, json2, ..., guess_string_type)
Parameters:
| Parameter | Description | Required |
|---|---|---|
json1, json2, ... | JSON strings to unfold | Yes |
guess_string_type | If FALSE, treats all values as strings (default: TRUE) | No |
Example:
=UNFOLDJSONOBJECTS('{"name":"Alice","age":30}', '{"name":"Bob","age":25}')
Returns a 3-row table (1 header + 2 data rows):
| name | age |
|---|---|
| Alice | 30 |
| Bob | 25 |
FOLDJSONOBJECTS
Converts a table (with headers) back into JSON objects — one JSON string per row.
Syntax:
=FOLDJSONOBJECTS(table)
Parameters:
| Parameter | Description | Required |
|---|---|---|
table | A 2D range where the first row contains column headers | Yes |
Returns one JSON string per data row, using the headers as keys.
RAWJSON
Parses a JSON string and expands it into spreadsheet cells.
Syntax:
=RAWJSON(json_string)
=RAWJSON(json_string, guess_string_type)
Parameters:
| Parameter | Description | Required |
|---|---|---|
json_string | A JSON string to parse | Yes |
guess_string_type | If FALSE, treats all values as strings (default: TRUE) | No |
- A JSON array of arrays becomes a 2D cell range
- A JSON array becomes a single row
- A scalar value becomes a single cell
Example:
=RAWJSON('[[1,2],[3,4]]')
→ 2×2 cell range with values 1, 2, 3, 4
TOJSON
Converts spreadsheet data to a JSON string.
Syntax:
=TOJSON(data)
=TOJSON(data, output_array)
Parameters:
| Parameter | Description | Required |
|---|---|---|
data | Cell, range, or value to convert to JSON | Yes |
output_array | If TRUE, always wraps the result as a JSON array | No |
- A single cell returns its value as a JSON scalar
- A single row or column returns a JSON array
- A 2D range returns a JSON array of arrays