Getting data from JIRA into Google Docs

April 20, 2010 at 8:09 am | Posted in Programming | 11 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.

About these ads