JSONTORANGE

Converts a json text to a range of cells.

Syntax

JSONTORANGE(values, keys)

1. json - a text in json format, from which the content is extracted as a range - Value type: text

Remarks

• In the result range, only the top 1-2 levels of the json string are converted to cell content

• Lower level content (level 3-n) is preserved as sub-json strings in the cells of the result range. This sub-json can be then extracted by subsequent calls of the JSONTORANGE function

Examples

Example 1: The json text contains a single json object. Only the values are returned in the result range.

Example 2 - The json contains multiple objects. They are returned as rows of the result range.

Example 3 - The json contains an array with values. Each element of the array is added as a new row in the result range.

Example 4 - The json contains an array nested in an array. In this case, the elements of the inner array are returned in one row in the result range.

Example 5 - The json contains multiple arrays nested in an array. The elements of each inner array are returned as separate rows in the result range.

Example 6 - The json contains two arrays and an object nested in an array. The elements of both arrays and the object are returned as separate rows in the result range.

Example 7 - If the json contains a structure with more than 2 levels, it is possible to extract the content of lower-level values by subsequent calls of the JSONTORANGE function.