Try the new Jira Cloud add-on for Google Sheets

77 comments

Tanguy Crusson
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
April 26, 2019

@Jannik Ackermand I think that's because you need to update the sheet name in the script (currently it's set to "INPUT"). Also , the cell is "A1".

Jannik Ackermand April 26, 2019

@Tanguy Crusson Thanks! I changed the sheet name to the real one. Should I change the A1 to some other value as well? I have the =JIRA() formula in A1 in the sheet. 

Tanguy Crusson
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
April 26, 2019

If you have the formula there then it should work

David Vielhuber April 26, 2019

Hello Jannik! You have to change your sheet_name to your actual sheet name. Mine is called "INPUT", but simply change that.

Jannik Ackermand April 29, 2019

Thanks @David Vielhuber and @Tanguy Crusson it is working really nicely. However, if I want the =JIRA() to import more than the default 250 issues the script seems to break. Do you know if it is possible to account for that somehow? 

Andy McCormick May 1, 2019

This is fantastic. Only thing I would want to add is the ability to hide column labels, maybe with an additional param. 
I'd rather be able to type in something like below and just have the status displayed. 

=JIRA(CONCATENATE("issuekey in (",B1,")"),"issuekey, status","columnlabel=false")

This would let me just copy and paste issue keys into a column and then fill this formula down in the column next to it. 

Right now since the formula uses a cell for the label then the cell below for the value, I can't stack this on top of itself. So I have to paste all my keys into a column, then have another cell that uses TEXTJOIN to take the entire column and merge to one cell separated with a comma, then have another cell off to the side that displays the issuekey and status for the query as so:


=JIRA(CONCATENATE("issuekey in (",B1,")"),"issuekey, status")

It works. just the spreadsheet is a little messy. I could use a separate worksheet to keep the values in one tab and results in another, but still adding more to the sheet than I think is needed.

Thanks again for always improving!

**after typing this all this out I did move the values to a separate tab and am just using this formula on the first sheet.

=JIRA(CONCATENATE("issuekey in (",TEXTJOIN(",",1,Values!A1:A1000),")"),"issuekey, status")

I think this works for what I need, but I'd still like to have that more granular control over the column labels.

 

Tanguy Crusson
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 2, 2019

@Jannik Ackermand the default is 250, but to return more rows you can use the pagination parameters. There's an example for how to do this in this spreadsheet.

In the sheet "large imports" 

Tanguy Crusson
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 2, 2019

@Andy McCormick thanks for the feedback! This is something we're considering, but there's already a built in function in Google Sheets that enables you to do just that. 

Check out this spreadsheet in the "remove header row" sheet:

For a single issue:

=INDEX(JIRA("key=MOB-1", "issuekey, status", 0, 1), 2, 0)

For a list of issues:

=QUERY(JIRA("project=MOB order by updated desc", "issuekey, status"), "SELECT * OFFSET 1", 0)
Andy McCormick May 2, 2019

@Tanguy Crusson  that works perfect. thanks!

=INDEX(JIRA(CONCATENATE("issuekey=",B1), "status", 0, 1), 2, 0)
Jose Antonio Rodriguez May 2, 2019

I was a bit confused, where I work we used Jira Server, so this add-on will not work for us... are there plans to have it as a Jira Server add-on to google sheets?

t.muchai May 9, 2019

Hi,

Do you have a way to update JIRA directly from the Google Sheet?

Lillian Shibata-Salley May 10, 2019

Hi I tried this yesterday and today and just trying to get data from a starred filter, nothing happens and I don't get any results back. what am I doing wrong?

Thanks,

Lillian

handicrafts
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 11, 2019

Hi @Lillian Shibata-Salley , we received a question about this yesterday and have responded with a couple of suggestions here:
https://community.atlassian.com/t5/Jira-Software-questions/Try-the-new-Jira-Cloud-add-on-for-Google-Sheets-No-results/qaq-p/1079366

Tanguy Crusson
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 13, 2019

Hi @t.muchai , there isn't currently a way to do that, but it's something we're considering. You can follow this ticket to be notified when we start working on it and when it becomes available: https://jira.atlassian.com/projects/API/issues/API-87

Gazza May 13, 2019

Could be the perfect tool for us to connect between Google Sheets and Jira.

API-52, API-86 & API-87 will make all the difference for this add on to be 10/10.

Do we have an idea of when these will become available - if no current plans we will have to get something from the marketplace that allows this

 

Thanks

Gary

Uday Joshi May 14, 2019

Very nice!! Thank you.

Tanguy Crusson
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 14, 2019

hi @Gazza , thanks for the feedback. 

We're currently working on a way to refresh data - and there is an existing workaround you can use in the meantime.

For the ability to create/update issues from Google Sheets: it's in our backlog but we can't make any promises for when it will be ready, as it's relatively tricky to get right. 

If the latter is a blocker for you in the short term then you might want to follow the issues to be notified when they're ready, and explore other solutions from the Marketplace in the meantime.

Syed Faisal Hasan May 16, 2019

Hi,

 

Is this add-on usable for Jira Service Desk as well??

Tanguy Crusson
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 16, 2019

Hi @Syed Faisal Hasan , yes it works with all editions of Jira Cloud. 

Jose Antonio Rodriguez May 16, 2019

Hi, what about Jira Server? would it work?

Alex Bernardin May 25, 2019

Really intrigued by the potential of this tool!

I'm wondering if there's a configuration option that would suppress the header row from being returned by the JIRA() function?

I'm also wondering if there's a way to allow the JQL to reference other cell values in the sheet

I have an existing sheet with a row per project, and many columns. I would like to populate one of the columns with the status of the project from Jira. Ideally, I would add a column, use a formula like JIRA("key=$c1","status",0,1), and then copy down to all the rows. 

But when I try that, I have two problems:

1. the $ to reference the other cell in the sheet doesn't get interpolated before the JQL is sent to the server, so it throws an error

2. the JIRA function returns a header row in addition to the results rows, so even if I hardcode the key value, i'm still getting back two rows inserted into my sheet instead of a single cell

 

Thanks!

Tanguy Crusson
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 26, 2019

@Alex Bernardin the following should work:

=INDEX(JIRA(CONCAT("key=", A1), "status"), 2)

CONCAT lets you concatenate text and cell values.

INDEX lets you return a particular cell/row in a range of cells.

Tal Ross May 26, 2019

This tool is an extremely helpful solution to develop meaningful data queries on the health of projects across the enterprise.

Tanguy Crusson
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 30, 2019

That's great to hear @Tal Ross ! Would you mind leaving us a review here ? :-) 

https://gsuite.google.com/marketplace/app/jira_cloud_for_sheets/1065669263016

Patricia Francezi
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 9, 2019

Hi. Im not sure if its already addressed somewhere, but Service Desk Managers, need the SLA results (the calculated information its shown in Jira Filters), but its not possible to share in another way.

Time elapsed for example: filters shows 1h26 - we are able to see the number, but not download it and work it in another way (i.e tracking the avarage time per agente). I know there is another database related to its results, and as Cloud instance we dont have access. I was hoping that this problem would be solved by the add-on.

 

Thanks in advance. For a first impression, the add solved the 1k limit problem .

Comment

Log in or Sign up to comment
TAGS
AUG Leaders

Atlassian Community Events