EricShiflet.com

Using Google Sheets to access the Jira REST API


I started using Atlassian's Jira a couple years ago after working with Rally for several years before that. There's a lot of data that both tools store in their database that is only accessible through the REST API, not through the web application. If you want to use this data, it's certainly possible to write your own application, though sometimes you just want a quick way to get the data out and run a few operations on it like aggregation, averaging and comparing. Spreadsheets are perfect for this type of work, but how do you get the data out of Jira and into a spreadsheet?


There are many ways in both Jira and Rally to search for the data you want, export it to an excel format file, then import it into a spreadsheet. This is a bit cumbersome and it doesn't automatically update, so it would be nice to automate the process. It turns out that Google Sheets supports running scripts, and one already exists for importing data from a generic REST API. It's called ImportJSON.gs and is available on GitHub here. And here's how to use it, from the same author.


Caveat emptor, there's one trick necessary to make this work with Jira: authentication. Obviously, Atlassian wants to make sure you really have the right to access the data this Google Sheet is going to get through the Jira REST API. We'll be securely passing, via SSL, your base64 encoded user credentials using Basic Auth by extending the script referenced above. Unfortunately, I haven't figured out a way to do this other than storing your base64 encoded credentials in the Google Sheet. This isn't great because if you share the Google Sheet and allow editing, or your Google account gets hacked, your credentials could be found and decoded into your Jira username and password. But if your Google account gets hacked, Jira credentials are probably the least of your worries. One more note, I've only used this with Jira Cloud and don't know if it would work with the on-premises product.


So how do you do this? Follow these steps.


  1. Create a Google Sheet document.
  2. Follow the steps at the URL above to copy and paste the script into your Google Sheet.
  3. Modify the ImportJSON function interface in the script to include passing your base64 encoded credentials, near line 90: 
    function ImportJSON(url, query, options, base64pw) {
  return ImportJSONAdvanced(url, query, options, base64pw, includeXPath_, defaultTransform_);
}
  4. Modify the ImportJSONAdvanced function to make the Jira REST API call using the base64pw parameter for Basic Auth in the Header, near line 130: 
    function ImportJSONAdvanced(url, query, options, base64pw, includeFunc, transformFunc) {

  // adding options to the API call, including headers and basic authorization
  var urlCallHeaders = {
    'Authorization': 'Basic '.concat(base64pw)
  };

  var urlCallOptions = {
    'headers': urlCallHeaders
  };

  var jsondata = UrlFetchApp.fetch(url, urlCallOptions);
  var object   = JSON.parse(jsondata.getContentText());
  
  return parseJSONObject_(object, query, options, includeFunc, transformFunc);
}
  5. Calculate your base64 encoded username:password from a site such as this one. Make sure you encode your full username and password joined with a colon. The result should look something like ZABjq4mYl4IqYISeh48ebq5jw301RPfia4VsUze=. There's a good chance you'll want to share the Google Sheet. That's fine because rows and columns can be hidden, which is what should be done with the base64 encoded credentials. Even though it isn't your username and password, it can still be used to access your account through Jira's REST API. To keep others from unhiding the cell, only share the Google Sheet as read-only.
  6. Set up the Google Sheet with the four parameters such as this:
    7. A B
    1 URL https://your_instance_name.atlassian.net/rest/api/2/issue/FS-2345
    2 Return Filter /key
    3 Options noInherit, noTruncate
    4 Base64 auth ZABjq4mYl4IqYISeh48ebq5jw301RPfia4VsUze=
  7. Call the ImportJSON function by entering this in a cell =ImportJSON(URL, return filter, options, optional base64 pw) where each of the four parameters is the cell where its value is defined. In the table above, it would be: =ImportJSON(B1, B2, B3, B4)

Well, that's a lot of fun, but what can you do with it? The real power lies in the URL, the Return Filter, and the script's ability to return multiple values if there are multiple matches for the Return Filter. For example, the URL in the table above will return the JSON representation for the issue FS-2345, and the /key Return Filter will extract only the key value, which is FS-2345. Not too interesting. To get Story Points, which is a little more interesting, change the Return Filter to /fields/customfield_10004. For whatever reason, Atlassian decided that Story Points are a custom field and it's ID (at least in my Jira instance) is 10004. (If you want to get all the custom field ID's, use this endpoint: https://your_instance_name.atlassian.net/rest/api/2/field).


Now let's do something you truly can't do within the Jira application: Aggregate the number of Story Points for an arbitrary search query that returns multiple issues. First, construct the URL. To search for all the Stories that were completed in a particular Sprint, construct a URL similar to this: https://your_instance_name.atlassian.net/rest/api/2/search?maxResults=300&jql=status in (done%2C verified) AND type %3D Story AND sprint %3D 165. The criteria are that the Sprint is ID number 165 (I'm assuming that if you're savvy enough to get this far, you understand how to get the index of a sprint in Jira), only Stories, and a Story is complete if it is in the Done or Verified state. Obviously, you can make this even more useful by filtering for criteria such as Epic parent or specific scrum team. See the Jira REST API doc to get some ideas.


If everything is working correctly, Jira returns a JSON list of all the Stories completed in Sprint 165. However, the Return Filter must be modified because the JSON data returned is a list, not a single element. Change the Return Filter to /issues/fields/customfield_10004. The great thing about the ImportJSON script is that it will iterate on all elements in the list! Under the Google Sheet cell holding the call to ImportJSON, it should now list the Story Point value for every issue in the list. Something like this:


Issues Fields Customfield 10004
1
0.5
3
2
etc.

Within the spreadsheet you can now implement any arithmetic or other function you would normally use, such as summation, averaging or graphing. This is great because it's significantly more powerful and flexible than what you can do in the Jira web app, and it's better than simply exporting the static data from Jira because it will automatically update every time you load the spreadsheet.