Fetch data from JIRA to excel

Hi. I am trying to count number of bugs which are reported every week in specific projects with JQL (I am not sure how to get count only). and then I want to get this number in excel, so that I can see how many bugs have been reported, in comparison to last week, last month, last year. And I am drawing excels graphs using that data. Excel is storing my data and drawing graphs for me but I need to have data from JIRA automatically based on filters, I apply.

So I need counts of bugs in projects (not bugs), historic data and how to fetch them directly from excel so that I don't need to go to JIRA. I trying to automate my reports. We have JIRA version 6.4.9

I hope I am able to describe my question and now waiting for response.

 

Thank you.

7 answers

You can easily and automatically import Jira Cloud data directly into Excel by following these steps:

 

1. Create a filter view in Jira with all of your desired columns, save it, and make a note of the path.  Example: https://mycompany.atlassian.net/issues/?filter=11940

2. Change the path to https://mycompany.atlassian.net/sr/jira.issueviews:searchrequest-csv-current-fields/11940/SearchRequest-11940.csv

Note that going to this URL in your browser returns a CSV file.

3. Start Excel, and select Data menu, New query, From Other Sources, From Web

4. A dialog will appear asking you for the URL. Paste in your CSV URL from step 2, and click OK

5. You will be prompted with a permissions dialog.  Select Basic authentication, enter your Jira username and password, and click Connect.

6. After Excel connects, you will see a dialog with a preview of the table.  Click Load to load it into the current worksheet.

 

From this point forward, your Jira credentials will be cached, and you can update the query at any time by clicking Data/Refresh all.

This is exactly what I needed. I had been downloading the CSV, putting it into a folder and connecting to it that way. It never occurred to me that I could directly link Power Query to JIRA through a Web data connection. Brilliant! This saves me a huge amount of time.

If you enter you credentials wrong or need to change them, it's not easy to find as you're not prompted again after entering them. Here's how to fix it. I'm using Excel 2016 (Office 365):

  1. Right click your Power Query entry in "Queries & Connections" select Edit
  2. Click "Data source settings". A list of data sources is displayed.
  3. Find the data source you want to modify and select "Edit Permissions"
  4. Under "Credentials" you can change your login type, from using Windows Credentials, to Basic (to enter your own username/password) etc.
  5. For Privacy Level, I used "Private" in my setting.

Hope that helps and thanks for the info!

The path does not seem to work with JIRA v6.4.13.  My filter is in the format: https://mycompany.atlassian.net/jira/issues/?filter=39787

would someone know the corrsponding CSV path?

I tried various choices like https://mycompany.atlassian.net/jira/sr/jira.issueviews:searchrequest-csv-current-fields/39787/SearchRequest-39787.html  but no luck.  

searchrequest-printable works but it creates 2 rows if I include the labels column so wanted to try CSV.

The URL's have not changed for me.  Not sure why your path is different. The way I determined the CSV path was by looking at the network traffic in Fiddler when I clicked the link to download a CSV of the report.  You can use the same technique.

Thanks I don't see an option to download csv files in the jira instance we have.  Perhaps it has been disabled. 

Peter,

Your URL needs to have .CSV at the end of it instead of .HTML . 

From reading other people's posts, the export to .csv option is no longer enabled by default, your company's JIRA admin would need to enable the option.

I've also tried to fetch data from the export xls. option, and it does not work.

Nope, wrong.  Export to CSV is fully enabled in all all Server versions of Jira, and still available on the last Cloud system I looked at a couple of minutes ago.

What are you looking at?

Hi Guys, Me too trying the same. But i am getting error as "The command you selected is not available from this shortcut menu." while clicking import option. But using the URL i am able to download the csv successfully.

Steps followed:

1. Create a filter view in Jira with all of your desired columns, save it, and make a note of the path.  Example: https://mycompany.atlassian.net/issues/?filter=11940

2. Change the path to https://mycompany.atlassian.net/sr/jira.issueviews:searchrequest-csv-current-fields/11940/SearchRequest-11940.csv

Note that going to this URL in your browser returns a CSV file.

3. Start Excel, and select Data menu, New query, From Other Sources, From Web

4. A dialog will appear asking you for the URL. Paste in your CSV URL from step 2, and click OK

