Automation to send Spreadsheet of JQL Results to Users

[Mathew Lederman]

Looking for a way to build an automation rule to populate a spreadsheet (csv, xls, xlsx, Google Sheets, etc.) with the results of a JQL query and send that to a pre-defined list of users. 

I have recommended subscriptions but my users' customers are external and we do not want to share links to our internal systems which they will be blocked from accessing. Additionally, we cannot give them access to generate their own exports as our instance is IP restricted.

Does anybody have any suggestions?

[Aron Gombas _Midori_]

@Mathew Lederman The Better Excel Automation implements exactly what you are looking for. 

As part of standard Jira automation rules, it can create custom Excel sheets, attach that to an email and send that to a set of email addresses. The recipient doesn't have to have a Jira account. A rule looks like this:

Here is the step-by-step guide (and video).

(Discl. this app is developed by our team.)

[Bill Sheboy]

Hi @Mathew Lederman 

Although there is a rule action to Create Doc on Google Drive, it only creates that as text.

Some alternative options to try are:

• use the Send Web Request action to try calling the Google REST API to create the doc
• put CSV in the Google doc from the rule action, and ask users to copy-and-paste into a spreadsheet
• send an email with CSV for users to copy-and-paste into a spreadsheet
• send an email with HTML for users to review, and copy-and-paste when needed into a spreadsheet

Kind regards,
Bill

[Mathew Lederman]

Bill,

As always, appreciate you digging in.

How would I populate the CSV from a JQL query? Or are you suggesting to build comma separated values from JQL results?  

[Bill Sheboy]

There are certainly marketplace apps to fill this need, although I have not used any of them.

My suggestions were options to use automation rules.  For example, one could use the Lookup Work Items action with JQL to gather and then iterate to construct a CSV for an email, perhaps like this:

"Key","Summary","Assignee"
{{#lookupIssues}}
{{key}},"{{summary}}","{{assignee.displayName}}"
{{/}}