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 the early access program for the Jira Cloud add-on for Google Sheets. This add-on gives any Jira user the power of JQL in Google Sheets. The add-on is available today on the G-Suite marketplace (detailed installation instructions below), and I would love to hear about your experience using it.

The solution for your reporting pains

We've all been there: it's Friday afternoon and management asks you for a last minute report. What's the status of your projects, and which ones matter most to our key customers? You have your regular reports configured in Jira, but none of them cover that angle. You'll need to whip something up quickly using data from Jira and Salesforce. This report is the last thing standing between you and your weekend, so what do you do? We all know the answer: spreadsheets to the rescue! But merging Jira and Salesforce data in spreadsheets is no easy task, fixing dozens of CSV exports is just one of the complications. Does it need to be that hard?

It turns out it doesn't anymore. We've heard you loud and clear, you want a better way to work with Jira data in your favorite spreadsheet app so that's what we just shipped: a Jira add-on for Google Sheet.

e92343dd-be06-4328-9674-dabc141cbfbb (1).png

Get your Jira data in Google Sheets in just a few clicks

What functionality is available 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. To customize the output format simply configure your saved filter or change your default columns in Jira.

There are two ways to import Jira data in a spreadsheet:

  1. Using the “Jira Cloud for Sheets” sidebar as shown in the above screenshot, or

  2. Using the =JIRA() custom function. The Jira function gives you the option to specify the list of columns you want the query to return.

image (13).png

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

More examples of what you can do today

ScenarioQuery

Return the issues in project ProjA that have been created in the last week, using your default set of columns.

=JIRA("project = ProjA and createdDate >= -7d")

Return all the issues in ProjA but only the issue key, summary text, the created time, and the reporter.

=JIRA("project = ProjA", "issuekey,summary,created,reporter")

Return the first 10 issues in ProjA but only the issue key and the internal id of the status.

=JIRA("project = ProjA order by createdDate desc", "issuekey,status.id", 0, 10)

Return the third page of issues, considering 10 issues in a page

=JIRA("project = ProjA",, 20, 10)

Questions? We have answers!

How do I install this?

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!

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.

e2283c8d-1c58-443d-9ad6-cd2c561ee161 (1).png

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

If you tick the "Yes, you may contact me about 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.

12 comments

Jodi LeBlanc Community Champion Feb 22, 2019

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

Like 3 people like this

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 1 person likes this

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 1 person likes this

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 1 person likes this

@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 3 people like this

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 2 people like this

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 3 people like this

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 4 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 1 person likes this

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 1 person likes this

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

Like 1 person likes this

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

Comment

Log in or Sign up to comment
Community showcase
Published Thursday in Agile

Managing Projects in Several Jiras at the Same Time

Have you ever noticed that fixing specific problems might be a door opener for a bigger challenge, affecting a wider audience? This was exactly the case when we, as a service company working with mul...

147 views 3 12
Read article

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