In this page

2017 March updates

Important changes were made in the automation options available for JIRA. We updated this article to reflect those. Here is some further explanation of the changes, and what those mean for the Excel Automation Plugin for JIRA users.

First, the JIRA Automation Plugin (originally launched by Atlassian) was taken over by Code Barrel. This add-on was renamed to Automation Lite for JIRA and is now maintained by Code Barrel. This article currently focuses on automating Excel exports using this add-on as trigger-action framework. We are dedicated to maintain this integration in the future, as a free automation solution.

At the same time, Code Barrel has another offering: their next generation paid automation add-on called Automation for JIRA. Although the name may suggest that this is just a more powerful version of Automation Lite, that's not true! This is, in fact, re-built from scratch and uses a completely different code base. These new automation add-on versions (3.0.0 or newer) will also work with Excel Automation Plugin for JIRA (3.0.0 or newer version needed!), read on!

2017 Jun updates

Code Barrel released the first version of the "next generation" Automation Lite add-on (version 3.0.0 to be precise). This is built from a new codebase, offers a lot more features, and it has a modern UI. Here is what the generation add-on means for the Excel Automation Plugin for JIRA users.

In nutshell: Excel Automation Plugin for JIRA supports both the old Automation Lite codebase and the new one, but you must be careful about the versions.

  • If you use Automation Lite for JIRA 3.0.0 or newer: use Excel Automation Plugin for JIRA 3.0.0 or newer
  • If you use Automation for JIRA 3.0.0 or newer: use Excel Automation Plugin for JIRA 3.0.0 or newer
  • If you use Automation Lite for JIRA pre-3.0.0: use Excel Automation Plugin for JIRA 2.1.0
  • If you use Automation for JIRA pre-3.0.0: these versions do not allow third party actions, i.e. not supported!

As for this article, the screenshots are taken about the old interface. We will update those later when the new UI becomes dominant. For now, the guides can be easily followed even if someone is using the new UI, because although the configuration forms look different, they offer the same fields.

Overview

The free Automation Lite for JIRA by Code Barrel offers a convenient way to implement powerful automation within JIRA. Automation is implemented by defining so-called rules, which are combinations of triggers and actions.

The execution model in a nutshell: if the trigger "happens", then the action is "executed".

Some example triggers are "07:00AM every morning in weekdays", "every two hours", "when an issue is created", "when work is logged on a task" and "when a critical bug is reopened".

Some example actions are "update an issue", "transition an issue", "assign an issue", and "edit the labels of an issue".

Therefore, some example rules are "create a 'weekly meeting' task 07:00AM every Monday" or "assign an critical bug to the component lead when that was reopened".

Automation rules can be easily created, updated, deleted, enabled or disabled in JIRA:

This is a really powerful mechanism to make JIRA work for you. Atlassian has published a blog post Why Atlassian Support uses the JIRA Automation Plugin. Although this plugin has been taken over by Code Barrel and renamed to Automation Lite for JIRA in Mar 2017, we still strongly recommend to read the blog post, as it perfectly summarizes the key concepts, which remained unchanged.

This article explains how to implement automatic generation of Excel spreadsheets from JIRA data. It also shows how to email the resulted file to an email address, save that to the file system, or attach that to a JIRA issue.

Automation for Excel exports

Automation Lite for JIRA can be extended by further plugins, by adding new triggers and new actions.

The Excel Automation Plugin for JIRA by Midori introduces new actions to generate Excel spreadsheets from JIRA data and:

  1. send those to certain email addresses
  2. save those to the file system
  3. attach those to JIRA issues

How will you use it? (examples)

  • Send a daily refreshed Created vs. Resolved tickets timeline to your support team lead.
  • Send a status-vs-priority pivot table of all tickets created in the previous week.
  • Send a summary of open leads to your sales staff every morning.
  • Send a time-spent pivot chart by theme, sprint and individual story to support JIRA-based portfolio management.
  • Save a timestamped snapshot of a saved filter's result to the file system for auditing reasons.
  • Save a custom list of issues updated in the last one hour for an external system to import, for super-easy data integration.
  • Attach the last month's worklog report to the Monthly Close task to save your accounting team from manual and error-prone efforts.

Introduction video

Get started by watching this video, to understand the big picture in a couple of minutes:

Installation

You need to install 3 separate plugins to implement this mechanism. Each plugin can be conveniently installed through the Universal Plugin Manager (UPM). The whole procedure requires only some clicks and some minutes of your time. Please read the UPM tutorial if unsure.

