Formulas v5.0.0

Formulas are powerful tools to surface hidden information in Jira, perform arithmetic operations, and turn raw data into realtime, actionable insights. You can automate planning, streamline tracking, enhance reporting, or whatever it is you're doing in Jira, right where your work lives.

To use formulas in a Sheet, add formula columns to the table. Once added, a formula column requires configuration. To configure the column and enter a formula, either click one of the initially shown Configure links in its cells, or hover with the cursor over the header of the column, click the More icon that appears, and choose Edit formula. In the dialog that appears, in the Cell content section, click the Formula field to open the formula composer.

The formula composer has various panels that assist you in writing the perfect formula.

• Language switch - At the top right of the dialog, you can switch between JFL and JFL Script mode. Read more about the differences between these languages below.
• Formula - The big input area on the left is where you type in your formula.
• Suggestions - Just below the formula input, you can find auto-complete suggestions responsive to what you enter.
• Result - At the bottom left, you can see a live preview of the result of the evaluation of your formula. You can pick which Work item you'd like to use for this preview.
• Help - On the right in the purple panel, there are always useful hints based on your input. E.g. you can explore data structures of Work items, Fields, and properties, as well as find descriptions and syntax help for functions, operators, and more.
• Errors - Also on the right in the red panel, in a separate tab, you can browse any errors your current formula draft may cause. This is intentionally very responsive to assist you in improving your input.

Language

If you have come across formula languages in other products like Microsoft Excel, Google Sheets, Airtable, Smartsheet, etc. this should be a somewhat familiar experience.

There are, however, some distinctive syntactical traits as well as a different approach to referencing data. Due to the dynamic nature of JXL Sheets, there is no way of referencing specific individual cells or cell ranges of the table (e.g. A1-B5). Instead, you refer to Jira objects (e.g. Work items) and their Fields, data, and properties.

JFL

JFL is an Excel-like formula language for Jira that's suitable for most of your use cases. (For more complex tasks, you can also switch over to JFL Script, which is an even more powerful, JavaScript-like language that requires some experience in scripting or programming.)

Just like an Excel formula, JFL is written in one line, without line breaks.

You can reference Jira objects (e.g. Work items) and their data, use methods like slice() or trim(), functions like DATE() or IF(), and much more.

Example

( this.customfield_10010 * VALUE(this.customfield_10011) * this.customfield_10012 ) / this.customfield_10013

In this example, we calculate a RICE prioritisation formula $\left(\right(Reach\times Impact\times Confidence)÷Effort)$, by multiplying and dividing the values of some Custom fields that we have created for this purpose.

We also use the VALUE() function to convert the data type (opens new window) of one of the Custom field values involved. The error panel will always let you know when Jira data makes type conversions like this necessary.

Getting started

Start by typing this. and you will receive suggestions for Fields, data, and properties in the suggestions panel. The this keyword is used to reference the current Work item.

Assuming the Custom field you'd like to reference is named "Reach", type that field name next, i.e. this.rea ...so the suggestions panel offers the "Reach" Custom field. When you pick that suggestion, the relevant Custom field ID will be inserted into your formula.

JFL Script

JFL Script is a scripting language suitable for all your use cases, from simple calculations to the most complex of jobs. It's a sub spec of JavaScript (opens new window) and TypeScript (opens new window) that requires some experience in scripting or programming, or willingness to learn. (For a simpler, more Excel-like formula language still suitable for most use cases, you can switch over to JFL.)

JFL Script can be written on multiple lines. Use your ↲ Enter key for line breaks just like you're used to.

You must write a return statement (opens new window) to stop execution of the script and publish a value into table cells.

Just like in JavaScript, you can declare variables and functions, reference data objects (e.g. Work items) and their properties, use methods like slice(), conditional statements like if(), access utility objects like Math, write comments, and much more.

Example

// Only run the formula for work items that aren't epics
if (this.issueType.description != "Epic") {
    // Get the values of the custom fields Reach, Impact, Confidence, and Effort
    let reach = this.customfield_10010 ?? 100;
    let impact = this.customfield_10011 ? parseFloat(this.customfield_10011.value) : 1;
    let confidence = this.customfield_10012 ?? 80;
    let effort = this.customfield_10013 ?? 1;
    // Calculate RICE score
    let riceScore = (reach * impact * confidence) / effort;
    // Publish score to cell
    return riceScore;
}

In this example, we calculate a RICE prioritisation formula $\left(\right(Reach\times Impact\times Confidence)÷Effort)$, by multiplying and dividing the values of some Custom fields that we have created for this purpose.