> ## Documentation Index
> Fetch the complete documentation index at: https://docs.flowforma.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Excel calculation
> Use the Excel calculation rule to run an Excel workbook's formulas server side, writing form answers into input cells and reading results back into questions.
The Excel calculation business rule lets you reuse an existing Excel workbook as the calculation engine for a form. You keep the formulas in the workbook, and the rule moves values in and out of it.
This is useful when the logic already exists in a spreadsheet that the business owns and maintains, such as a pricing model, a quotation calculator, or an actuarial projection, and you do not want to rebuild those formulas as FlowForma rules.
Each time the rule runs it:
1. Copies your template workbook to a destination file.
2. Writes the answers you have mapped into the input cells of that copy.
3. Forces Excel to recalculate the whole workbook.
4. Reads the output cells you have mapped and writes those values back into form questions.
The destination file is kept, so each form has an auditable copy of the workbook that produced its numbers.
The rule can be found in the Data Integration section of the Add rules panel in the **Flow Designer**.
## Title
The first part of the business rule lets you define a **title** for your rule and select the event on which it operates. The default title is the standard **FlowForma** rule name; change it by deleting the text and entering your own.
In the **event** field you determine when the rule is executed. For a flow this can occur when the form is loaded, started, or completed. For a step this can occur when the step is started, saved, or completed. For questions it can occur when the question is updated.
## Rule condition(s)
The second part of the business rule lets you set conditions. These are not required, but if entered they control when the rule is executed in relation to other items in the form.
## Before you start
* Your FlowForma tenant must be configured for Azure AD app-only authentication. The rule uses it to drive Excel's calculation engine. If it is not configured, the rule fails with a message saying so. Contact FlowForma support to have it enabled.
* The template workbook and the destination file must both be within the same site as the flow.
* The destination file must be in the site's default document library (usually **Documents** or **Shared Documents**). Other libraries on the site are not supported.
* The workbook must be `.xlsx`. Formulas must already be in the template: the rule does not add or edit formulas, it only sets values and reads results.
* You need the step, question, and sub-question codes of every question you want to map. These are shown in the properties panel of the [Flow Designer](/product/creating-flows/flow/flow-designer/overview).
## Rule actions
* **Template file** (required): the server-relative path of the workbook holding your formulas, including the file name and `.xlsx` extension. This file is never modified.
* **Destination file** (required): the server-relative path where the calculated copy is written, including the file name and `.xlsx` extension. Any folders in the path that do not exist yet are created.
* **Overwrite existing destination file**: checked by default. When checked, the destination file is replaced on every run. When cleared, the rule fails if a file already exists at that path, so use a token such as `{FormID}` in the destination path to keep the name unique.
* **Input cell mappings**: a JSON block that maps form answers into cells of the workbook. See [Input cell mappings](#input-cell-mappings).
* **Output cell mappings**: a grid that maps cells of the recalculated workbook back into form questions. See [Output cell mappings](#output-cell-mappings).
Both file paths accept text tokens, so the destination can change on every run: `{FormID}`, `{StepID}`, `{FlowTitle}`, `{StepTitle}`, `{FormTitle}`, `{FlowFormaUrl}`, `{FlowFormaParentUrl}`, `{FlowFormaSiteCollectionRoot}`, `{QuestionCode}`, and `{StepCode|QuestionCode}`.
`{FlowFormaParentUrl}` and `{FlowFormaSiteCollectionRoot}` resolve to a location above the flow's own site, so on their own they point outside it and the rule fails. Include the rest of the path back into the site, for example `{FlowFormaParentUrl}/Host_Web/Shared Documents/Calculators/{FormID}.xlsx`.
The destination is always created fresh from the template before any values are written, so values from a previous run never carry over into the next one.
### Cell addresses
Both mapping sections use the same cell address format: an optional worksheet name, an exclamation mark, then the cell reference, for example `Input!B305`.
* On a workbook with more than one worksheet, always include the worksheet name. A cell address with no worksheet name is ambiguous and the rule fails rather than guessing which sheet you meant.
* On a single-sheet workbook you can omit the worksheet name.
* Worksheet names in a cell address may contain letters, numbers, and spaces. A sheet whose name contains other characters cannot be targeted.
* Only single cells are supported, not ranges. Map each cell individually.
### Input cell mappings
Enter a JSON object with a `Mappings` array. Each entry writes one answer into one cell.
```json theme={null}
{
"Mappings": [
{
"Question": {
"StepCode": "PjzGtBqxt5",
"QuestionCode": "PjzGtBqxt5.0CNiw9Kct5"
},
"Cell": "Input!B305",
"ValueType": "number"
},
{
"Question": {
"StepCode": "PjzGtBqxt5",
"QuestionCode": "PjzGtBqxt5.WddnWKCV2E"
},
"Cell": "Input!B316",
"ValueType": "string"
}
]
}
```
| Property | Required | Description |
| -------------------------- | --------------------- | ----------------------------------------------------------------------------------------------------------------------------------- |
| `Question.StepCode` | Yes | The step code of the question to read. |
| `Question.QuestionCode` | Yes | The question code, in `stepCode.questionCode` form. For a repeating table, this is the table question itself. |
| `Question.SubQuestionCode` | Repeating tables only | The column inside the repeating table to read, in `stepCode.tableCode.columnCode` form. |
| `Cell` | Yes | The cell to write, for example `Input!B305`. |
| `ValueType` | Yes | `number`, `boolean`, or `string`. See [Value types](#value-types). |
| `FilterQuestion` | Repeating tables only | An object of the form `{ "SubQuestionCode": "stepCode.tableCode.columnCode" }`, naming another column in the same row to filter on. |
| `FilterValue` | With `FilterQuestion` | The value that `FilterQuestion` must match for a row to be included. |
| `RowIndex` | Repeating tables only | Which row to read, counting from 1. |
Click **Validate & format JSON** to check the block before saving. It reports invalid JSON, a missing or malformed cell address, a missing `Question`, an unrecognised `ValueType`, and any step, question, or sub-question code that does not exist in the flow. When the block is valid it is reformatted and the number of mappings is confirmed. The same checks run when you save the rule, so a rule with an invalid mapping block cannot be saved.
Input mappings are typed by hand and do not use the question picker. Enter the codes as plain text exactly as shown above.
### Reading rows from a repeating table
For a question inside a repeating table, add `FilterQuestion`, `FilterValue`, and `RowIndex`. The rows are filtered first, then `RowIndex` picks the Nth row from the filtered result. It is not an absolute position in the table.
Take a repeating table of investments with a **Type** column and a **Value** column:
| Table row | Type | Value |
| --------- | -------- | ------- |
| 1 | Pension | 10,000 |
| 2 | Property | 250,000 |
| 3 | Pension | 5,000 |
| 4 | Shares | 7,500 |
| 5 | Pension | 2,000 |
This mapping writes the value of the **second pension** into `Input!B115`:
```json theme={null}
{
"Mappings": [
{
"Question": {
"StepCode": "PjzGtBqxt5",
"QuestionCode": "PjzGtBqxt5.investments",
"SubQuestionCode": "PjzGtBqxt5.investments.value"
},
"FilterQuestion": { "SubQuestionCode": "PjzGtBqxt5.investments.type" },
"FilterValue": "Pension",
"RowIndex": 2,
"Cell": "Input!B115",
"ValueType": "number"
}
]
}
```
Filtering on `Type` = `Pension` gives table rows 1, 3, and 5. `RowIndex` of 2 selects the second of those, which is table row 3, so `5,000` is written to `Input!B115`.
To feed a whole block of the workbook, repeat the mapping once per row with an increasing `RowIndex` and cell, for example `RowIndex` 1 to 7 against `Input!B114` to `Input!B120`.
If you omit `RowIndex`:
* With a `FilterQuestion`, the first matching row is used.
* Without a `FilterQuestion`, and when the rule is triggered by a question inside the repeating table, the row being edited is used. Otherwise the first row is used.
If a row cannot be found, for example `RowIndex` is 5 but only three rows match the filter, that mapping is skipped. The cell keeps whatever value the template held and the rule still succeeds. Design the template so an unwritten cell gives an obviously wrong result, or add a form validation to guarantee the row count.
### Output cell mappings
Output mappings are entered as a grid rather than JSON. For each row:
* **Cell** (required): the cell to read after recalculation, for example `L1 Ret1!H37`.
* **Question** (required): the question to write the result into, chosen with the question picker.
Click the plus button to add another row and the cancel button to remove one.
Any question type can be a target. The value read from the cell is applied to the question the same way any other rule sets a value, so number formats, date formats, and choice values are handled by the question itself.
Yes/No questions are the one special case: a cell returning `TRUE` or `FALSE` is converted to **Yes** or **No** automatically, so you do not need a formula in the workbook to produce the text.
If a mapped output cell returns nothing, the target question is left unchanged.
## Value types
`ValueType` tells the rule how to convert a form answer before writing it to Excel. It applies to input mappings only.
| `ValueType` | Suitable FlowForma question types | What is written to the cell | Notes |
| ----------- | ----------------------------------------------------------------- | --------------------------- | ---------------------------------------------------------------------------------------------------------------------------------- |
| `number` | Number, Currency, Percentage, Auto number | A number | Thousands separators are removed. A percentage question is converted to the fraction Excel expects, so 6.5% is written as `0.065`. |
| `boolean` | Yes/No | `TRUE` or `FALSE` | **Yes**, `true`, and `1` become `TRUE`. Anything else becomes `FALSE`. |
| `string` | Single line of text, Multiple lines of text, Choice, Person, Date | Text | The answer is written as it appears on the form. An unanswered question writes an empty string. |
Unanswered questions mapped as `number` or `boolean` are skipped rather than written as `0` or `FALSE`, so the cell keeps the template's own value. If your template relies on a specific default in an input cell, make sure that default is in the template rather than depending on the rule to write it.
Use `string` for a date only when the workbook treats it as text. Excel cannot calculate on a date written as text, so if the formulas do date arithmetic, add a question that produces the date as a number and map that as `number` instead.
## What happens when the rule fails
If the rule cannot complete, for example a Graph call fails or a cell address is invalid, then:
* The output questions are left unchanged. On a calculator this looks the same as the numbers simply not moving, so do not treat unchanged outputs as a successful run with no change.
* The destination file copy is removed, so the presence of a destination file is not proof that the calculation ran.
* The error is recorded in the flow's Failures list with the step, question, and rule name. Check there first when a calculation does not produce a result.
Common causes:
* The tenant is not configured for Azure AD app-only authentication.
* The template or destination path resolves outside the flow's site.
* The destination file already exists and **Overwrite existing destination file** is cleared.
* A cell address has no worksheet prefix on a workbook with more than one worksheet.
* A cell address points at a sheet or cell that does not exist in the template.
A mapping whose question cannot be resolved is a softer failure: it is logged to the Failures list, that single cell is skipped, and the rest of the calculation still runs.
* **Stop execution**: if stop execution is enabled and this rule is executed, no other rules are executed.
* **Enabled**: rules aren't saved to a list like questions and steps are, so if you want to stop a rule executing without deleting it, toggle this option to enable or disable the rule.
* **Enable audit**: if checked, a record is added to the audit trail when this rule is executed. This option is available when the Compliance feature pack is activated.
The Excel calculation rule needs network access and is supported on public forms.