Connect JIRA to Google Sheets via JIRA API

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?  smile 

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 

5 answers

1 accepted

@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!

 

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"

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.

@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?

@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.

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

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

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.

 

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).
1 vote
Pedro Cora Atlassian Team Apr 29, 2015

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.

wink

-- Pedro

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%.

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 Community Champion Jun 28, 2017

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?

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 ...

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. 

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,313 views 14 20
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot