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

This widget could not be displayed.

@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 widget could not be displayed.

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).
This widget could not be displayed.
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%.

This widget could not be displayed.

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/

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

Jon Lee I'm New Here May 31, 2018

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

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.

This widget could not be displayed.

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 Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

262 views 5 0
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