# 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](https://support.microsoft.com/en-us/office/define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64) 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](/getting-started-in-5-minutes.md). {% hint style="info" %} 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](/build-spark-services/subservices.md). {% endhint %} ## Define a named range in Excel Read [Excel tips](/build-spark-services/how-to-prepare-an-excel-file-for-spark/excel-tips.md#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_` and `Xoutput_` 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](#define-a-named-range-in-excel "mention") for the selected range. 2. When creating the Name, use the noted prefix defined in the syntax. {% hint style="warning" %} For ranged values of `Xinput` and `Xoutput`, each column should have a unique heading name. {% endhint %} ### Additional `Xinput` and `Xoutput` features #### Apply validation rules to input values Learn more about validations in [How to: Set data validations](/build-spark-services/set-data-validations.md). #### 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](/navigation/service-documentation.md) and [API Tester](/navigation/api-tester.md) [API Tester](/navigation/api-tester.md#documentation). #### Support for spill functions but within a predefined area * [Neuron](/build-spark-services/neuron.md) supports [dynamic array formulas and spilled arrays](https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531), however inputs and outputs that include the [spill range operator](https://support.microsoft.com/en-us/office/spilled-range-operator-3dd5899f-bca2-4b9d-a172-3eae9ac22efd) `#` 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](https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e) such as `Sales[[#All],[A]]`, `DeptSales[Sales Amount]`, may lead to unexpected behaviors and impact the ability for the [API Call History](/navigation/api-call-history.md#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](/build-spark-services/how-to-prepare-an-excel-file-for-spark.md) and [Data types for Spark](/build-spark-services/data-types-for-spark.md). * 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`, `B`and `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. {% file src="/files/s5XwpUKNoAQhixMRbH78" %} #### 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 [Execute API (v3)](/spark-apis/execute-api/execute-api-v3.md#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 {% hint style="warning" %} We recommend using [Neuron](/build-spark-services/neuron.md) [/pages/6KSg4ILon6IcoRfdy6jv#id-1.20.0-2024-06-17](https://docs.coherent.global/build-spark-services/pages/6KSg4ILon6IcoRfdy6jv#id-1.20.0-2024-06-17 "mention") or newer which contains the latest `Xrichoutput` fixes. {% endhint %} Syntax: `Xrichoutput_` `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](/spark-apis/transforms-api.md) [JSONtransforms](/spark-apis/transforms-api/transform-types/jsontransforms.md). ### 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. {% file src="/files/nXb7c1pEko4CnE0DId7l" %} 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](/build-spark-services/neuron.md). {% file src="/files/5MFljrGMmyf62fb8ZZjn" %} ## CSV Inputs / Outputs ### Use CSV for large input ranges Syntax: `XCSVInput_` 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](#define-a-named-range-in-excel "mention") 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_` 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](#define-a-named-range-in-excel "mention") for the selected range. 2. When creating the Name, use the noted prefix defined in the syntax. {% file src="/files/-Me0W2FGdk1yEo2sMCCH" %} --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.coherent.global/build-spark-services/map-inputs-and-outputs.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.