nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Jon Frampton

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

Google Sheets - Script Editor.JPG

Google Sheets - Menu Bar.JPG

Tanguy Crusson

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!

All comments

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.

wink

-- Pedro

Midori

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

Use the Better Excel Plugin to generate custom XLSX format exports from JIRA. (customization tutorial)
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)
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).

Jon Frampton

Google Sheets - Script Editor.JPG

Google Sheets - Menu Bar.JPG

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

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?

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

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

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.

Jon Lee_vkr4ma

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

Tanguy Crusson

We hope that helps!

unknown

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?

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 ?

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

unknown

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-development