How to: Map inputs and outputs

In order for Excel services converted in Spark, the files need to have "X mappings" applied to the inputs and outputs needed from the workbook. These are usually defined by applying specific Named Ranges in an Excel file that Spark can identify. These identified inputs and outputs will then correspond to the parameters of the API request and API response.

Before reviewing this section, it is recommended to review Get started in 5 minutes.

Do not use the period/dot character (.)in your input or output names; this character is specifically reserved for defining How to: Work with subservices.

Define a named range in Excel

Read Define Named Ranges about the different ways Names can be defined in Excel.

Map a single/multiple cell range to an input/output

Syntax: Xinput_<parameter> and Xoutput_<parameter>

This can be used to define a standard input or output to a Spark service from a range in Excel. In Excel, a range can be a single cell or multiple cells.

1. Follow the instructions to create a Name in Define a named range in Excel for the selected range.

2. When creating the Name, use the noted prefix defined in the syntax.

For ranged values of Xinput and Xoutput, each column should have a unique heading name.

Additional Xinput and Xoutput features

Apply validation rules to input values

Learn more about validations in How to: Set data validations.

Set default values for a calculation

• Values that are saved in Xinput fields are considered the default values. This means if a Spark calculation is made and the particular input is not provided, the value in the Excel file will be used in the calculation. A warning message will appear if default values are used in an API call.

• Formulas that reside in an Xinput will be evaluated once upon upload and considered to be the default value.

Add comment to input and output values

• When adding or editing a Name in Excel, there is the opportunity to include a comment. This comment will be displayed in the Service Documentation and API Tester Documentation.

Support for spill functions but within a predefined area

• Neuron supports dynamic array formulas and spilled arrays, however inputs and outputs that include the spill range operator # are not supported. For example, do not set an Xoutput to D3#, instead set to D3:G10 with a sufficiently large range to cover the size of the output data.

Avoid setting inputs and outputs to table structured references

• Setting inputs and outputs against structured references such as Sales[[#All],[A]], DeptSales[Sales Amount], may lead to unexpected behaviors and impact the ability for the Download the Excel file with inputs to populate the inputs correctly. Instead use an absolute range reference such as ='My Data Worksheet'!$A$1:$F$25.

Prevent integration issues

• To reduce potential API integration issues, try to have inputs and outputs maintain the same data types across the domain of inputs and outputs. In other words, a cell value should only always be a number or always a date, or always text. Mixing numbers, dates, and text in one string will lead to inconsistencies in the inputs and outputs, for example 0,1,2,3+ contains both numbers and text. See more in How to: Prepare an Excel file for Spark and Data types for Spark.

• If Xinput and Xoutput has been defined for the same range, Spark will not process the results correctly.

• If you are using a range to contain key value pairs, e.g. Xinput_A contains A and 1, Band 2, etc., we strongly recommend modifying your Excel file to use lookups to retrieve these values in case the integration provides key-value pairs in different order. See the attachment for an example.

12KB

Caution using table inputs for key value pairs.xlsx

Open

Table behaviors

• If an Xinput has been mapped to a range (e.g. 5 rows x 6 columns cells) and the API request data inputs contains an incomplete range (e.g. 2 rows x 4 columns cells), the following behaviors will apply:

  • Spark will process the input based upon the numbers of rows provided in the API request.

  • For the rows that will be processed:

    • Values of "" or null will clear the value in a cell.

    • Any missing values (unreferenced) will be replaced by the default value. This includes if an entire column is missing.

  • In the above example, the table input will be processed as 2 rows x 4 columns of inputs and 2 rows and 2 columns of default values.

  • Note that for Neuron 1.14.0 and earlier, when tables were provided in the default "records" format (reference Submit tabular inputs as a JSON array), e.g. [{"A":1,"B":2}, {"A":3,"B":4}], default values are not used for incomplete table ranges. In the above example, the table input would only be treated as 2 rows x 4 columns of inputs.

• For an Xoutput range, any empty rows from the bottom will be trimmed from the response. A row is considered empty if the cells are cleared or if they contain ="".

• Columns without a heading will be given one based on their column letter in Excel.

• Merged headings can be used to indicate a hierarchy above subheadings. In this screenshot, Age, Location, Job, and Industry would be represented as Personal.Age, Personal.Location, Professional.Job and Professional.Industry respectively.

  

Return outputs as complex JSON response structures

We recommend using Neuron 1.20.0 2024-06-17 or newer which contains the latest Xrichoutput fixes.

Syntax: Xrichoutput_<parameter>

Xrichoutput allows users to define more complex JSON structures without a complex schema. The heading row can contain instructions that indicate whether keys will be nested in an object or list. For more complex JSON transformations, we recommend to use the Transforms API JSONtransforms.

Example of mapping Xrichoutput

This service provides a number of examples that encompass the functionality and notation for Xrichoutput. We suggest that if you would like to use this feature:

1. Examine the different examples present in this file.

2. Identify the example that looks the most similar to your target JSON structure.

3. Amend an existing Xoutput to use the example notation.

4. Upload to Spark and test the results.

22KB

Xrichoutput Updated.xlsx

Open

For long time users of Xrichoutput, the previous examples document can be found below. Features such as ? or = are no longer supported in newer versions of Neuron.

155KB

Xrichoutput.xlsx

Open

CSV Inputs / Outputs

Use CSV for large input ranges

Syntax: XCSVInput_<parameter>

The use of JSON data tables may not be very efficient for large input ranges. The XCSVInput mapping allows the ingestion of comma-separated values as part of the API request. Because the Spark API requests are made in JSON format, the XCSVInput must be in escaped format with escape characters.

1. Follow the instructions to create a Name in Define a named range in Excel for the selected range.

2. When creating the Name, use the noted prefix defined in the syntax.

Return large output ranges as CSV

Syntax: XCSVOutput_<parameter>

If the user needs to produce an API response similar to a CSV 2-dimensions arrays, this is possible using the XCSVOutput mapping. This method is more efficient, especially if very large tables are used. Every row on the output range is converted to a comma-separated row on Spark. Because the response is in JSON format, the output from the API will be returned in escaped format with escape characters.

1. Follow the instructions to create a Name in Define a named range in Excel for the selected range.

2. When creating the Name, use the noted prefix defined in the syntax.

16KB

XCSVOutput.xlsx

Open