Jira Cloud For Sheets - Newbie questions

[tal-yechye]

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!

[Earl McCutcheon · Accepted solution]

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:

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

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)

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:

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

[Aron Gombas _Midori_]

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