Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,463,074
Community Members
 
Community Events
176
Community Groups

In the Excel plug, how to use cell references in JQL?

The plug-in opens a window on the right and gives me the opportunity to enter JQL directly into the "JQL Query" text-entry field.  Is there a way to use cell references in that query from another tab?

Specifically I have another tab (named "Weeks") that contains a from- and to-date fields.  I would like to use them in my JQL.  Something similar to:

status changed to "Dev Complete" during("&Weeks!b6", "&Weeks!c6")

 

1 answer

0 votes

Hi @Tom Gagne 

Please take a look at the examples in this question's solutions, as I believe you are missing some ampersands to make the query concatenate correctly and use of nested quotation marks:

https://community.atlassian.com/t5/Jira-Software-questions/How-to-reference-cells-in-JIRA-function-for-Google-Sheets/qaq-p/1581545

Kind regards,
Bill

Bill, that might work for =JIRA.SQL() but I haven't been able to get it working for the textarea box.  Have you?

No, I have only done it as mentioned in that other post or with saved filters.  

What happens if your remove both the quotation marks and ampersands: will the plugin resolve it then?

status CHANGED TO "Dev Complete" DURING ( Weeks!$B$6, Weeks!$C$6 )

Bill, good point.  If I try without quotes it complains about the "!" If I try with quotes I get:

Date value 'Weeks!$B$6' for predicate 'during' is invalid. Valid formats include: 'yyyy/MM/dd HH:mm', 'yyyy-MM-dd HH:mm', 'yyyy/MM/dd', 'yyyy-MM-dd', or a period format e.g. '-5d', '4w 2d'.; Date value 'Weeks!$c$6' for predicate 'during' is invalid. Valid formats include: 'yyyy/MM/dd HH:mm', 'yyyy-MM-dd HH:mm', 'yyyy/MM/dd', 'yyyy-MM-dd', or a period format e.g. '-5d', '4w 2d'.

Ah...that second thing is the Jira/Excel date format mismatch.  Perhaps try this to solve both issues at once:

Create Excel named variables which point to your cells, and in the named variable use Excel's TEXT() function to convert to data format Jira is wanting and forcing to "text" in one step.  Then reference the named variables in the JQL.

=TEXT(Weeks!$B$G, "YYYY-MM-DD")

Jim, maybe one of my questions should be:  Is there any documentation about what is supposed to be supported?

And if it's not substituting a cell reference, how would it reference a named variable?

I'm not an excel expert so I'll look up named variables...

Tom, I have not found any documentation for these spreadsheet addin features...just some community posts on them.

And apologies if I misunderstood that error.  I thought it was indicating it resolved the cell reference and that the date/time format was wrong.  It is possible the exclamation point is causing the problem and that is why I suggested using an Excel named reference.  Please look here for information on using the name manager in Excel:

https://support.microsoft.com/en-us/office/use-the-name-manager-in-excel-4d8c4c2b-9f7d-44e3-a3b4-9f61bd5c64e4

Yea, documentation would be good.  I don't think the JQL window supports cell references .  I've tried a bunch of permutations with and without quotes, with and without the &, I don't think it's going to work.

Thank you so much for helping.

Yes I have tried all kinds of permutations as well and haven't found a way to have the JQL in Excel use Cell references as parameters for a JQL in the addin.

Suggest an answer

Log in or Sign up to answer