The ValueAtxxx functions are used with the WrapConnector interface to parse the information returned in JSON format for an HTTP request.
The WrapConnector real-time interface is used to make API calls from wraps to external systems. When a POST/PUT or GET HTTP request has been completed, the result is returned as a JSON structure in a text string that can be parsed using the ValueAtxxx functions described on this help page.
The ValueAtxxx functions parse the JSON returned for an HTTP request. Using the provided key, they locate a key/value pair in the JSON and operate on the value. If the value is an array, the functions operate on the designated element of the array.
There are five ValueAtxxx functions:
The syntax for the above functions is described in detail in the Function Reference below.
The basic version of ValueAt() parses the JSON returned by the HTTP request according to its parameters and returns the designated value as a variant.
=VALUEAT(json_as_string, key_and_index)
where json_as_string is the JSON structure or a cell reference for it, and key_and_index contains the nested keys for the value, and optionally an element index (1,2,..) if the value is an array.
The ValueAt() function returns a variant, either a string, number, or boolean depending on the value.
Examples
=ValueAt(“{‘httpstatus’:200,’msg’:’random text”, ‘payload’:{“‘wrench’:’big wrench’}}”,”httpstatus”) => 200
We have included the JSON in the function call above as a text string to make the example easier to read. The key can refer to any key/value pair in the JSON. In this case, the key requests the value of httpstatus.
=ValueAt(“{‘httpstatus’:200,’msg’:’random text”, ‘payload’:{“‘wrench’:’big wrench’}}”,”payload”,”wrench”) => “big wrench”
The key can also refer to key/value pairs in the payload, which means you have to provide two keys. In the example above, we are requesting the value for wrench which is a part of payload.
=ValueAt(“{‘httpstatus’:200,’msg’:’random text”, ‘payload’:{“‘wrench’:[1,2]}}”,”payload”,”wrench”,2) => 2
An element index must be provided if the value is an array, and returns the corresponding array element. The first element of an array has the index 1. The example above requests the second element of the array wrench in the payload.
=ValueAt(“{‘httpstatus’:200,’msg’:’random text”, ‘payload’:{“‘rotation_lock’:TRUE}}”,”payload”,”rotation_lock”) => TRUE
Since ValueAt() returns a variant, it can also return a Boolean.
The ValueAt() function returns a variant, which may require additional validation of the result. We recommend that you instead use the typed versions of ValueAt that raise a “#N/A!” error if the result does not have the expected type. This automates the validation and makes it easy to catch unexpected results. The typed functions have the same syntax as the ValueAt() function.
The ValueAtAsString() function returns a string. If the key references a value that is not a string, a “#N/A!” error is raised.
=ValueAtAsString(“{‘httpstatus’:200,’msg’:’random text”, ‘payload’:{“‘wrench’:’big wrench’}}”,”payload”,”wrench”) => “big wrench”
The ValueAtAsNumber() function returns a floating point number. If the key references a value that is not numeric, a “#N/A!” error is raised.
=ValueAtAsNumber(“{‘httpstatus’:200,’msg’:’random text”, ‘payload’:{“‘wrench’:[1,2]}}”,”payload”,”wrench”,2) => 2
The ValueAtAsBoolean() function returns True or False. If the key references a value that is not Boolean, a “#N/A!” error is raised.
=ValuteAtAsBoolean(“{‘httpstatus’:200,’msg’:’random text”, ‘payload’:{“‘rotation_lock’:TRUE}}”,”payload”,”rotation_lock”) => TRUE
You can use the ValueAtCheck() function to determine the datatype of a value in the JSON. ValueAtCheck is extra useful if the JSON does not return a static form, e.g. it includes an array with an unknown number of elements.
=ValueAtCheck(json_as_string, key_and_index) => -2,-1, 0, 1, 2, 3, 99
where key_and_index contains the nested keys for the value, and optionally an element index (1,2,..) if the value is an array.
The ValueAtCheck function returns the value type as an integer:
Additional return codes are documented in the Function Reference.
=ValueAtCheck(“{‘httpstatus’:200,’msg’:’random text”, ‘payload’:{“‘wrench’:’big wrench’}}”,”payload”,”wrench”) => 1
=ValueAtCheck(“{‘httpstatus’:200,’msg’:’random text”, ‘payload’:{“‘wrench’:[1,2]}}”,”payload”,”wrench”,2) => 2
=ValuteAtCheck(“{‘httpstatus’:200,’msg’:’random text”, ‘payload’:{“‘rotation_lock’:TRUE}}”,”payload”,”rotation_lock”) => 3
=ValueAt(“{‘httpstatus’:200,’msg’:’random text”, ‘payload’:{“‘wrench’:[1,2]}}”,”payload”,”wrench”,2) => 2
In the example above, a ValueAt function is used to parse the JSON data returned by an HTTP request.
The first parameter designates the JSON in a string variable. We have included the JSON in the function call as a text string to make the example easier to read. The second parameter designates the key and optional array index to return.
=ValueAtxxx(json_as_string, key_and_index)
Provide the JSON returned by the HTTP request or reference a cell where the JSON is stored. In both cases the JSON needs to be a string.
Provide the nested keys for the value, and optionally an element index (1,2,..) if the value is an array. The first element of an array has the index 1.
The ValueAt() function returns a variant.
The ValueAtAsString(), ValueAtAsNumber() and ValueAtAsBoolean() functions return a typed value according to their name. If the designated value is not of the expected type, a “#N/A!” error is raised.
The ValueAtCheck() function returns a numeric return code: