Is it possible to automate exporting jira filters to excel sheet and emailing on scheduled intervels

MahanthPrudhvi July 11, 2022

Is it possible to automate exporting jira filters to excel sheets and emailing them on scheduled intervels to selected people

4 answers

1 accepted

1 vote
Answer accepted
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 11, 2022

Hi @MahanthPrudhvi  You can email the filter result using Filter Subscription in JIRA  :- https://www.atlassian.com/blog/jira-software/jql-the-most-flexible-way-to-search-jira-3-of-4

If you want to directly import JQL result into excel, then you need run Power Query in excel, in power query put JQL filter API and add fields in excel columns . In order automate this, you need to use Power automate. 

Thanks

MahanthPrudhvi July 12, 2022

Hi @Vikrant Yadav thanks for the response, now we are already using the filter subscriptions but we are looking for the reports to be converted into excel and then those excel reports should be automatically mailed to specific emails on scheduled times. May I know the steps in detail. 

Like Vikrant Yadav likes this
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 13, 2022

Hi @MahanthPrudhvi  Sorry for late response. 

You can use JQL API in Excel to fetch JQL result in excel :-

For eg :- JQL API :-  https://jira.atlassian.com/rest/api/3/search?jql=project%20%3D%20"ABCD"%20AND%20reporter%20%3D%20vikrant-yadav2

 

Open Excel >> Data >> Data Source from Web >> Paste JQL API url >> Ok >> Power Query windows opens automatically . Now you can parse json in excel columns using steps in below link :- 

https://docs.microsoft.com/en-us/power-query/parse-json-xml

https://community.powerbi.com/t5/Desktop/Extracting-value-from-JSON-column-into-a-new-column/td-p/2006271

 

Once it's done , you only need to refresh connection to update data( jql result) in excel. 

You can upload file in one drive and create power automate which refresh file and send excel as to a particular user.

 

it's very long process i know but it's only one time setup and without plugin it works. If you have plugin mentioned by @Roland Traier-Kiss _Midori_  you can use that one. 

 

Thanks

V.Y

MahanthPrudhvi July 14, 2022

Hi @Vikrant Yadav thanks for the response . I will try this one.

MahanthPrudhvi July 14, 2022

Hi @Vikrant Yadav it says 

 https://jira.atlassian.com/rest/api/3/search?jql=project%20%3D%20"ABCD"%20AND%20reporter%20%3D%20vikrant-yadav2

this is a dead link.

Thank you.

Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 15, 2022

Hi @MahanthPrudhvi  Replace highlighted url according to your Atlassian instance :- 

 

https://powerusers.atlassian.net/rest/api/2/search?jql=order%20by%20created%20DESC

 

Thanks

Like MahanthPrudhvi likes this
1 vote
Roland Traier-Kiss _Midori_ July 12, 2022

@MahanthPrudhvi 

This is a highly sought-after feature for Better Excel Automation users as well. That would be able to deliver exactly what you requested.

Apps and integrations like these would be important for Jira Cloud customers, but unfortunately, Atlassian currently doesn't allow integrations for Automation for Jira Cloud.

If you think this would be a useful feature for your team, please follow these steps to express your needs toward Atlassian:

Please vote (click "thumbs-up") for the feature request here:  https://jira.atlassian.com/browse/CLOUD-10966

 

Please do the same for Midori's Better Excel Automation feature request as well: https://midori.zendesk.com/hc/en-us/articles/229993328-Better-Excel-Automation-for-Jira-Cloud

0 votes
Tony Bathich February 20, 2024

I think you can simply use the addon for Excel.. which supports JQL.

- no need to export to excel

- the user can simply refresh excel

https://appsource.microsoft.com/en-us/product/office/WA200000556?tab=Overview

Hope this helps

0 votes
AMP Admin Email Account February 8, 2023

Hi All,

Thought this will help others. You can follow the method mentioned by Christopher Lebruh here: https://community.atlassian.com/t5/Jira-Core-Server-questions/Import-JIRA-REST-API-results-to-Excel-Web-Power-Query/qaq-p/1083354

It's much faster and easier to implement. You don't have to parse using this method.

Hope this helps someone.

 

Thanks.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
PERMISSIONS LEVEL
Site Admin
TAGS
AUG Leaders

Atlassian Community Events