Connect JIRA to Google Sheets via JIRA API

[Jon Frampton]

I would like the ability to update (pull) via the JIRA API, a Google Sheets spreadsheet with JIRA data using predefined JQL queries, from our self-hosted instance of JIRA.  

Has anyone done this?  If yes, are you willing to share your brilliance with me and help me to understand how this was accomplished?   

Is there a Plugin out there that I have somehow missed in all of my searching of the Marketplace?

All of the search engine queries return results that seem to be missing a little bit of information that would help bridge the gap between my failure to accomplish this functionality and ultimate blissful success! 

I appreciate any and all help any and all of you are willing to provide!

Cheers, 

JF 

[Jon Frampton · Accepted solution]

@Ferenc Kiss [Midori],

Thank you for the response.  This is a pretty cool approach, thank you for taking the time to share your response.  I will probably use your suggestion for a different situation.

However it doesn't quite work for what I am trying to do as I want to update the same Google Sheet over and over.  

I found a tutorial at www.LittleBlueMonkey.com which solved my problem.  It makes use of the JIRA REST API to import a backlog of all JIRA issues.  I tweaked the code a bit to make use of a Search Issues Filter to pull in the issues based upon a FixVersion.  But you can choose whatever predefined Issue Search just by clicking on the URL after executing the search. (Instructions can been found within the Google Sheets Template on LittleBlueMonkey.com) 

By using the Google Scripts Tool (Browse to Tools -> Script Editor) you can QUICKLY & EASILY program a connection between the two systems. 

Google Sheets - Script Editor.JPG

 It will add Custom Menu items within Google Sheets:

Google Sheets - Menu Bar.JPG 

The directions provided are for a Cloud instance of the software, but I was able to get it to work with our hosted instance, with little problem.  (Change the HTTPS within the sample code if your JIRA instance isn't HTTPS)

Again, to view the tutorial go to the following URL: http://www.littlebluemonkey.com/blog/automatically-import-jira-backlog-into-google-spreadsheet

If you find this useful you may want to consider donating to the developer - this is a huge time saver and worth a few hours of development time.  All kudos should go to the developer!

 

[ShivasaiS]

Hi Jon, My ResourceURL http://jiraserver/rest/api/2/filter/favorite works only on my private network. I want to make this URL public any idea how to make it public. I am facing the same error on littlebluemonkey.com " SyntaxError: Emptry JSON String"

[Jon Frampton]

I think it is a setting within JIRA that allows public access but I am not sure. I am not a developer so I don't know what that JSON String error means, sorry.

[Sree Putturi]

@Jon Frampton - can you share details on how you tweaked to use a jql filter instead of a project to pull backlog into the spreadsheet?

[Jon Frampton]

@Sree Putturi, What I used was a FixVersion for the backlog. So when I did the JQL within JIRA it was FixVersion="FixVersion" name. I then copied that entire JQL URL string into the code at the part where this is located: while (data.startAt + data.maxResults < data.total) { Logger.log("Making request for %s entries", C_MAX_RESULTS); It is important to remember that I am not a developer/programmer so I may not be answering your question the way you would like it to be answered. If what I shared makes sense to you and you have additional questions around the JQL statement that I wrote in JIRA please let me know.

[Sree Putturi]

This was very helpful and i made a donation as well :). Thanks for adding the bloggers details to your post

[Jon Frampton]

@Sree Putturi :-) Glad it helped you out. Cheers!

[miguel gonzalez]

Hello. I have followed the instructions but is isn't working for me. I get an error from the sheet that says... 'Address unavailable: https://jira.companyname.com/rest/api/2/field' but when I copy that address (with the correct company's name:-)) the API shows. I guess there are some security issue. Do I need to do some kind of access authorisation?

I will appreciate any idea you may have. Thanks.

[Tanguy Crusson · Accepted solution]

Hi all, if you are using Jira Cloud (so not Jira Server, the hosted version of Jira), you can also use this Google Sheets add-on: https://community.atlassian.com/t5/Agile-articles/Try-the-new-Jira-Cloud-add-on-for-Google-Sheets/ba-p/1016436

We hope that helps!

[Deleted user]

This is great.  The documentation for the plugin, however, is a bit limited.

I have started using the JIRA() formula in GSheets, and so far have been loving the results - but there isn't sufficient information about how to pull in all the different types of fields.

(If anyone is reading this, the following link is a good starting point...
https://confluence.atlassian.com/jirasoftwarecloud/advanced-searching-fields-reference-764478339.html)

I am currently trying to get the Sprints field to return only a list of the sprint numbers, rather than every bit of metadata about every sprint a ticket has been a part of.  Any ideas?

[Tanguy Crusson]

@[deleted] my apologies I just saw your reply! 

Yes, the documentation is currently a bit light, we're currently creating a sample spreadsheet with the most popular use cases to make it easier to discover. 

We don't currently handle some fields really well, for example sprints, but are working on it. for sprints, today you'd need to do some string manipulation to extract the sprint names or numbers. 

=iferror(right(index(split(<cell reference>, ",", TRUE,TRUE), 0,4),LEN(index(split(<cell reference>, ",", TRUE,TRUE), 0, 4))-5),)

 I know, not ideal :-) 

