ajMakeJson function
Description
The ajMakeJson 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.
the following formula:
=ajMakeJson(“name”, “value”)
will formulate the JSON string and return the following:
{"name":"value"}
Syntax
ajMakeJson(header, data, [data_type], [exclude_column])
Argument Name | Description |
---|---|
header (required) | 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. ajMakeJson 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 ajMakeJson 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 ajMakeJson 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 ajGetAddress to return an address range string. Refer to ajGetAddress for more details. |
data (required) | 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) | Specify 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 ajMakeJson 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. If you do not specify anything, the default data type will follow the cell data type. |
exclude_column (Optional) | Specify 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 ajMakeJson 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. If you do not specify anything, no fields will be excluded. |
The function will return:
1) Content type: JSON object / JSON array
2) Method: Within a cell / row cell array
Examples
Here are a few examples of ajMakeJson function.
Example 1
As you can see from the example below, the name and value of the JSON name-value pairs are spanned across A10:D10 and A11:D13 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 ajMakeJson. The formula is in B41 is '=ajGetAddress(D22:E22)&"*"&ajGetAddress(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 ajMakeJson would only return the first row of the value.
The JSON returned:
{
"ID": 1,
"Phone": {
"Number": 22233621,
"Type": "Home"
}
}