ajJSONFromTable function

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.