Because of the dependencies, please install the plugins in this order:

  1. Install Better Excel Plugin for JIRA
  2. Install Automation Lite for JIRA (or Automation for JIRA if the lite (free) version does not meet your needs)
  3. Install Excel Automation Plugin for JIRA

Even if you faced some "plugin cannot be initialized" problem during the installation, continue installing all the 3 plugins, then restart JIRA and enable the disabled ones of these plugins in the previous order.

Version compatibility

As this mechanism is composed of JIRA plus 3 plugins, each with its own versioning, the following table defines the recommended combinations. Please note that the table shows version ranges (not specific versions) to avoid that you are wasting your time with a completely unsupported combination.

The golden rule of thumb: you should install the add-on version that is offered by the Universal Plugin Manager (UPM).

JIRA Automation add-on Excel Automation Plugin for JIRA Better Excel Plugin for JIRA
7.0.11 or newerAutomation Lite 3.0.0 or newer
(free)
3.0.0 or newer1.3.0 or newer
7.0.11 or newerAutomation 3.0.0 or newer
(paid)
3.0.0 or newer1.3.0 or newer
6.2 - 7.0.10Automation Lite 1.1.5 - 2.0.3
(paid)
1.0.0 - 2.1.x1.0.0 or newer

In case you install an add-on by uploading it to JIRA (i.e. not via UPM), then check out its "Version History" page at Atlassian Marketplace. That will show the compatible JIRA versions for each add-on version:

  1. Automation Lite version history
  2. Automation version history
  3. Excel Automation Plugin for JIRA version history
  4. Better Excel Plugin for JIRA version history

In this case the golden rule of thumb: you should install the last add-on version that is compatible with your JIRA version.

Automation rules

Sending Excel spreadsheets in email from a JQL query periodically

Configuration steps:

  1. Login to JIRA as JIRA administrator.
  2. Go to AdministrationAdd-onsAutomation.
  3. Click Add Rule.
  4. Select the JQL Filter Trigger and enter your CRON expression. Hint: set it to a frequent value (like every minutes by using "0 * * * * ?") for the testing period. (If you are using Automation 3.0.0 or newer, then expand the Advanced section and check the Process all issues produced by this trigger in bulk checkbox. Otherwise, there will be a separate email sent for each issue.)
  5. Select the Send Excel Action and fill all parameters (read the comments under the input controls). Hint: set the email address to your own mailbox for the testing period.
  6. Save the rule. Also, don't forget to enable the rule if you haven't done so!
  7. Now wait for the first execution to test if the rule works as expected. Check the Audit log in the top of the Automation screen, because that keeps a good track of executions and eventual problems. Also check the JIRA log if there is problem, but the Audit log was not helpful.
  8. If it works as expected, configure the rule with the final CRON expression and with the final email addresses.
  9. You're done!

You can configure the Send Excel Action via this straight-forward form:

Every time the rule is executed, recipients will receive the email with the Excel file in the attachment:

Sending Excel spreadsheets in email on issue events (creation, update, comment, etc.)

Automation Lite for JIRA offers another type of trigger, called Issue Event Trigger. As its name suggest, it triggers an action when a certain event happens to an issue. It also supports post-filtering issues by a JQL expression.

Follow the same steps as in the first section, but use this other type trigger. (Configuring the action part is 100% identical.)

Saving Excel spreadsheets to the filesystem from a JQL query periodically

Follow the same steps as in the first section, but select the Save Excel Action. This action has slightly different parameters, the most important one being the file system path where to save the resulted Excel spreadsheet.

You can configure the Save Excel Action via this straight-forward form:

Every time the rule is executed, the Excel file is saved to the file system:

Hint: if your rule will be triggered multiple times for the same issue(s) and you export to the same path, then the file will be overwritten every time. If this is unwanted, you should include the time stamp in the filename. For example, use the path parameter /my/directory/myexport-${date.get('yyyyMMdd-HHmmss')}.xlsx to get a unique file name at each export.

Saving Excel spreadsheets to the filesystem on issue events (creation, update, comment, etc.)

It is trivially the same as the previous section, but using the Issue Event Trigger.

Attaching Excel spreadsheets to issues from a JQL query periodically

Follow the same steps as in the first section, but select the Attach Excel Action. This action has slightly different parameters, with two notable differences.

Use the Attach to option to select the issue which the resulted Excel spreadsheet will be attached to. This can either be the issue that triggered the execution (first option), or any other issue selected by a JQL query (second option). Using the second option allows you to implement static (ex: select the target issue by its key) or dynamic behaviour (ex: select the most recently created issue of a given issue type).

