The Atlassian Community can help you and your team get more value out of Atlassian products and practices.
@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".
@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.
If you have the formula there then it should work
Hello Jannik! You have to change your sheet_name to your actual sheet name. Mine is called "INPUT", but simply change that.
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?
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.
@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"
@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)
@Tanguy Crusson that works perfect. thanks!
=INDEX(JIRA(CONCATENATE("issuekey=",B1), "status", 0, 1), 2, 0)
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?
Do you have a way to update JIRA directly from the Google Sheet?
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?
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
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
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
Very nice!! Thank you.
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.
Is this add-on usable for Jira Service Desk as well??
Hi @Syed Faisal Hasan , yes it works with all editions of Jira Cloud.
Hi, what about Jira Server? would it work?
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
@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.
This tool is an extremely helpful solution to develop meaningful data queries on the health of projects across the enterprise.
That's great to hear @Tal Ross ! Would you mind leaving us a review here ? :-)
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 .
5 total posts