Try the new Jira Cloud add-on for Google Sheets

[#1]

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

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:

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.

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.

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:

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:

Export filters and searches directly from Jira to Google Sheets, with just one click!

You can now export filters and searches directly from Jira to Google Sheets. You can find the Google Sheets icon as a new option next to the share and export buttons on the issues search page:

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.

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.

[Jodi LeBlanc]

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

[Jens Rosemeier]

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

[Tanguy Crusson]

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

[Damon Gaylor]

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.

[Tanguy Crusson]

@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 

[Paul Volk]

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!

[donovan]

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.

[Emanuel Barros]

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

[Jose Ernesto Samperio Cabrera]

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?  

[David Vielhuber]

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.

[Emanuel Barros]

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

[Deleted user]

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

[Jannik Ackermand]

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

[Tanguy Crusson]

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

[Jannik Ackermand]

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

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

[Jannik Ackermand]

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]

@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 

[donovan]

@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

[Tom Cooper]

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]

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 

[Jose Antonio Rodriguez]

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]

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

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);
}

[Jannik Ackermand]

@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