The File name option is fairly trivial, but there is an additional option Overwrite, which decides what to do at file name collisions. It's important to understand that JIRA allows issues to have multiple attachments with the same filename! If the issue already has an attachment with the same filename when the automation action is executed, two things can happen. If Overwrite is checked, then only the last attachment will be kept, previous ones will deleted (cleaned up). If Overwrite in unchecked, then a separate attachment with the same file name will be created.

You can configure the Attach Excel Action via this straight-forward form:

Every time the rule is executed, the Excel file is attached to the selected issue:

Attaching Excel spreadsheets to issues on issue events (creation, update, comment, etc.)

It is trivially the same as the previous section, but using the Issue Event Trigger.

Configuration

Template variables for actions

In addition to hard-coded static values, several action parameters accept dynamic values using template variables in Velocity syntax. For example, you can say "send this Excel spreadsheet to the creator of the corresponding issue", instead of entering a concrete email address. You can find a "Tip: use template variables." message at each parameter in the configuration UI, which accepts these.

The variables are best explained by some examples:

## ${issues} is a collection of issue objects.
## Note that starting from $issues, you can traverse the object graph:
## enclosing projects, priority objects, status objects, assignees, reporters, etc.
## You do it the same exact way as in any other Velocity template.
## See: https://docs.atlassian.com/software/jira/docs/api/latest/com/atlassian/jira/issue/Issue.html

## - standard fields -----------------------------------------------

## the number of issues passed (as size of the collection)
${issues.size()} pending bugs

## the key of the first issue (accessed by the item's index)
${issues.get(0).key} done!

## the summary of the first issue
NEW: ${issues.get(0).summary}

## the key of the first issue's project (accessed as embedded property)
Report for ${issues.get(0).projectObject.key}

## email address of the first issue's creator (typically used as a target email address)
${issues.get(0).creator.emailAddress}

## email address of the first issue's assignee (typically used as a target email address)
${issues.get(0).assignee.emailAddress}

## - custom fields -------------------------------------------------

## The #cfValue() macro displays nicely formatted values from custom fields,
## depending on the custom fields' types.

## formatted value of the custom field with the ID=10000 in the first issue, with line break
#cfValue(${issues.get(0)} 10000)

## formatted value, without line break
#cfValueInline(${issues.get(0)} 10001)

## - dates & times -------------------------------------------------

## The ${date} tool allows freely formatting date and time values using simple "pattern strings".
## See: https://velocity.apache.org/tools/devel/javadoc/org/apache/velocity/tools/generic/DateTool.html

## formatting current date and time as "20141107-1046"
${date.get('yyyyMMdd-HHmm')}

## formatting an existing date object in medium length (ex: "Oct 7, 2003 3:14:50 AM")
$date.format('medium', $someDateObject)

## - etc. ----------------------------------------------------------

## ${user} is the username string you specified as "Actor"
This Excel is rendered as user <${user}>           ## for debugging purposes

## ${templateName} is the filename string you specified as "Template name"
This Excel is rendered from <${templateName}>      ## for debugging purposes

## ${to} is the email address string you specified as "Email address"
All stakeholders receiving this email: ${to}
 
## - rule and trigger configuration --------------------------------

## The variables below give convenient access to configuration of the automation rule.
## All values are strings.
## Not documented here in details, as they just hold the corresponding setting available in the UI.
${automationRuleId}                                ## ex: "1"
${automationRuleName}                              ## ex: "Archive closed bugs"

${automationTriggerProcessIssuesInBulk}            ## ex: "true"
${automationTriggerOnlyUpdatedIssues}              ## ex: "false"

${automationTriggerJiraJqlExpression}              ## ex: "filter = 10123"
${automationTriggerJql}                            ## same, for backward compatibility with old JXLSA versions
${automationTriggerScheduleMethod}                 ## ex: "FIXED", "CRON"
${automationTriggerExecutionMode}                  ## ex: "jql"
${automationTriggerScheduleRate}                   ## ex: "10"
${automationTriggerScheduleRateInterval}           ## ex: "86400"
${automationTriggerScheduleCronExpression}         ## ex: "0 0 */2 * * ?"

${automationTriggerPropertyKeys}
${automationTriggerIssueEvent}                     ## ex: "issue_commented"
${automationTriggerEventKey}                       ## ex: "jira:issue_updated"

