Function: WebGetMultiple
Extract multiple values (numbers, text, etc.) from a source reference, using a list of query conditions that identify the values of interest.
Usage
-
Excel
=WebGetMultiple(string url
, string queryList
, string additionalConfig = ""
, object noDataValue = null)
Parameters
-
Excel
-
urlA reference to the source data to be queried. Can be a reference that is HTTP, HTTPS or FILE based. Required.
-
queryListOne or more query specifications used to isolate specific data elements from the raw source data. Each specification should be enclosed in { } (braces) and be comma separated (see examples). Details about the query syntax are covered here. Required.
-
additionalConfigAn optional list of configuration parameters that control how the query is issued. Details about the configuration parameters syntax are covered here. Optional - provide an empty string or omit the parameter if there are no configuration parameters needed (i.e. default behavior).
-
noDataValueWhen no data value is retrieved, this value is returned as a "default" for all fields in the range. This value also plays a role depending on your "ErrorHandling" configuration setting. Optional - no value is treated as an empty string.
-
Return Value(s)
-
Excel
- Returns values of an appropriate type. For example, a value that looks like a number will be returned as a numeric (i.e. can be formatted as a number). Populates a range of cells (multiple cells within one row or one column).
- Values that can't be parsed to a native type are left as strings.
- No data matching the query expression returns the value specified in the noDataValue parameter (or an empty string if no value is provided) for all cells of the range.
- Error values can be returned as well, depending on your "ErrorHandling" configuration setting.
Remarks
This function can be used to extract multiple scalar values within a single source (page, file, etc.). This can be useful from an efficiency perspective (one request returning multiple values), and it has the behavior of populating a one-dimensional cell range. (Strictly speaking, similar functionality is available using WebGetSingle.) Some pre-parsing of the return value can be done depending on the query expression used.
Examples
-
Excel
1. Pull multiple data elements from a JSON file stored on a network share
=WebGetMultiple("file://\\servername\sharename\sample.json", "{xpath=/widget[1]/window[1]/title[1]/text()}, {xpath=/widget[1]/window[1]/width[1]/text()}, {xpath=/widget[1]/window[1]/height[1]/text()}")
With the above function, we would expect to extract exactly three elements from the following sample JSON file:
{"widget": {
"debug": "on",
"window": {
"title": "Sample Konfabulator Widget",
"name": "main_window",
"width": 500,
"height": 500
},
"image": {
"src": "Images/Sun.png",
"name": "sun1",
"hOffset": 250,
"vOffset": 250,
"alignment": "center"
},
"text": {
"data": "Click Here",
"size": 36,
"style": "bold",
"name": "text1",
"hOffset": 250,
"vOffset": 100,
"alignment": "center",
"onMouseUp": "sun1.opacity = (sun1.opacity / 100) * 90;"
}
}}We use "xpath" expressions since JSON is treated internally as if it were XML (offering a uniform way to access the data using xpath). Our three query terms turn into a cell range of either 1 row and 3 columns, or 3 rows and 1 column. To get the range to populate, we would select the cell range, enter the function text in the formula area, and hit Ctrl-Shift-Enter. After that, we would see something like this for example:
2. Error handling scenarios
Using example #1, say we change the query text to be invalid xpath for the second expression:
=WebGetMultiple("file://\\servername\sharename\sample.json", "{xpath=/widget[1]/window[1]/title[1]/text()}, {xpath=/widget[]/window[1]/width[1]/text()}, {xpath=/widget[1]/window[1]/height[1]/text()}")
The default behavior here is to show "#VALUE!" for all cells in the range, given the entire function has thrown an exception. If we add a configuration option for error handling such as:
=WebGetMultiple("file://\\servername\sharename\sample.json", "{xpath=/widget[1]/window[1]/title[1]/text()}, {xpath=/widget[]/window[1]/width[1]/text()}, {xpath=/widget[1]/window[1]/height[1]/text()}", "ErrorHandling=ReturnValue")
With the above adjustment, we see "xSkrape: Expression must evaluate to a node-set." in each cell of the cell range. xSkrape errors are preceded by "xSkrape:" as one way to identify a failure. Other error handling options for Excel include "NoData" (use the "no data" value) and "Exception" (default).
3. "No Value" usage
=WebGetMultiple("file://\\servername\sharename\sample.json", "{xpath=/widget[100]/window[1]/title[1]/text()}, {xpath=/widget[100]/window[1]/width[1]/text()}, {xpath=/widget[1]/window[1]/height[1]/text()}", "", "N/A")
In this example (using the data from the first two examples), we're purposely trying to extract data that is not there: we have only one widget element, not 100 of them - so "no data" is returned by definition for the first two queries. Since we've provided "N/A" as the text to use for "no data", this is what would show up in the first two cells of the range, with "500" in the third cell.
Related
- WebGetMultiple is closely related to WebGetSingle.
- WebGetMultiple is mentioned in the Using WebGetSingle and/or WebGetMultiple against an RSS feed example for Excel
- Make a suggestion about this documentation (for Excel)