ajJSONFromTable function
AlchemyJ Excel Library, AlchemyJ Community Edition, AlchemyJ Enterprise Edition
Description
The ajJSONFromTable function returns a JSON string of a single table.
In some cases, we want to create a JSON string that contains a name with a value which is a JSON array as shown below.
{
"object":[
{"array1":"value1"},
{"array2":"value2"},
{"array3":"value3"}
]
}
Syntax
ajJSONFromTable(header, data, [data_name], [data_type], [exclude_column])
Argument Name | Argument Type | Description |
---|---|---|
header (required) | Range / Array | The range of cells to be set as the name of the JSON name-value pair. It can be selected either vertically or horizontally. |
data (required) | Range / Array | The range of cells to be set as the value of the JSON name-value pair. It can be selected either vertically or horizontally but the orientation must be the same as ‘Header’. |
data_name (optional) | String | The name of the JSON name-value pair. The default value is "data". |
data_type (optional) | String | The data type of the fields (‘Header’) in the format below. For the list of data types, please refer to the data type supported in AlchemyJ. The fields that do not exist will be ignored. <field_name>-##-<data_type>-||-<field_name>-##-<data_type>-||-... Where, -||- denotes AlchemyJ parameter separator. -##- denotes AlchemyJ parameter type separator. For example, ID-##-number-||-Name-##-string Please take note that both separator symbols can be configured in %%AppConfig worksheet. The default data type will follow the cell data type. |
exclude_column (optional) | String | The fields to be excluded from the result. The fields that do not exist will be ignored. <field_name>-||-<field_name>-||-... Where, -||- denotes AlchemyJ parameter separator. For example, ID-||-Last Name Please take note that the parameter separator symbols can be configured in %%AppConfig worksheet. No fields will be excluded by default. |
The function will return:
1) Return Value: JSON string
2) Return Type: Single Value / Multiple values (array formula)
Example
As you can see from the example below, the JSON array is defined with a list of names from cell A1:C1 and the array value is defined in A2:C4, where A2:C2, A3:C3 and A4:C4 are the 3 sets of the JSON string in the array.
If the JSON string is too large, the return value can be specified in a row cell array.
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 |
---|
Failed to convert data to specified data type. |
Invalid data type, it could be number, integer, boolean or string. |
Header or data range does not exist. |