ajPopulateJsonToTable function
Description
The ajPopulateJsonToTable 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.
The ajPopulateJsonToTable function converts a JSON string to an array of data. This function can also extract the values of a multiple level JSON string.
Syntax
ajPopulateJsonToTable(table_column, json_data, [traversal_level], [Convert_to_text])
| Argument Name | Description |
|---|---|
| table_column (required) | 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) | 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) | Specify how deep (level of the path) a node should be searched from the JSON string. The top-level is 1. If you do not specify anything, all the levels in the JSON string will be searched. |
| convert_to_text (optional) | 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 but any string value that has more than 256 characters will cause an error. If you do not specify anything, the default value will always be FALSE. |
The function will return:
1) Content type: Populated JSON string in a range of cells
2) Method: Cell array
Examples
Example 1
A common scenario is to define the required columns as shown in B36 to E36. Then, use an array formula of ajPopulateJsonToTable 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.
