Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Jira Cloud For Sheets - Newbie questions

Edited

Hello all,

I installed "Jira Cloud For Sheets" today.

I tried to use JQL to get several Jira issues.

There are some points I couldn't figure out:

1. Can I use cells values in the query? How?

2. Can re-order the fields?

3. Can I preserve the sheet formatting (colors, bolds, etc.) when refreshing the query?

4. Must the report begin at A1 cell?

 

I am not locked on JQL. If there is a better way I'd like to hear.

Regards!

2 answers

1 accepted

1 vote
Answer accepted

Hello @tal-yechye ,

Welcome to the community!

To address your questions this can all be done by using the =jira() command and the native google sheet options:

1. Can I use cells values in the query? How?

2. Can re-order the fields?

3. Can I preserve the sheet formatting (colors, bolds, etc.) when refreshing the query?

4. Must the report begin at A1 cell?

Question 1, 2 and 4 are all answered right up front with the first answer, you can insert a =jira() command into any cell on the sheet to change starting position of where the data is inserted and within the =jira() command you can reference alternate cells as well as rearrange the order of the columns and I will give a better example of that below after covering the other points.

The format for the command is as follows and question 2 is addressed in the columns section:

=JIRA("jql", "columns", offset, limit)

The Example below is given in the sheet when you start typing data into the command with the quick tips preview for the command, and it would give you the first 10 issues in the project "myproj" ordered by the date created using Advanced searching JQL formatting and the columns listed "issuekey, summary, status, assignee, created" would be displayed in that defined order

JIRA("project = MYPROJ order by created desc", "issuekey, summary, status, assignee, created", 0, 10)

Next, the desired fields are listed as columns in the desired order you want them to appear, so enter the column name comma separated in any order, and you can even repeat column names if you wanted to highlight different comparable data points within a single field.  (as an use case example this might be useful in fields that have text or multiple inputs where you want different columns showing comparisons between different combinations of data in the field)

the Offset affects the starting point for the issues, as an example, if you have 10 issues returned by the JQL, and you set the offset to 5 the first 5 issues will not be displayed, so leave this at 0 to show all issues.

the Limit is how many total issues you want to be displayed,  again leave this at 0 to display all results of the JQL or limit the result set which can be useful in testing changes so you are not slowing down the system with excessive calls OR if you have the same 10 issues mentioned above but only care about seeing issues 3 through 9, you can do offset = 2 limits = 7 for specific sub-datasets

Next, point 3, generating a query referencing other cells, I put together a super simple use case which is to have a single query that can be easily switched to different projects by reference to another cell for the input.  In my test instance, I have 3 projects "asdf", "qwerty", and "zxcv" So I entered a data validation cell in A1 that contains these three as a drop-down list.  Do this by going to Data >> Data Validation >> Set Criteria to "List of items" input the list:

Screen Shot 2021-12-01 at 3.24.19 PM.png

and A1 will now have a drop-down like this:

Screen Shot 2021-12-01 at 3.27.14 PM.png

now for my data import, I can enter my =jira() command in any other cell but i'm using B1 for the example.  and input the following to reference A1 as the source for my Project value and the data set will output 5 issues including the columns Issue key, summary and status as my output and update each time i change the value in A1 between my three projects:

=jira("project = "&A1,"issuekey, summary, status",0,5)

Screen Shot 2021-12-01 at 3.39.09 PM.pngScreen Shot 2021-12-01 at 3.39.15 PM.pngScreen Shot 2021-12-01 at 3.40.52 PM.pngScreen Shot 2021-12-01 at 3.36.04 PM.png

And lastly for point 3 formatting any formatting you add to cells such as maning the header Rom Bold, or conditional colors the changes will remain as the input variable is changed:

Screen Shot 2021-12-01 at 3.44.23 PM.pngScreen Shot 2021-12-01 at 3.44.39 PM.pngScreen Shot 2021-12-01 at 3.44.57 PM.png

And you can get as crazy and elaborate as you want with this to generate all kinds of reporting, using data references between multiple sheets, and using the data output in charts and graphs, and even using Google Sheets App script functionality to build out queries based on conditional logical inputs,  or just keep is simple to accomplish smaller report tasks, but the options are seemingly endless

Hope this helps get ya going in the right direction to get rolling.

Regards,
Earl

1 vote

What is exactly that you're trying to solve?

As far as I can see from your question, you want to export an arbitrary list of issues to a spreadsheet, but pre-define visuals (colors, fonts, etc.), column order, the top-left cell, etc. You have full control of all these with a Better Excel Exporter which allows defining an Excel file as template and inserts issue fields into that with very level of flexibility.

To be fair, I think that 2 is "yes" and 4 is "not necessarily", so these can probably be solved with Jira Cloud for Sheets, as well.

(Discl: note that Better Excel Exporter is an app that my team develops.)

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
TAGS
Community showcase
Published in Jira Software

👋 Looking for 15-20 volunteers to test Atlassian training content

Hi everyone! Are you interested in beta testing Atlassian University’s newest (unreleased!) training course? We’re looking for 15-20 volunteers to test our newest training course, Basic reporting...

430 views 19 22
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you