How can I link Jira to a Spreadsheet that updates a burndown chart daily

Chris Langlay-Smith March 13, 2017

I have an excel burndown chart (see image) which calculates the burndown of a sprint by number of hours (Original estimate) per issue completed in a day. An average figure is given to issues without a time estimate. 

At the moment i manually total all the issues and hours everyday using a filter which shows all issues completed that day.

Is there a way i can have this automatically populate my spreadsheet from JIRA?

 

Thanks in advance for any help.Excel Burndown image.png

2 answers

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

0 votes
Aron Gombas _Midori_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 14, 2017

You can use the Excel Automation Plugin to automate the generation of Excel spreadsheets from templates.

The actual rendering work is delegated to the Better Excel Plugin which is using actual Excel files as template, so it should be fairly straightforward to convert your Excel file to a template.

Chris Langlay-Smith March 16, 2017

Thanks for your response. The spreadsheet is a working document which needs to be updated daily. Does this solution allow that or does it require a new spreadsheet be created each time?

Joe Miller January 27, 2018

Aron,

I'm actually very interested to hear more about how this would work with Better Excel.  We own both Better Excel and Automation for Jira so I'd like to figure out how to build an automated burndown that I can email in an Excel attachment.

Aron Gombas _Midori_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 27, 2018

Joe,

You'd basically write a Groovy script that does the calculation outlined in the question:

At the moment i manually total all the issues and hours everyday using a filter which shows all issues completed that day.

As a minimum implementation, it will create a map of (day -> total of remaining hours). I think the easiest could be iterating over each issue, take its Resolution Date and the Original Estimate field value, then construct the data structure. At the end of the iteration, you will also know the total of Original Estimate (which will be the starting point of the "remaining hours" line in this model).

Then you return this to the Excel template and render it to 2 columns (1st column: date value that represents the day, 2nd column: hours as a number).

Then you can render a regular timeline chart in Excel.

When you have this working at manual export, you can automate this using a dedicated automation rule with the Send Excel action.

0 votes
Volodymyr Krupach
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 13, 2017

I guess you can automate it with bite of VB script by calling JIRA REST API, particularly search method: https://docs.atlassian.com/jira/REST/cloud/#api/2/search

TAGS
AUG Leaders

Atlassian Community Events