Importing JIRA data to Google Apps

Executive Summary

If you are unhappy with the way that data is displayed in the JIRA issue navigator, or when exported to Excel, you might want to try using Google Apps to display the data.

Then, you can do fancy formatting such as:

  • setting font size
  • setting font color
  • conditional formatting
  • setting column widths, etc.

…to display the data more nicely.  You simply set up a search filter in JIRA, then import the XML feed to a Google Spreadsheet.

What does it look like?

You will be able to access your nicely displayed data using a URL such as:

http://spreadsheets.google.com/pub?key=x1-XXzAqwJXXXXXCRaE6g&single=true&gid=0&output=html

Your Google spreadsheet  will look something like this:

crn-html

How do I do it?

JIRA side

1) Set up your JIRA search filter for the data you want to display.  You don’t need to worry about the navigator columns, as all data will exist in the XML feed.

2) Make sure JIRA allows data to be exported as XML (this can be set in JIRA adminstration section).  If you can see an ‘XML’ link above your issue navigator, then you are good to go.

3) Copy the XML URL.  Example:

https://jira.company.com/jira/sr/jira.issueviews:searchrequest-xml/11940/SearchRequest-11940.xml?tempMax=1000

Google Apps Side

4) Open a Google Apps account and create a new spreadsheet.

5) Use the importXML command to fetch your JIRA data.  Example:

importxml

=importXML("https://jira.company.com/jira/sr/jira.issueviews:searchrequest-xml/11462/SearchRequest-11462.xml?os_username=rocky&os_password=balboa","/rss/channel/item/status")
  • Change the blue section with your XML feed that you copied in step 3.
  • Change the red section with your JIRA login details (see limitations and problems section below for an important warning!)
  • Change the green section with the correct xpath command to fetch the selected data value from your XML.  (You can fetch any data in the XML feed, issuekey, summary, even dates and customfields.)

6) It will now fetch the data and display like this:

imported-data

Note: it is colorful because I applied conditional formatting to the column already.

7) Continue adding all the desired columns and formatting, using xpath to fetch the data.

Note that customfields are tricky – to fetch one use this xpath command:

/rss/channel/item/customfields/customfield[@id='customfield_10994']/customfieldvalues/customfieldvalue

8.) Once your spreadsheet is complete, click ‘Share’ button from top right and then ‘publish as web page’.  You can then get a URL for a static, non-editable HTML version of the spreadsheet:

publish

PDF, Atom, CSV and other file formats are also available in addition to HTML.

9) You can now give the URL for the HTML version to your colleague to let them see the data in a nicely formatted, read only, easy to print way.

Limitations and Problems

1) A huge problem was that the source data is shown automatically at the bottom of the HTML page.  This is a problem because the XML feed contains your JIRA login details.

oh-crap

I could not figure how to turn this off in Google Apps, but I thought of two ways around it:

a) Create a dummy login with limited power so that if someone sees the login and figures out it can be used to login to JIRA, then they can only do limited damage.

b) Use a kind of proxy URL to hide the login details.  e.g. (intranet.company.com/getJiraXML.php?id=100024) will fetch the XML for search filter 10024.

2) Google currently only allows 50 importXML statements.  I’m not sure how this is calculated (my sheet only had about 20 when I hit the limit), however it meant I was only able to import about 20 columns and 300 rows.  There may be ways around this by using another spreadsheet(s) to import the data, then a master one that combines the data from these sheets.

3) Google automatically converted some of my data to Date format.

e.g. Cell value “3/4, 1” gets converted to “3/4/2001” automatically.

In excel you can set the cell format (text, decimal, date format etc) easily, but I couldn’t see how to do this in Google Apps.  Probably you have to wrap a function round the data to convert to a specific format (or turn off auto-formatting).

Aside:

Microsoft Excel from version 2003 up can also import data from an XML feed, however I was unable to import the live XML feed due to digital certificate problem that I was unable to resolve.  I also tried import the XML using a local file, but then the data was displayed illogically and I couldn’t figure out easily how to reformat it.  Google Apps however uses xpath which is much more easy to use.

Farewell

Hope you found this post useful.  If you can solve any of the problems I encountered above, I would love to hear your comment!

Advertisements

3 thoughts on “Importing JIRA data to Google Apps

  1. This was extremely helpful. However, I found that if I updated the details in JIRA, the google doc is not updated. Even when the xml feed shows the changes. Is there a way for the spreadsheet to be updated as Jira changes, or is this just a static document that needs to be made everytime I want to report on the changes?

    • I am not sure how often google checks the XML feed and applies the changes. Perhaps it only does it when opening the document?

      You would have to test it yourself or ask a question on the google group for google docs, as I don’t know the answer to this question.

Comments are closed.