Excel Add-In for REST

Build 24.0.9060

jsonproviderGet

The jsonproviderGet operation is an APIScript operation that is used to process local JSON files or remote JSON data stores. It allows you to split JSON content into rows.

Required Parameters

  • URI: The URI parameter specifies the location of the JSON content. This URI scheme can be file:// for local files or can specify a remote data source: http:// (or https://), s3://, gdrive://, box://, or ftp:// (ftps://). The URI connection property can also provide this input.

  • JSONPath: The JSONPath parameter is used to split the document into multiple rows. The add-in will detect the paths to the rows when the DataModel property is set to FlattenedDocuments or Relational. See Parsing Hierarchical Data for a guide to configuring how the data is modeled.

    The XPath connection property can also provide the row XPaths. Specify the XPaths as absolute paths; define multiple paths in a semicolon-separated list.

    A wildcard JSONPath can also be used and is helpful in the case that the JSONPaths are all at the same height but contain different names: 

    <api:set  attr="JSONPath" value="/feed/*" />

HTTP

The jsonproviderGet operation can be used to make HTTP requests to JSON data sources and process the results. It abstracts the complexity of connecting to JSON-based REST APIs but also gives you control over the layers involved, providing the following inputs to configure authentication, the HTTP request and headers, and firewall traversal.

Column Mapping

The jsonproviderGet operation reads the Column Definitions from the api:info section of the table schema file. In the column definition, the other:xPath property maps the column value to a JSON element.

Input Parameters

You can use api:set to specify the operation's input parameters, listed below. The connection properties also pass inputs to the operation; setting an attribute in the schema will override the connection property. 

<api:set  attr="uri"                      value="NorthwindOData.json" />

Processing

  • URI: The URI parameter specifies the location of the JSON content. This URI scheme can be file:// for local files or can specify a remote data source: http:// (or https://), s3://, gdrive://, box://, or ftp:// (ftps://).

  • JSONPath: The JSONPath parameter specifies the XPath to an array element that is used to split the document into rows. Specify multiple paths in a semicolon-separated list; see DataModel to configure the tables discovered based on these paths. By default the add-in will detect the object arrays in the document as the rows -- see Automatic Schema Discovery to configure the row scan.

    A wildcard JSONPath can also be used and is helpful in the case that the JSONPaths are all at the same height but contain different names: 

    <api:set  attr="JSONPath" value="/feed/*" />

HTTP

  • Method: The HTTP method that corresponds to the SQL data manipulation statement. The allowed values are GET, POST, PUT, DELETE, and MERGE. The default value is GET.
  • ContentType: The content type of the HTTP post. Relevant only if data is specified.
  • Data: Data to include in the put or the post.
  • Text: Input JSON text (an alternative to URI).
  • Header:Name#: The name for each custom header to pass with the request.
  • Header:Value#: The value for each custom header to pass with the request.
  • ParamName#: The name for each parameter to pass with the request.
  • ParamValue#: The value for each parameter to pass with the request.
  • Cookie:*: Any cookies that should be added to the request.
  • Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
  • LogFile: The file where exchanged/transferred data is logged.
  • PushResponseHeader#: The response headers which should be returned from this operation. Any headers added to this list are returned as header:* parameters.
  • PushResponseCookie#: The response cookies which should be returned from this operation. Any cookies added to this list are returned as cookie:* parameters.

Authentication

  • User: The username used for authentication.
  • Password: The password used for authentication.
  • AuthScheme: The authentication method to use. Only relevant if User and Password are provided. The allowed values are BASIC, DIGEST, NONE, NTLM, NEGOTIATE. The default value is BASIC.
  • KerberosKDC: The KDC setting of Kerberos, available when AuthScheme is NEGOTIATE.
  • KerberosRealm: The Realm setting of Kerberos, available when AuthScheme is NEGOTIATE.
  • KerberosToken: The Kerberos token used for authentication.

OAuth

  • Version: The OAuth version. The allowed values are DISABLED, 1.0, 2.0. The default value is DISABLED.
  • Token: The access token for OAuth.
  • Token_Secret: The access token secret. OAuth 1.0 only.
  • Client_Id: The OAuth client Id. OAuth 1.0 only.
  • Client_Secret: The OAuth client secret. OAuth 1.0 only.
  • Sign_Method: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
  • Other_Options: Other options to control the behavior of OAuth.

