> For the complete documentation index, see [llms.txt](https://docs.coherent.global/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.coherent.global/build-spark-services/additional-mappings/inputs-and-outputs-xinput-and-xoutput.md). # Inputs and outputs (Xinput and Xoutput) Syntax: `Xinput_` and `Xoutput_` This page supplements [Tutorial: Map inputs and outputs](/build-spark-services/tutorial-map-inputs-and-outputs.md). ## Use cell addresses to define table boundaries * Set inputs and outputs with [structured references](https://support.microsoft.com/en-US/Excel/using-structured-references-with-excel-tables) 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. * There is support for [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). Inputs and outputs must be set to a predefined size which contain the largest expected size of the spilled array. Setting an input or output with a [spill range operator](https://support.microsoft.com/en-US/Excel/spilled-range-operator) `#` such as `D3#` is not supported. ## Define default values * Values that are saved in `Xinput` fields are considered the default values. If these inputs are not provided in an API request, Spark will use values saved in the cell as defaults and raise a warning. * If the default value contains a formula, Spark uses the value of the formula as the default value. ## Define data validation Spark processes [data validation](https://support.microsoft.com/en-us/excel/get-started/apply-data-validation-to-cells) during execution and raises an error if an invalid input is provided. Customize the error message by entering a message in the **Data Validation Error message**, see [More on data validation](https://support.microsoft.com/en-us/excel/more-on-data-validation). If the [API Tester](/navigation/api-tester.md) shows invalid data validations and raises input errors, it could caused by a bug in Excel where the interactive data validation hides the underlying data validation that is read by Spark. 1. Open the workbook in Excel. 2. Save changes in case you want to revert the file. 3. Select the `Xinput` that has the faulty validation in Spark. 4. Select the **Data** tab and select **Data Validation**. 5. Select **Clear All**. 6. If the cell still shows a data validation dropdown, this indicates that there was an underlying data validation. 7. Confirm by selecting **Data Validation**. 8. See the details of the underlying validation read by Spark. ## Describe inputs and outputs * Comments can be added to Excel names in the Excel **Name Manager**. You can find the name comments in [Service Documentation](/navigation/service-documentation.md) and [API Tester](/navigation/api-tester.md#documentation). ## Prevent integration issues * Review [How to: Prepare an Excel file for Spark](/build-spark-services/how-to-prepare-an-excel-file-for-spark.md). * If `Xinput` and `Xoutput` have been defined for the same range, Spark may not calculate the results properly. * If you are using an input range to submit key-value pairs, modify your Excel file to retrieve the values using a lookup function for the key identifier rather than a direct link. If the order of the items in the input range changes, a lookup can continue to return the correct value. {% file src="/files/pl9lR6ZHs7PTMtnShQ7I" %} ## Understand 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 contain 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. * Values of `""` or `null` will clear the value in a cell. * Any missing values (unreferenced) are replaced by the default value. This includes if an entire column is missing. * In the above example, the table input is processed as `2 rows x (4 columns of inputs + 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)), for example `[{"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 is", which is `2 rows x 4 columns` of inputs. * For an `Xoutput` range, any empty rows from the bottom are trimmed from the response. A row is considered empty if every cell is empty or every cell is `=""`. * Columns without a heading are assigned a heading based upon the column address 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.
The first row contains merged headers for Personal and Professional which behave as a hierarchy over the headers in the second row..
## Prerequisites for dynamic validation The [API Tester](/navigation/api-tester.md) and [Validation APIs](/spark-apis/validation-apis.md) can pick up validations where a validation depends on the formulas of other cells, e.g. city and state combinations. However, there are a number of requirements needed for this to work: * This applies to single cell `Xinput`. * **Data Validation** that is dynamic must be defined using absolute cell references, e.g. `$A$1`. * For **Data Validation List**, Dynamic array functions are not supported. It is recommended to use [`OFFSET`](https://support.microsoft.com/en-us/excel/functions/offset-function), [`MATCH`](https://support.microsoft.com/en-us/excel/functions/match-function), [`COUNTIFS`](https://support.microsoft.com/en-us/excel/functions/countifs-function) functions instead. An example is included in [Validation APIs](/spark-apis/validation-apis.md#sample-api). * Dynamic validations are only evaluated to `1` level of evaluation for performance considerations. In the **Data Validation** settings, when defining the formula for the dynamic validation rules (such as the **List**, **Source**), if the referenced ranges also contain formulas, then the dynamic validation will fail. For example, if the formula in **Source** included `$B$1:$B$2` but the cell `$B$1` also includes a formula, then the dynamic validation will fail.