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 (developed by Atlassian) 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 the app went through lots of changes since the blog post, we still strongly recommend reading it, 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 More options 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 with this straight-forward form:

Most action parameters should be trivial, but the two states of the Send a single email switch may require a quick comparison:

"Send a single email" state Off (default) On
Number of emails A separate email is sent to each recipient in the "To" parameter.
(N emails are sent in total, where N is the number of "To" addresses.)
A single email is sent to every recipient in the "To" parameter.
(Only 1 email is sent.)
"Cc"/"Bcc" recipients Not allowed.
(Because it would lead to an email flood! Imagine this: you want to send separate emails, with the "To" field set to "alice@acme.com, bob@acme.com" and "Cc" set to "cecil@acme.com". In this case, Cecil would receive the same email twice: both the one sent to Alice and the one sent to Bob!)
Allowed.
(They are simply the"Cc" and "Bcc" recipients of the single email.)
Privacy Recipients will not know about each other. Recipients will know about each other.
(More precisely, they can see each other in the "To" and "Cc" fields, but not in "Bcc".)
Email threads Not knowing the other recipients, it is not possible to start an email thread. By using "reply all" on the original email, it is possible to start an email thread with the other recipients.

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 with this straight-forward form:

The File system path parameter determines the full file system path (including the filename, too) where to save the generated Excel spreadsheet. You can freely use local paths, network paths and drives here. See these examples:

## for Linux, Mac OS X, U*x variants:
/my/directory/${issues.get(0).key}-${date.get('yyyyMMdd-HHmm')}.xlsx
## ...will result in the path: "/my/directory/FOO-123-20141027-1639.xlsx"

## for Windows "regular" paths:
## (note the double backslash characters used for escaping!)
c:\\my\\directory\\${issues.get(0).key}-${date.get('yyyyMMdd-HHmm')}.xlsx
## ...will result in the path: "c:\my\directory\FOO-123-20141027-1639.xlsx"

## for Windows network locations:
\\\\my-server\\my\\directory\\${issues.get(0).key}-${date.get('yyyyMMdd-HHmm')}.xlsx
## ...will result in the path: "\\my-server\my\directory\FOO-123-20141027-1639.xlsx"

Tips for advanced use cases:

  • Saving the Excel file to a remote location that requires authentication: just map it as a network drive with the proper login credentials, then configure the action to write to the resulted drive.
  • Saving the Excel file to the cloud (Dropbox/Google Drive/Microsoft OneDrive): see the recipes.

Avoid these typical problems:

  • Make sure that the target directory can be written by the Jira process.
  • If you use an expression in the File system path parameter, make sure that it will not result in characters that would break the resulted path.
    For example, it is safe to use the key field of the issue in the expression, because issue keys can contain alphanumeric characters and '-' only, and all those are allowed in paths. In contrary, the summary field can contain nearly any character, some of which are not allowed in paths. Note that the disallowed characters in paths depend on your operating system!
    Similarly, if a summary like "Crash with low/zero memory" contains the path separator character ("\" on Windows and "/" on Linux), it will be rendered to the final path as "/my/directory/Crash with low/zero capacity.xlsx", and there will be unexpected directories created.
    To avoid these, use an expression like this to replace the dangerous characters with '-':
    /my/directory/${issues.get(0).summary.replaceAll("[^\w- ]", "-")}.xlsx
    

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 with 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 that 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

## - basic ---------------------------------------------------------

## ${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}, ${cc} and ${bcc} are the email address strings you specified in the 3 action parameters
Primary recipients: ${to}
Secondary recipients: ${cc}
Blind carbon copy goes to: ${bcc}

## - 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)

## - 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)
${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)
${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]}

Questions?

Ask us any time.