Excel calculation - FlowForma

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:

Copies your template workbook to a destination file.
Writes the answers you have mapped into the input cells of that copy.
Forces Excel to recalculate the whole workbook.
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.

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.
Output cell mappings: a grid that maps cells of the recalculated workbook back into form questions. See 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.

{
  "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.
`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:

{
  "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.

​Title

​Rule condition(s)

​Before you start

​Rule actions

​Cell addresses

​Input cell mappings

​Reading rows from a repeating table

​Output cell mappings

​Value types

​What happens when the rule fails

Title

Rule condition(s)

Before you start

Rule actions

Cell addresses

Input cell mappings

Reading rows from a repeating table

Output cell mappings

Value types

What happens when the rule fails