It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Try the new Jira Cloud add-on for Google Sheets

Try the new Jira Cloud add-on for Google Sheets

Hi Atlassian community,

I work on the product integration team at Atlassian and I am very excited to announce our new Jira Cloud add-on for Google Sheets which gives any Jira user the power of JQL in Google Sheets. The add-on is available for free on the G-Suite marketplace and can be installed by any Jira Cloud user.

Install the add-on from the Google Suite Marketplace.

Over the past months, we collected feedback from early users. Here are some cool use cases from users:

  1. Create a weekly stakeholder report to estimating the launch date of a project.

  2. Analyze the velocity of a development team.

  3. Estimate the quality of shipped code via bug re-open rates.

  4. Review the budget of a project by merging data from Jira and other systems in GSheets.

We are excited to hear what you will do with the add-on, simply comment with your experience on this post or leave feedback in-product. Not using Google Sheet? Stay tuned for an integration with Office 365 in the coming months.

What can I do with this today?

You can import Jira issue data from any number of Jira sites into a spreadsheet using either your saved filters or a custom JQL query.

Here are the ways to import Jira data in a spreadsheet:

1. Using a JQL query from the add-on’s side panel

ff696bce-c9da-49bd-a3fa-e78238e580d5.png

The add-on will run the JQL query and return all Jira issues matching the JQL specified, using your default columns, as specified in Jira.

To change your default columns in Jira:

24fc149e-2476-4a01-88ae-f9b60e02c286.png

2. Using a Jira filter from the add-on’s side panel

Simply select a Jira filter to import from the side panel, the import will display the columns specified in the filter.

Note: the filter must be starred in Jira for it to appear in the list of filters you can select in the add-on.

4cd288a2-fb23-4d91-8143-c80e7ced624d.png

If it’s your first time creating a filter in Jira, we suggest you have a look at this very handy article from our friends over at Zapier: How to create a filter in Jira.

3. Using the JIRA() spreadsheet function

Alternatively, the add-on provides a function which gives you the option to specify the list of Jira fields you want the query to return, as well as the number of rows. You can also use it to paginate through a large list of issues.

GIF_VSCODE_COMPRESSED.gif

Using the Jira function gives you a powerful way to return Jira data in Sheets

Before you use it to make sure to enable the function using the toggle in the add-on’s side panel:

a6d2ef1f-8e89-4915-82ad-d13f031e71b9.png

 

Using the JIRA() spreadsheet function you can do more advanced things like refreshing the data periodically, returning more complex fields or returning a table without a header. In order to help you use it we’ve created this sample spreadsheet.

Here are a few examples of what you can do:

table.jpg

What are the planned improvements?

We’ve received quite a lot of feedback since the early access program. We’ve already addressed a few and we’re currently working on the following features (along with their Jira issue key so you can follow their resolution):

  • A way to easily see which fields are available in a Jira site, and which ones are supported/not supported by the add-on (API-81)

  • A way to refresh the spreadsheet data at the click of a button (API-52)

We’re also looking into the feasibility of the following feature requests:

  • A way to refresh the spreadsheet data based on a schedule (API-54)

  • A way to query the worklog (API-85)

  • A way to bulk create Jira issues from a spreadsheet (API-86)

  • A way to import issues in a spreadsheet, make changes and push them back to Jira (API-87)

Questions? We have answers!

How do I install this?

This add-on only works with Jira Cloud, and does not yet support Jira Server, the on-premise version of Jira.

Using this is pretty simple and does not require any help from your friendly Jira administrator. From any Google Sheet:

  • Click on Add-ons > Get new add-ons

  • Search for “Jira Cloud for Sheets

  • Click the “+Free” button and follow the prompts

G Suite administrators might need to enable access to Google spreadsheets add-ons for all users. Read more

Once enabled on G Suite, follow the steps below to install the add-on:

  • Go to the G Suite Marketplace

  • Search for “Jira Cloud for Sheets

  • Click Install and follow the prompts

Note that we only support Jira Cloud today, if you are on Jira Server or Data Center you'll need to use one of the Google Sheets or Excel apps available in the Atlassian Marketplace.

We've done a lot of work to test this, but since this is the first version we put out in the wild you might hit a couple of rough spots. If so let us know and we'll fix this for you!

Where can I find the documentation and FAQ?

You’ll find it here on confluence.atlassian.com

I have an issue, how can I get help?

Please get in touch with Atlassian Support, we’re here to help.

I've got suggestions or questions, can I give you feedback?

Yes, please! We have plans for additional features, and we'd love to hear from you how we can make the add-on even better.

f1410bac-c2d3-4534-af7d-99ab90d9f0e0.png

