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
    • url
      A reference to the source data to be queried. Can be a reference that is HTTP, HTTPS or FILE based. Required.
    • queryList
      One 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.
    • additionalConfig
      An 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).
    • noDataValue
      When 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