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

Tom Gagne March 1, 2022

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
Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 1, 2022

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

Tom Gagne March 1, 2022

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

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 2, 2022

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 )
Tom Gagne March 2, 2022

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

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 2, 2022

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

Tom Gagne March 2, 2022

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

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 3, 2022

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

Tom Gagne March 3, 2022

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.

Jorge Dardon July 28, 2022

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