You can send us feedback using the “Jira Cloud for Sheets” side panel

If you tick the "Yes, you may contact me about this feedback" box we might get in touch to understand your needs a bit more. Help us shape the future of this add-on!

I've tried it and I love it!

Nice! The team would love to hear that their hard work has paid off: if you could drop us a comment in the feedback form we'd really appreciate it, that's what keeps us going! If you would like to give us a little quote we could use when we announce the feature more widely we'd be eternally grateful.

 

48 comments

Jodi LeBlanc Community Leader Feb 22, 2019

This post will make a lot of Jira users happy! Thanks for sharing :)

Like # people like this
Jens Rosemeier Feb 26, 2019 • edited

Very interesting to see.

So i can soon stop further development on the already available OpenSource Google Add-On:
"Project Aid for Jira" to be found in Google Marketplace and on github ?  :)

Like Bianca Encinas likes this
Tanguy Crusson Atlassian Team Feb 27, 2019

Hi @Jens Rosemeier ,

We’ve had many requests from Jira users for an integration that is officially supported for Google Sheets. I realize now that we should have gotten in touch earlier to share our plans. I do apologize for the lack of communication on our part so far. You’ve built a great add-on, as shown by the 50k+ users who have installed it!

To answer your question: your add-on supports Jira Cloud and Server and our add-on only supports Jira Cloud. We don’t currently have plans to make it work with Jira Server or Data Center, however we can’t really make promises that we won’t go there longer term, it will depend on user demand and technical feasibility. Your add-on also supports more features than ours does, and if you want we can share more details about our roadmap so you can best decide next steps for your project. Feel free to reach out via email (I just sent you one).

Cheers,
Tanguy

Like Bianca Encinas likes this
Damon Gaylor Feb 28, 2019

I can't seem to get the JIRA() function to enable although I'm the document owner of the sheet and also an admin user.  The default query displays fine, I just want to do advanced query to get different columns.

Like Bianca Encinas likes this
Tanguy Crusson Atlassian Team Feb 28, 2019

@Damon Gaylor yes that is a known issue.

We're currently testing a fix for it. You can track the status of the resolution here: https://jira.atlassian.com/projects/API/issues/API-11 

Like # people like this
Paul Volk Feb 28, 2019

Has anyone figured out how to request a custom field in the JIRA() function? 

I tried the approach below with just adding the custom field ID (in bold) as a column:

=JIRA("project = ABC", "issuekey,issuetype,components,summary,10026")

 The result of the above was "Error: Field "10026" is not known (line 1827)." So my assumption is there is a prefix or format to this. I tried a bunch of variants but couldn't figure it out. Can anyone point me to documentation on this or just write the solution in here?

 

Thanks in advance!

Like # people like this
Donovan Kolbly Atlassian Team Feb 28, 2019 • edited

Hi @Paul Volk , that is exactly correct.  The prefix is "customfield_", so you should be able to ask for "customfield_10026" in your =JIRA() query.  You can also specify the name of the field, actually (as long as it is unique in your Jira instance).

That said, there are still several Jira types that we don't yet support, so it may wind up returning "[field not supported]".  If that happens, we'll be very interested to know what field type it is you're trying to query, so we can make sure its covered in our backlog.

Like # people like this
Emanuel Barros I'm New Here Mar 01, 2019

This is GREAT! I just start using it.

It's perfect because i need to see time estimate and spend to all projects to select users to calculate releases. 

This make my life a lot easy.

Thanks

Like # people like this

Thank you very much for this, it works great! I just need help with two fields....I need the project category field and the project leader field, can you help me with the syntax?  

Like Bianca Encinas likes this
David Vielhuber Mar 01, 2019

Wow, this is awesome.

Can you provide some comments on how often the content of =JIRA gets automatically updated? When I update tickets it seems not predictable when the updates appear in Google Sheets.

Like # people like this
Emanuel Barros I'm New Here Mar 04, 2019 • edited

@Jose Ernesto Samperio Cabrera Hi! I don't think they have a field for project leader. I'm looking here.

Like Bianca Encinas likes this
Everest Hoxha Mar 04, 2019

This is awesome, love it! Exactly what we needed. Saves a huge amount of time with exports. 

Jannik Ackermand Mar 25, 2019

@Donovan Kolbly Hi! This is amazing and will really make a difference in our daily work! Do you have any information on how often the imports will be updated in the sheets when changes happen in the filter? Would be great to know so that I can work around it. 

Again, amazing work!

/Jannik

Like Donovan Kolbly likes this
Tanguy Crusson Atlassian Team Mar 25, 2019