Here's a ticket to track for the proper resolution: https://jira.atlassian.com/browse/API-24

[Deleted user]

Thanks Tanguy for the suggestion with the string manipulation.

This is a great plugin and has already helped considerably.  Some documentation would help immensely.

Longer term:
It will be even more amazing if we could extract data from sprint reports etc.  Right now I have to separately compare the data from sprint reports (which keeps a snapshot of reality at each sprint), and compare that to the overall list of issues in order to track burndown.

Best regards,
S

[Tanguy Crusson]

Thank you @[deleted]that is great feedback. Currently we're focusing on JQL and we already have miles to go to make the solution deal with the wide variety of Jira fields, but we're noting your idea of sprint reporting.

By the way we're creating a spreadsheet that shows various scenarios for how to use the JIRA() function (which might be easier to keep accurate than documentation). What do you think is missing there for your needs? 

https://docs.google.com/spreadsheets/d/11jc9CPnBt1KWhRlaO5nnNSFkV5LR3lFjMV7_i6uMB_k/edit?usp=sharing 

[Deleted user]

That is great!  Thanks!

[matt lavinia]

@Tanguy Crusson Do you see this being able to work with Parent Link fields?

Example: I am project managing a project - this is entered in JIRA as an initiative. I have N Epics on that Initiative, then each Epic has Z stories. 

If I am program managing this - I would like a quick sheet that does roll up views of the Initiative, then the Epics, and finally the Stories that make that up, with Sprints and Status

[Tanguy Crusson]

@matt lavinia it makes complete sense. We've added this to the backlog. Here's a ticket to watch: https://jira.atlassian.com/browse/API-123

[Pedro Cora]

Jon,

You could use the Script Runner Add-On (https://marketplace.atlassian.com/plugins/com.onresolve.jira.groovy.groovyrunner) to call a script on an event that  can be triggered by a workflow post-function. Then in the script you can send the REST calls that you want to update your spreadsheet.

-- Pedro

[Jon Frampton]

Thank You Pedro Cora , I will check this out for other functionality it may help with. See below for my response to Ferenc Kiss [Midori], as that resolution solved my problem 100%.

[Midori]

This is more like a push type of approach, but this would also work:

1. Use the Better Excel Plugin to generate custom XLSX format exports from JIRA. (customization tutorial)
2. Use the Excel Automation Plugin to automate exports, either triggered by some issue create/update/transiton or by a CRON schedule. Save the resulted file to the filesystem. (automation tutorial)
3. Upload the XLSX from the filesystem to Google Drive and tell it to convert to a Google Spreadsheet (use the convert parameter). Automate this step with an OS job (cron).

[Jens Rosemeier]

Hi Jon,

even this is an old post, i had same/similar need of getting some updates and jira details into google sheets without manually copy&pasting them.

So one day i decided to start building a Add-on myself.
I think, if you still look for some of this functionality, my Add-On for Google Sheet would help you.

Its available in the Google Sheet Add-on store for free.
Just look for "Jira Sheet Tool".

Details, readme, support and even contribution to that plugin can be found at github: https://github.com/ljay79/jira-tools/

[Volodymyr Krupach]

Hi Jens,

Thnak you for contributing and sharing the plugin! Unforutnately I do not see "Jira Sheet Tool" on the marketplace. Is it public and approved by Atlassian?

[Jens Rosemeier]

Oh no sry if i didnt clearyfied, this is a Add-On for Google Sheet enabling you to "pull" jira data via JIRA Restapi.

You can find it in the Google Docs Addon Store.
Open a new Google Sheet, then menu: Add-Ons -> Get add-ons ...

[Jon Lee]

Getting a "Could not connect to Jira Server![500]" on a private JIRA server.  Is this script only for the cloud instances?

[Jens Rosemeier]

Hi Jon.

This add on works with both server and cloud.

Error 500 seems not to be auth related but more like a wrong url. Or your private server isn't reachable from the internet.

If you have trouble with the addons pls feel free to open an issue on the github page. You find it in the addons help and about menu.

[Abhaya K Premawardena]

I am sorry by I get a security error when I try this . Googles sheets says it is not a verified application and it does not work . is there a fix for it ?

[Marie-Helene Larouche]

Follow the instructions on this https://docs.google.com/spreadsheets/d/1c_FxfkIXtj-QOYfQmvxBMZMjIM-JpdHvuD4jJ4Gobxk/edit#gid=4

Then, prior to anything, you "insert" filter you've pre-configured in Jira.