In this page

Note that there are three techniques to implement automatic processes with Better Excel Exporter for Jira. To be sure that using the Better Excel Automation for Jira app is the best for you, read the Automation article (in the Better Excel Exporter for Jira documentation) before this one.

Overview

The free Automation Lite for Jira app 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 app 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 apps, by adding new triggers and new actions.

Better Excel Automation 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:

Automation rules

Sending Excel spreadsheets in email from a JQL query periodically

Configuration steps:

  1. Login to Jira as Jira administrator.
  2. Go to AdministrationSystemAutomation rules.
  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

Better Excel Automation for Jira relies on the XlsApi (implemented by Better Excel Exporter 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

Better Excel Automation 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 Better Excel Automation 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 Better Excel Automation for Jira).

Generally speaking: Better Excel Automation for Jira works perfectly with Jira Data Center.

Questions?

Ask us any time.