@Jannik Ackermand if you run a query from the sidebar it is currently not updated: you need to re-run it to get fresh data. We're currently adding a button to help you refresh all queries in a sheet (whether generated from the sidebar or via the =JIRA() function) in one go. It's top of the list in terms of improvements as, as you can imagine, we got this feedback a lot. 

Like Jannik Ackermand likes this
Jannik Ackermand Mar 25, 2019

@Tanguy Crusson Thanks for clarifying! A button sounds great. But just to be sure, if I do the =JIRA() it would be updated in realtime? Or do I have to manually update the formulas? 

Tanguy Crusson Atlassian Team Mar 25, 2019

@Jannik Ackermand if you're using the =JIRA() function the behavior can be a bit unpredictable. Most times it will refresh when you close and reopen the spreadsheet, but sometimes the spreadsheet does cache the results and the refresh doesn't happen.

Until we ship the button, the following hack works:

  • leave an empty cell in the sheet, for example A1.
  • concatenate A1 to the JQL you use in the function:
=JIRA(CONCAT("project=MYPROJECT", A1), "issueKey, summary, status, assignee.fullName))
  • anytime you'd like to refresh the data, add or remove a space in A1 

--> this will force the function to be reevaluated.

Like Jannik Ackermand likes this
Jannik Ackermand Mar 25, 2019

OK thanks for the hack @Tanguy Crusson . Are you planning to implement some sort of solution to have it update realtime, or scheduled (say once every hour)?

Tanguy Crusson Atlassian Team Mar 25, 2019

@Jannik Ackermand we're not sure yet, and we haven't looked at the implications of doing it this way. 

I created a ticket so you can track this: https://jira.atlassian.com/browse/API-54 

Like # people like this
Donovan Kolbly Atlassian Team Mar 25, 2019

@David Vielhuber as Tanguy mentioned above, it is a little hard to predict but there's a workaround that can make it a little more deterministic.  There are a couple of tickets gathering interest on this subject, notably https://jira.atlassian.com/browse/API-54  and https://jira.atlassian.com/browse/API-40

Like Jannik Ackermand likes this
Tom Cooper Apr 15, 2019

Is there anyplace we can get a list of supported fields we can specify?  I'm stumbling around trying to find what I'm after, a nice curated list would be helpful.  

 

For instance I'm trying to find a way to tie sub-tasks to their parents and stories to the epics they're linked to as well as the sprints items are assigned to.

Tanguy Crusson Atlassian Team Apr 15, 2019

Hi @Tom Cooper we've been working on changes that would allow you to query the 2 things you just asked for: tying issues to their epic, and sub-tasks to their parent. 

You can follow these issues to be notified when the features are ready:
https://jira.atlassian.com/browse/API-72

https://jira.atlassian.com/browse/API-75

We're also working on this sample spreadsheet that shows you what you can do with the add-on: https://docs.google.com/spreadsheets/d/11jc9CPnBt1KWhRlaO5nnNSFkV5LR3lFjMV7_i6uMB_k/edit?usp=sharing 

Like # people like this

Would this work in google docs? is there a way to do queries in a google doc like it can be done within a atlassian wiki?

Tanguy Crusson Apr 23, 2019

@Jose Antonio Rodriguez the easiest way to do this is to copy a portion of a sheet and paste it in a doc, selecting the option "link to spreadsheet". Anytime you update the sheet (for example by rerunning the query importing Jira data) the doc will be automatically updated

David Vielhuber Apr 23, 2019

As a workaround for auto refreshing, simply add this Google Apps Script function and trigger it every minute:

function refresh() {
var cell_coords = 'A1',
sheet_name = 'INPUT',
sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_name),
val = sheet.getRange(cell_coords).getFormula(),
pos1 = val.lastIndexOf(' ")'),
pos2 = val.lastIndexOf('")');
if( pos1 > -1 ) {
val = val.substring(0, pos1)+''+val.substring(pos1+1);
}
else if( pos2 > -1 ) {
val = val.substring(0, pos2)+' '+val.substring(pos2);
}
sheet.getRange(cell_coords).setFormula(val);
}
Like Tanguy Crusson likes this
Jannik Ackermand Apr 26, 2019

@David Vielhuber Thanks for the tip! When I try to run it i get this error:

"TypeError: Cannot call method "getRange" of null. (line 2, file "Code")". Do you know what the issue could be? 

Best,

Jannik

Comment

Log in or Sign up to comment
Community showcase
Published in Jira

Try Jira Cloud for Outlook: Organize your work without leaving your inbox

Hi Atlassian community, My name is Max and I work on the product integration team at Atlassian. I am pleased to announce the early access program for the Jira Cloud add-in for Outlook. This add-in...

2,668 views 6 15
Read article

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you