Proxy

  • Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. The allowed values are TRUE, FALSE. The default value is FALSE.
  • Proxy_Server: IP address or host name of the proxy server used for the request.
  • Proxy_Port: The port number of the proxy server.
  • Proxy_User: The user Id used to authenticate with the proxy server.
  • Proxy_Password: The password used to authenticate with the proxy server.
  • Proxy_AuthScheme: The authentication scheme of the proxy server. The allowed values are BASIC, DIGEST, PROPRIETARY, NONE, NTLM. The default value is BASIC.
  • Proxy_AuthToken: The proxy authentication token.
  • Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.

Firewall

  • Firewall_Server: The IP address or host name of the firewall.
  • Firewall_Port: The port number of the firewall.
  • Firewall_User: The user Id used to authenticate with the firewall.
  • Firewall_Password: The password used to authenticate with the firewall.
  • Firewall_Type: The type of the firewall. The allowed values are NONE, TUNNEL, SOCKS4, and SOCKS5. The default value is NONE.

Advanced Processing

  • ElementMapPath#: The XPath to the subelement that you want to specify as columns within your chosen row. This can be relative to the item (for example, title) or absolute (for example, /json/@version).
  • ElementMapName#: The name of the chosen ElementMapPath. The ElementMapName input can be used to give a name to the XPath chosen in ElementMapPath.
  • ElementArrayMapPath#: The XPath to the element that you want to specify as an array.
  • ElementArrayMapName#: The name of the chosen ElementArrayMapPath. The ElementArrayMapName input can be used to give a name to the XPath chosen in ElementArrayMapPath.
  • AggregateFormat: The format to use with aggregate values. The allowed values are JSON, XML. The default value is JSON.
  • AggregatePath#: The XPath to the element that you want to return as an aggregate.
  • AggregateName#: The name of the chosen aggregate. The AggregateName input can be used to give a name to the XPath chosen in AggregatePath.
  • ResultCodeXPath: The XPath of the result code element in the response.
  • ResultSuccessCode: The success code of the value located at ResultCodeXPath that is used to verify and identify the request as being successful.
  • ErrorMsgXPath: The XPath of the error message returned in the response.
  • IndexParentNodes: Multiple occurrences of the same element will be indexed if this is set to true. If set to false, multiple occurrences will be combined into an array. The allowed values are TRUE, FALSE. The default value is TRUE.
  • TrimStartingParentIndexes: Specifies whether to trim starting parent indexes. Only valid when IndexParentNodes is TRUE. Specifically useful when the JSONPath specifies multidimensional arrays. The allowed values are TRUE, FALSE. The default value is FALSE.
  • PushEmptyArrayObjects: Specifies whether to push empty objects within multidimensional arrays. The allowed values are TRUE, FALSE. The default value is FALSE.
  • PushPrefix: The prefix to be prepended to each item attribute that is pushed.
  • EnablePaging: Specifies whether the Rows@Next input will be used for paging. The allowed values are TRUE, FALSE. The default value is FALSE.
  • OutputPrefix: Whether the prefix should be output with each pushed attribute of an item. The allowed values are TRUE, FALSE. The default value is FALSE.
  • PushOuterValueRow: Whether to generate special rows for paths outside of the repeat element when no other rows would contain them. If one of these rows is generated it will contain the values of all mapped paths outside the repeat element, along with a special attribute called @isoutervalue which is set to true. However, if there is a row in the repeat element which contains these values then the @isoutervalue is not pushed.

    The allowed values are TRUE, FALSE. The default value is FALSE.

Output Parameters

  • *: Objects found.
  • header:*: Any headers requested from the PushResponseHeader# input (e.g. adding "Content-Type" to PushResponseHeader# will return "header:Content-Type")
  • cookie:*: Any cookies requested from the PushResponseCookie# input (e.g. adding "session" to PushResponseCookie# will return "cookie:session")
Note If you are using api:push to emit rows from within different invocations of this operation (either separate calls in the schema, or in a loop), it is recommended that you use api:map to copy all the elements from the output item produced by api:call into a temporary item that is then passed to api:push: 
<api:call op="jsonproviderGet" out="output">
  <api:map from="output" to="temp" map="* = *" />
  <api:push item="temp" />
</api:call >
The items produced by this operation are required to have the same layout for performance reasons, which may not be the case if different documents are used with calls to this operation. Performing this map converts the item into a more flexible form which does not have the same layout restriction.

The flexible layout has different restrictions to be aware of:

  • It is slightly slower than the optimized layout.
  • It does not support column names containing a period, so you will want to set the hidden PathDelimiter property to an underscore and access flattened columns using names like a_b_c instead of a.b.c.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060