ajJSONToTable function

Description

The ajJSONToTable function extracts node values from a JSON string and converts them into an array of data. When the selected nodes exist at different levels, it flattens the structure and this will be repeated for node values at a higher level.

Syntax

ajJSONToTable(table_column, json_data, [traversal_level], [Convert_to_text])

Argument Name	Argument Type	Description
table_column (required)	Range	The range of cells (row only) in which the value in each cell represents a node name in the JSON string. The wording must be the same and it is case-sensitive. These node names can be at different levels in the JSON string.
json_data (required)	Range / Array	The range of cells that contains the JSON string. The range of cells can span across multiple rows and columns. The cell values will be concatenated into one string from left to right and top to bottom.
traversal_level (optional)	Double	Level of the path of the node should be searched from the JSON string. The top-level is 1. All the levels in the JSON string will be searched by default.
convert_to_text (optional)	Boolean	If it equals TRUE, the return result will be converted to string values. If it equals FALSE, the return result will preserve the original data type. The default value is FALSE.

The function will return:

1) Return Value: Populated JSON string in a range of cells

2) Return Type: Multiple values (array formula)

Examples

Example 1

A common scenario is to define the required columns as shown in B36 to E36. Then, use an array formula of ajJSONToTable from B37 to E46 to populate the data into the range.

Since the ‘Traversal_level’ is not defined, the function searches for the node name at all levels.

Example 2

Using the same JSON string, this example set Traversal_level to 1 which limits the node search to the first level only.

Click here to download the use case workbooks for further reference.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
Invalid traversal level, it must be 0 or above.
Invalid JSON Format.
JSON data contains an Excel error.