Try the new Jira Cloud add-on for Google Sheets

[Tanguy Crusson]

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

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

If you have the formula there then it should work

[David Vielhuber]

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

[Jannik Ackermand]

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]

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]

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

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

@Tanguy Crusson  that works perfect. thanks!

=INDEX(JIRA(CONCATENATE("issuekey=",B1), "status", 0, 1), 2, 0)

[Jose Antonio Rodriguez]

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]

Hi,

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

[Lillian Shibata-Salley]

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]

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]

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]

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]

Very nice!! Thank you.

[Tanguy Crusson]

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]

Hi,

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

[Tanguy Crusson]

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

[Jose Antonio Rodriguez]

Hi, what about Jira Server? would it work?

[Alex Bernardin]

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]

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

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

[Tanguy Crusson]

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]

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 .