ajJSON function

Description

The ajJSON function returns a JSON string with a corresponding name and value pair. If the value of the JSON name-value pair contains multiple records, a JSON array will be returned. Data with either an empty header or an empty value will be ignored.

the following formula:

=ajJSON(“name”, “value”)

will formulate the JSON string and return the following:

{"name":"value"}

Syntax

ajJSON(header, data, [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. You can also merge multiple columns into one by giving consecutive columns with the same name and index suffix as shown below <field_name>__<running_number_from_01>: For example, you have a field called Description, and it should be 60000 characters long which is longer than the capacity limit of a single cell in Excel. Therefore, two columns, Description__01 and Description__02 are used. ajJSON will concatenate the values and the node name in the return JSON which would be Description. If a header name is surrounded by [], the content will be treated as the input parameter of a nested ajJSON call which returns an array of JSON objects. Use asterisk "*" to join header and data addresses. For example, A1:B1*A2:B4. This means A1 to B1 is the header range of the nested list, and A2 to B4 are the three rows of data. If a header name is surrounded by {}, the content will be treated as the input parameter of a nested ajJSON call which returns a JSON object. {} and [] headers can create JSON strings with multiple level nodes. As the content expects a string that contains cell a range address, it is recommended to use ajAddress to return an address range string. Refer to ajAddress for more details.
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 must be the same as ‘Header’. For example, if ‘Header’ is selected vertically, ‘Data’ must be selected vertically as well. If a single record is selected, the result will be a JSON object. If multiple records are selected, the result will be a JSON array.
data_type (Optional)	String	The data type of the fields (‘Header’) in the format below. The following data type supported in AlchemyJ are number, integer, boolean and string. Fields that do not exist will be ignored. The fields specified here will also be applied to nested ajJSON call using [] and {} headers. <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. Fields that do not exist will be ignored. The fields specified here will also be applied to nested ajJSON call using [] and {} headers. <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)

Examples

Here are a few examples of ajJSON function.

Example 1

As you can see from the example below, the name and value of the JSON name-value pairs are spanned across A11:D11 and A12:D14 respectively.

Please take note that the range of cells in both arguments is spanned horizontally and a JSON string is returned.

If the JSON string is too large, the return value can be specified in a row cell array.

Example 2

This example is similar to Example 1. The only difference is that the range of cells is declared vertically instead of horizontally.

Example 3

From the example below you can see, the header Phone is surrounded by [], then the value will be treated as the parameters of a nested call of ajJSON. The formula is in B41 is '=ajAddress(D22:E22)&"*"&ajAddress(D23:E24)'

The JSON returned:

{
    "ID": 1,
    "Phone": [
        {
            "Number": 22233621,
            "Type": "Home"
        },
        {
            "Number": 22216247,
            "Type": "Office"
        }
    ]
}

Example 4

This example is similar to example 3, the only difference is the Phone is surrounded by {}. Please take note that the nested call of ajJSON would only return the first row of the value.

The JSON returned:

{
    "ID": 1,
    "Phone": {
        "Number": 22233621,
        "Type": "Home"
    }
}

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
Defined header or data range does not exist.
Invalid data type, it could be number, integer, boolean or string.
Failed to convert string to specified data type or format.