5. You will be prompted with a permissions dialog.  Select Basic authentication, enter your Jira username and password, and click Connect.

6. After Excel connects, you will see a dialog with a preview of the table.  Click Load to load it into the current worksheet.

Please help me in this 

That's not a native Jira message, which suggests an add-on is in use.  I'd look at the docs for that (If you don't have access to something in Jira, it hides it, not blocks it after selection)

Did you find the solution to this problem? I am also running into the same error. 

I am having an issue with this as well, but I am not getting an error. My power query in excel is setup correctly and I can successfully pull the same CSV  from other URLs, but when I try to connect to Jira the data does not populate; the preview always shows up empty.

Above it was mentioned that I should not be blocked by any permissions, but I believe that there is some authentication issue at hand and I can't figure it out.

 

Below are the steps I used in detail:

 

  1. Login to jira.mycompany.com
  2. Saved a filtered list of issues within the my project (https://jira.mycompany.com/issues/?filter=12345)
  3. Selected Export dropdown menu
  4. Right-clicked on the CSV (Current fields) option and select Inspect
  5. Copied the CSV URL (https://jira.mycompany.com/sr/jira.issueviews:searchrequest-csv-current-fields/12345/SearchRequest-12345.csv)
  6. Open Excel Workbook
  7. Select the Data tab
  8. Select Get Data -> From Web
  9. Pasted the CSV URL in the Web Address box (Basic selected), clicked OK

 

From here, the preview window was showing up empty so I made sure that I had the correct permissions set by doing the following:

  1. Select Edit on the preview window
  2. Select Data source settings
  3. Select Edit Permissions
  4. Select Edit (Permissions were already set to Basic)
  5. Select Basic from the side panel on the left
  6. Entered jira.mycompany.com credentials
  7. Click Save and closed the windows, returning to the Power Query Editor
  8. Click Refresh Preview

 

Any insight on how to resolve this problem?

i'm following the same steps, but in the preview is showing HTML code instead of the CSV information, if i go to the link https://mycompany/jira/sr/jira.issueviews:searchrequest-csv-current-fields/51155/SearchRequest-51155.csv the CSV is downloaded correctly and without any issues, but when i copy that link to the power query option in excel it returns HTML code, does anyone knows how can i solve this? 

 

thank you.

Try adding the "?" to the end of your URL

The ? is at the end as you show.. 

make sure you are using:

Select: New Query->From Other Sources-> From Web

 

Then I have :

File Origin: 65001: unicide (UTF-8)

Delimiter: Comma

Date Type Detection: Based on first 200 rows

 

Hope this helps

I was having problems even getting it to download from the URL.  I went back to the app and did a right click on the download as CSV link and found that there was a limit added to the end of the URL in Jira.  I guess you can't download more than 1000 rows at a time (certainly when I tried 2000 it didn't work).

?tempMax=1000 was appended to the URL:

https://mycompany.atlassian.net/sr/jira.issueviews:searchrequest-csv-current-fields/11866/SearchRequest-11866.csv?tempMax=1000

The solution suggested at the top of this post works for me. I used basic authentication and specified my credentials. But when the excel file is shared with other team members, the import operation fails with following message in excel when the member tries to refresh the excel table : "Initialization of data source failed. Check the database server or contact your administrator. Make sure the external database is available, and then try the operation again. If you see this message again., create a new data source to connect to database." The same operation works on my machine. Please suggest possible solution.

Answer from Dave Ahlers above worked for me after I used Fiddler to get the exact query "https://MyCompany.atlassian.net/sr/jira.issueviews:searchrequest-csv-all-fields/12345/SearchRequest-12345.csv?"  _ I was then able to use the query editor to remove the columns that I did not want... Thanks so much!

I am a novice on using JIRA and have only basic understanding of Fiddler. Could you help me with a couple of hints on how you used fiddler to capture that URL, please?

Appreciate the help

Sujit

You could just enable develop console and view the console to see what the sent code is.. for instance in Chrome it's Ctrl+Shift+I to enable  - look for the Console Window

Thank you, Ken.

I had got distracted :-) when I could not make progress with this. Will try again.

0 votes
Timothy Chin Community Champion Sep 02, 2015

You'll probably be able to find some reporting add ons in the marketplace:

For example:

 

 

getIssueList from JIRA Command Line Interface (CLI) produces a CSV file that can be imported into Excel.

SQL for JIRA is a standard SQL API for reporting on JIRA with support for SQL aggregation functions like COUNT, SUM, etc. It also supports JIRA JQL, therefore you can execute:

SELECT COUNT(*) FROM ISSUES WHERE JQL = 'project=MYPROJECTID and type = Bug' 

The database SQL query above will execute the JIRA JQL query and count the number of issues returned.

You could write a small Java program to save the results in a file with CVS format, for instance.

SQL for JIRA is really powerful add-on and it would be able to provide all the data sequence at once (all the bugs for a project grouped weekly for a period of time).

SQL for JIRA  can be integrated seamlessly with Birt Reports for JIRA if you want to create a nice chart graph and display it on a JIRA Dashboard.

In this way, you would be able to see the graph up to date on JIRA itself at any time.

Use the Better Excel Plugin that makes creating this report easy:

  1. Write all issues to include in the report to a worksheet (make sure you export the project and creation date for each issue)
  2. Calculate the creation year, month, week from the creation date using Excel functions
  3. Create a pivot chart from this worksheet, using the project field on the horizontal axis, and the creation year, month, week on the other
  4. Draw a pivot chart from the pivot table

If you have the working template, you can refresh this any time using the latest issue data.

 

When I put the given path into a browser, I get the CSV file:  https://mycompany.atlassian.net/sr/jira.issueviews:searchrequest-csv-current-fields/11940/SearchRequest-11940.csv

When i set up the web query in Excel, I get HTML code in the columns and only a single row of data.

My hypothesis is that it's caused by my company's Single Sign-On. When I log in to jira, I put in my work email and it then takes me to a SSO page to re-enter my log in info. I believe that my credentials are not making it to that second sign on page or are not getting accepted. Any ideas?

Adrian, I reckon I've just bumped into the same problem. We just converted to SSO via Cloud and I now can't access these URLs from Excel anymore.

+1 on any ideas?

Like 1 person likes this

Unfortunately, I run into the same problems as Adrian Galarza. When connecting through query in Excel, I get HTML code (e.g. <!DOCTYPE.html> etc. and further down meta-names and javascript-code. In addition, no real data from JIRA is imported. Does anybody know how to configure the query correctly?

So I got it working again. The cloud APIs don’t use the SSO credentials. They still link to the old password stored on Jira servers.

Problem is if this has expired the teams can’t reset it without contact the admin - fortunately I am one. 

Either way I got it working again and now have some great looking dashboards in Excel without the need for any fancy plugins - just Jira APIs and Power Query.

Happy to share if anyone is interested.  

Like 1 person likes this

Brendan, nice job getting it working! Please do share how you did it. (I'm not a programmer, but might be able to get some help here at work.)

we finally got it up and running. There is a strong need to reset the access to login-type=standard (not windows!!!) and set username/password. Data then was importing correctly to PowerQuery in Excel.

The first row has to be configured as header-row and several headers had been modified. In addition, the type of some attributes had been adapted (e.g. original estimate, time spent and remaining estimate to type=number, etc.).

Unfortunately, times are not available in detail but consolidated per issue. This is not useful for our needs. Does anybody know how to get a detailed list for reported hours per user, per day etc.?

Hi all,

A lot of comments bout using CSV. I've actually followed very similar step but am using the APIs instead. MUCCCHHHH better. The data is much richer and you don't run into troubles when importing fields that are multi-select. In CSV, multi-select field come up in separate columns which you might not be able to predict so you transform logic become fragile.

Here's what I did... very similiar to the CSV version.

Importing into Excel Using Jira APIs

  1. Create a filter, or JQL you want to use to establish your data set, and make a note of it. Example: "project=DIG and issuetype=Bug" -> https://mycompany.atlassian.net/issues/?jql=project%3DDIG%20and%20issuetype%3DBug
  2. You then want to use the search API to retrieve all the issues and their meta data. I also advise you limit the query to the columns you want, not everything to limit the hit on the database, noting that the Cloud APIs will only return 100 records at a time - don't worry though you can appen them all in Power Query. Example API call: https://mycompany.atlassian.net/rest/api/3/search?jql=project%3DDIG%20and%20issuetype%3DBug&fields=issueType,summary,assignee,status,created&maxResults=100&startAt=0)
  3. Start Excel and the Power Query editor. This is different in different versions of Excel, but you need Excel 2016 or above I believe.
  4. You need to create a function in Power Query to load the first 100 rows and then call this function multiple times in order to get all your data. I haven't bothered figuring out how to set this up without hard coding, but I'm sure a techie could get onto that - I'm just a dumb project manager! ;) You'll need to add these using the Advanced Query editor initially. You can follow the wizards but this blog would be too long if I tried to explain that. :/
  5. Here's a copy of the function I set up: Query: LoadJiraIssuesFn
let LoadJiraIssuesFn = (startAt as number) =>
    let
        Source = Json.Document(Web.Contents("https://mycompany.atlassian.net/rest/api/3/search?jql=project%3DDIG%20and%20issuetype%3DBug&fields=issueType,summary,assignee,status,created&maxResults=100&"&Text.From(startAt))),
        issues = Source[issues],
        #"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key", "fields"}, {"key", "fields"})
    in
        #"Expanded Column1"
in LoadJiraIssuesFn
  1. And here's a copy of the query to get all the data out: Query: LoadAllJiraIssues
let
// load all the records (this is will only load 200 records unless I make the call a few more times)
    Source = Table.Combine({#"LoadJiraIssues"(0),#"LoadJiraIssues"(100)}),
    #"Filtered Rows" = Table.SelectRows(Source, each true),
// Expand out the results to get the data
    #"Expanded fields1" = Table.ExpandRecordColumn(#"Filtered Rows", "fields", {"summary", "assignee", "created", "status"}, {"fields.summary", "fields.assignee", "fields.created", "fields.status"}),
// Expand out some of the fields to get display names
    #"Expanded fields.assignee" = Table.ExpandRecordColumn(#"Expanded fields1", "fields.assignee", {"displayName"}, {"fields.assignee.displayName"}),
    #"Expanded fields.status" = Table.ExpandRecordColumn(#"Expanded fields.assignee", "fields.status", {"name"}, {"fields.status.name"}),
// Rename the column because I'm OCD
    #"Renamed Columns" = Table.RenameColumns(#"Expanded fields.status",{{"fields.summary", "Summary"}, {"fields.assignee.displayName", "Assignee"}, {"fields.created", "Created"}, {"fields.status.name", "Status"}, {"key", "Jira Id"}})
in
    #"Renamed Columns"
  1. When you run the query, if you haven't already, you'll be prompted with a permissions diaglogue (much like the CSV version shown in other people's responses). Select Basic authentication, enter your Jira username and password, and click Connect.
  2. After Excel connects, you will see a dialog with a preview of the table.  Click Load to load it into the current worksheet. From this point forward, your Jira credentials will be cached, and you can update the query at any time by clicking Data/Refresh all.

(Thanks Dave Ahlers for the CSV tips above - I unashamedly based this on yours! Thanks mate - this really helped!)


Some other things to note:

  • I've done this with the Cloud APIs, so am just assuming it works the same for Server version. I'm not a Jira guru... yet. :)
  • If you've just switch over to Atlassian Access for SSO, then you'll need to realise that the APIs (and CSV) imports don't authenticate against the SSO. Not sure why, and I'm no way an expert in this area - like I said, a dumb project manager - so best to talk to your security gurus about that kind of thing. :) To authenticate, you'll need to use old passwords, as stored on the Atlassian servers. If the people using your spreadsheet can't remember the password then an admin will have to reset it for them.

With this method I've been able to produce reports like this in Excel really quickly that also update with the press of a button. They look much nicer than what I can do in Confluence and no plug-ins required.

Capture.JPG

Like 1 person likes this

Thank you for sharing, @Brendan Walker

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Jan 08, 2019 in Jira

How to Jira for designers

I’m a designer on the Jira team. For a long time, I’ve fielded questions from other designers about how they should be using Jira Software with their design team. I’ve also heard feedback from other ...

1,098 views 4 9
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you