Utility Functions

Overview

APIX.XL™ includes a number of utility functions. The following steps allow the user to access and view these functions.

To view the list of APIX™ utility functions, do the following:

1. Launch the Insert Function window by clicking the fx symbol next to the formula bar.
2. Select the A_utilities category from the category drop-down.

The utility functions included with APIX™ are detailed below:

a_util_create_array
a_util_create_dictionary
a_util_create_file
a_util_create_table
a_util_expand
a_util_flatten
a_util_path
a_util_read_file
a_util_render_array
a_util_render_dictionary
a_util_render_table
a_util_render_table_hierarchical
a_util_to_csv
a_util_update_json

a_util_create_array

This function generates and returns a textual representation for a range of Nx1 or 1xN selected cells. The result is displayed in the cell where the function is defined. This function may be used inside another function. Data types in the selected cell are converted to textual representation according to the rules.

Parameter List:

Rng (required)
Input range from Excel.

OutputType (optional)
Format of output string (JSON). Default is JSON.

a_util_create_dictionary

This function generates and returns a textual representation for a range of Nx2 or 2xN selected cells. The result is displayed in the cell where the function is defined. This function may be used inside another function.

Parameter List:

Rng (required)
Input range from Excel.

OutputType (optional)
Format of output string (JSON). Default is JSON.

a_util_create_file

This function creates a file to the file system based on the range selected. This function can write the contents of a single cell or a range of cells. When writing a range of cells, the data can be written out as JSON or CSV.

Parameter List:

Rng (required)
Input range from Excel.

FolderPath (required)
Folder in which to write the file.

FileName (optional)
Name of the file. If FileName is blank, the full path of the file should be provided in the folder_path parameter.

FileType (optional)
Output format of file (JSON, CSV). Default is JSON.

a_util_create_table

This function generates and returns a textual representation for a range of NxM selected cells. The result is displayed in the cell where the function is defined. This function may be used inside another function.

Parameter List:

Rng (required)
Input range from Excel.

HeaderOrientation (optional)
Row or column. The default is a row.

TableType (optional)
Record or column. Default is record.

OutputType (optional)
Format of string (JSON). Default is JSON.

a_util_expand

This function is useful to visualize the structure of the supplied data. It renders the data into a cell range starting directly below the cell where this function is defined, with every piece of data in the input rendered in one cell each, positioned within the cell range according to its logical position within the data structure.

Parameter List:

Json (required)
Input JSON string.

JsonPath (optional)
Path to drill down to a node. Default is root.

a_util_flatten

This function is useful for learning the JsonPath expression associated with each piece of data of the supplied structure. It renders the supplied structured data into an Nx2 cell range, starting directly below the cell where this function is defined. Every piece of data in the data structure is rendered in one cell each in the second column. For each one, the JsonPath expression corresponding to that piece of data is placed in the corresponding cell of the first column.

Parameter List:

Json (required)
Input JSON string.

JsonPath (optional)
Path to drill down to a node. Default is root.

a_util_path

This function applies a JsonPath expression to a JSON structure, returning the result in the same cell. Data types in the textual representation are rendered appropriately according to the rules.

Parameter List:

Json (required)
Input JSON string.

JsonPath (optional)
Path to drill down to a node. Default is root.

a_util_read_file

This function reads data from a file in the file system and returns the value to the cell where the function is defined.

Parameter List:

Path (required)
Path to read file from.

JsonPath (optional)
Path to drill down to a node. Default is root.

InputType (optional)
Format of input string (JSON). Default is JSON.

MinifyFile (optional)
Should be used to remove new lines. Default is true.

a_util_render_array

This function renders the supplied textual representation of an array or list into a cell range starting directly below the cell where this function is defined, with each element of the array or list rendered in one cell each. By default, the elements are rendered in a Nx1 range (i.e., vertically) but the Orientation parameter can be used to change that to a 1xN range (i.e., horizontally).

Parameter List:

Json (required)
Input JSON string

JsonPath (optional)
Path to drill down to a node. Default is root.

Orientation (optional)
Column or row. Default is column.

InputType (optional)
Format of input string (JSON). Default is JSON.

ReturnArray (optional)
Returns an Excel function array instead of rendering the range below the cell.

a_util_render_dictionary

This function renders the supplied textual representation of an array or list intThis function renders the supplied textual representation of a dictionary into a cell range starting directly below the cell where this function is defined. The keys and the corresponding values are rendered vertically in one cell each, with the keys in the first column and the values in the second column. The Orientation parameter can be used to cause the keys and values to be rendered in rows instead.

Parameter List:

Json (required)
Input JSON string

JsonPath (optional)
Path to drill down to a node. Default is root.

Orientation (optional)
Column or row. Default is column.

InputType (optional)
Format of input string (JSON). Default is JSON.

ReturnArray (optional)
Returns an Excel function array instead of rendering the range below the cell.

a_util_render_table

This function renders the supplied textual representation of a table into a cell range starting directly below the cell where this function is defined. The headers are rendered in the first row, each in one cell, and the rest of the data is rendered in the subsequent rows. The HeaderOrientation parameter can be used to cause the headers and values to be rendered in column instead. It is also possible to change the order in which the headers are rendered and verify there are no missing values for those headers with the Columns parameter.

Parameter List:

Json (required)
Input JSON string

JsonPath (optional)
Path to drill down to a node. Default is root.

Columns (optional)
Determines order of columns and checks for presence.

HeaderOrientation (optional)
Row or column. Default is row.

InputType(optional)
Format of input string (JSON). Default is JSON.

Index (optional)
Index column is displayed only once for all groups. Used in conjunction with GroupBy parameter.

GroupBy (optional)
Group by column is displayed as extra header row. Used in conjunction with Index parameter.

ReturnArray (optional)
Returns an excel function array instead of rendering the range below the cell.

JsonPathForValue (optional)
Use this JsonPath in case value is a json object.

ColumnDelimiter (optional)
Use this when cell values are delimited (e.g., pipe, comma).

ColumnJsonPathOrDelimited (optional)
JSON to extract columns from input JSON or delimited string.

Flatten (optional)
Flag to determine if the parsed JSON should be flattened out for child objects.

AdditionalColumns (optional)
Comma separated list of JsonPaths to pick additional columns from JSON blob.

UniqueKey (optional)
Key in the input JSON to show only unique records in the table.

IgnoreBlanks (optional)
Flag to control whether to ignore blank rows in the return dataset.

RenderAtNamedRange (optional)
Name of a range to render the return dataset.

a_util_render_table_hierarchical

This function renders the supplied textual representation of a table into a cell range starting directly below the cell where this function is defined. Each field name is rendered as a header and the corresponding value of the field is rendered as the field value represented as text. The field value can be an object, an array, or text.

Parameter List:

Json (required)
Input JSON string

JsonPath (optional)
Path to drill down to a node. Default is root.

Columns (optional)
Provide mapping for columns in order to display custom column headers in rendered data.

ParentKey
JSON path for the starting point to recursively go through the hierarchical data.

JsonPathForValue
Path to drill down in case a value is also a JSON object.

a_util_to_csv

This function allows the conversion of an Excel range into comma separated string. This can then be reused within the sheet or exported into a file.

Parameter List:

Rng (required)
Input range from Excel.

a_util_update_json

This utility function allows updating a JSON from an Excel range. This can be used in conjunction with the flatten function. Once the flatten function renders a range in Excel, the user can update the value in Excel and generate the updated JSON in Excel. The updated JSON can be sent back to server or reused in Excel.

Parameter List:

Json (required)
Input JSON string.