## ${automationTriggerRawParameters} is a multi level string-keyed map of all trigger parameter objects.
## These give a lower-level access to the automation rule, also including 3rd party triggers.
${automationTriggerRawParameters}                  ## ex: {schedule={rate=10, rateInterval=86400}, jql=[project=DEMO], executionMode=[jql]}

Model variables for the XlsApi

Excel Automation Plugin for JIRA relies on the XlsApi (implemented by Better Excel Plugin for JIRA) to generate the actual Excel spreadsheet files. More precisely, it invokes the getXls() API end-point and passes several variables to that. These variables can be used to affect the resulted Excel spreadsheet in intelligent ways: from just displaying them in the Excel spreadsheet to using them as condition variables. For example, if the automation action's name contains the word "weekly", then the date format could turn to "2017 W13" during the rendering, otherwise to "2017 Feb".

The following variables are available both in the Excel templates and in the Groovy scripts during the rendering. In the template you would typically prefix them with a dollar sign and wrap them in curly braces (as shown below), like ${automationActionName}. In Groovy scripts, they are regular "global" variables, no need for the dollar sign and the curly braces, just automationActionName.

## - rule and trigger configuration --------------------------------

## The variables below give convenient access to configuration of the automation rule.
## All values are strings.
## Not documented here in details, as they just hold the corresponding setting available in the UI.
${automationRuleId}                                ## ex: "1"
${automationRuleName}                              ## ex: "Archive closed bugs"

${automationTriggerProcessIssuesInBulk}            ## ex: "true"
${automationTriggerOnlyUpdatedIssues}              ## ex: "false"

${automationTriggerJiraJqlExpression}              ## ex: "filter = 10123"
${automationTriggerJql}                            ## same, for backward compatibility with old JXLSA versions
${automationTriggerScheduleMethod}                 ## ex: "FIXED", "CRON"
${automationTriggerExecutionMode}                  ## ex: "jql"
${automationTriggerScheduleRate}                   ## ex: "10"
${automationTriggerScheduleRateInterval}           ## ex: "86400"
${automationTriggerScheduleCronExpression}         ## ex: "0 0 */2 * * ?"

${automationTriggerPropertyKeys}
${automationTriggerIssueEvent}                     ## ex: "issue_commented"
${automationTriggerEventKey}                       ## ex: "jira:issue_updated"

## ${automationTriggerRawParameters} is a multi level string-keyed map of all trigger parameter objects.
## These give a lower-level access to the automation rule, also including 3rd party triggers.
${automationTriggerRawParameters}                  ## ex: {schedule={rate=10, rateInterval=86400}, jql=[project=DEMO], executionMode=[jql]}

Support for JIRA Data Center

Excel Automation Plugin for JIRA 2.0.0 and newer versions fully support JIRA Data Center, enabling automation in clustered JIRA deployments.

The execution model is simple: automation rules will be executed on exactly one node in the cluster. (This is guaranteed by Automation Lite for JIRA.)

The resulted behaviour is:

  • Send Excel Action: one email will be sent from the cluster (not one per node!). This is exactly what you want.
  • Save Excel Action: the file will be saved by one node. If the file is written to the node's local non-replicating filesystem, then the file will not automatically appear on other nodes. If output file replication is a requirement, you should use an output directory that is replicated among the clusters on a filesystem level. This is out of the scope of JIRA Data Center and the Excel Automation Plugin for JIRA.
  • Attach Excel Action: the attachment will be created on one node, but that will then be automatically replicated to all other nodes. The replication itself is the responsibility of JIRA Data Center (not the Excel Automation Plugin for JIRA).

Generally speaking, Excel Automation Plugin for JIRA works perfectly with JIRA Data Center.

Best practices

Reusing existing saved filters

When you want to re-use an existing saved filter as trigger, use one of the following expressions in the trigger's JQL:

filter = 10100					## executes the saved filter with the ID = 10100
filter = "My important tasks"	## executes the saved filter with the given name

Tip: using the ID-based syntax is safer, because the second one will break if someone renames the saved filter.

When using the Excel templates in your automation action that rely on dynamic column configuration, like issue-navigator.xlsx or issue-navigator-with-comments.xlsx, there is an additional powerful feature. Those templates will intelligently find and use the column configuration of the saved filter! In other words, just enter "filter = 10100" for the trigger, then the trigger will run the corresponding query and the action will export the result using the corresponding column configuration. This makes reusing saved filters a breeze.
(since Better Excel Plugin 2.4.0 and Excel Automation Plugin 2.2.0)

Questions?

Ask us any time.