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

[Tom Gagne]

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

[Bill Sheboy]

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]

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]

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]

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]

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]

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]

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]

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]

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.

[Kevin Maes]

This is sad. It would make the integration so much more dynamic and valuable