Getting data from JIRA into Google Docs

April 20, 2010 at 8:09 am | Posted in Programming | 12 Comments

In most of my projects we use JIRA for tracking bugs and user stories. For weekly progress reporting to our customers I often have to extract data from JIRA. This can become a boring task if you have to copy and paste this data to Excel or OpenOffice. Luckily JIRA offers a set of web services that allows you to get almost all relevant data. In Excel it is possible to access SOAP web services. However this is not supported on the OS-X version of Excel. In OpenOffice this might be possible as well but I haven’t investigated that yet.

Recently I began exploring the possibilities of moving my documents on-line, using Google Docs. The functionality of Google Docs seems a bit limited sometimes (try conditional formatting with formulas in a spreadsheet for example!) but luckily there is a scripting interface. My first experiment involved retrieving JIRA data and importing it into a Google Docs spreadsheet. The JavaScript code below retrieves the names of all supported operations and inserts them into the current open sheet:

function getJIRA() {
  var wsdl = SoapService.wsdl("https://intranet.xebia.com/jira/rpc/soap/jirasoapservice-v2?wsdl");
  var serviceNames = wsdl.getServiceNames();
  var service = wsdl.getService(serviceNames[0]);

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var operations = service.getOperationNames();
  var length = operations.length;

  // Dump service name and number of operations
  sheet.getRange("B1").setValue("service: " + serviceNames[0]);
  sheet.getRange("B2").setValue("# operations: " + length);

  // Loop through the operation names
  for (var i = 0; i < length; i++) {
    var cell = sheet.getRange("C" + (i + 1));
    cell.setBackground((i % 2) ? "lightgrey" : "white");
    cell.setValue(operations[i]);
  }
}

In line 2 you have to fill in the url of your own JIRA instance. A screenshot of the resulting spreadsheet:

Google Docs spreadsheet with JIRA operations

My next experiments will involve calling these operations and actually retrieving some data from JIRA.

12 Comments »

RSS feed for comments on this post. TrackBack URI

  1. What I am looking for is a plugin in either google docs or google wave that can issue a query to jira and lists the result in a table. We actually modified a mediawiki plugin to just do that and use mediawiki to create reports with tables include in it. The reason we like mediawiki is that several people can add content in parallel in different sections. Now if we can do that in google docs or wave, we could use them instead of mediawiki.

    what we have in mediawiki is something like

    (summary ~ Documentation) OR (comment ~ Documentation)

    This would be real useful for the Google web 2.0 way.

    If anyone has done this, or plans to do this, please send me mail.

    • Both Google docs and Google Wave are straightforward to use in combination with JIRA. As you can see in this blog it’s easy to use JavaScript for this when you are using Google docs. For the moment that only holds for the spreadsheet, since the Google docs word processor doesn’t have a scripting interface yet.

      Google wave can be used with any language that runs on a JVM. I’ve written small POC’s for example in Scala. JIRA has a great client API in Java, so you don’t have to deal with webservices. Writing a Google Wave Robot that handles JIRA queries is easy.

      If you need any help with this you can contact me directly.

  2. Yes you are right it is possible to do so. What we were hoping for is a ready available solution though😉

  3. hey, did you have any luck getting any issue or time tracking related data out of jira? i’m interested in doing some pivot table type stuff in either google docs or excel to custom report on issues and time tracking etc and if you have a code sample to get data out, i’d really appreciate it

    thanks

    Andy

    • Hi Andy, I haven’t tried that yet, but it shouldn’t be too difficult I guess. I’ll try to come up with an example and write a short blog about it. If you already tried something yourself and are running into problems, please let me know.

  4. Hi,
    So where do i put that script so i could see the data in gdocs? Sorry for asking this. I’m no familiar with the procedure but want to know so i can use it on my project.

    Thanks,
    gene

    • When you open a new or existing spreadsheet in Google docs you will see the “Tools/Scripts/Scripts Editor” menu. Use this editor to add new scripts to your document.

      • Can I actually pull the tickets, particularly the resolved tickets to googledocs so i can prepare a fast status report? I dont know how to do the script.
        Please help me. thanks in advance.

  5. We did a prototype that donwloads the google doc, replaces the jira tickets with links and uploads a new document that is just viewed.

    Its not the solution we were loking for, but workes somewhat if you do not use much of the formatting features.

    I wrote someone in google, but I am not sure if they will do something so we can integrate an API in googledocs for documents.

    We have a complete solution for this in mediawiki, so we create our reports with mediawiki. Naturally confulence would also work.

  6. […] posts about getting data from JIRA. In the first one I described how you can do that in Ruby. In the other one I used JavaScript to import JIRA data directly into Google Documents. Both methods worked and both […]

  7. Simply want to say your article is as amazing.
    The clearness in your post is just nice and i can assume you’re an expert on this subject.
    Fine with your permission let me to grab your
    feed to keep updated with forthcoming post. Thanks a million and
    please keep up the gratifying work.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: