trouble passing jql to jira plugin for excel (jira.jql)

[UdayKiran]

i have jira plugin installed in excel and working with JQL query import type.

Trying to use import type - JIRA.JQL() function

When i give below in excel 

=JIRA.JQL("status in (""To Do"", ""In Progress"", WAITING) and assignee = ""xy@zab.com""","key, summary, priority,labels")

I get the desired output.

Now i want change assignee as a parameter, i.e., give email address in another cell and want the jql to be updated.

I tried splitting the jql into 3 different cells (very rudimentary way :))

"status in (""To Do"",""In Progress"",WAITING) and assignee =""

xy@zab.com

""",key, summary, priority,labels")

and, i combine/merge the above 3 into a single cell(say cell H34) and 

=JIRA.JQL(H34), i get the below error

Error in the JQL Query: Expecting operator but got 'To Do'. The valid operators are '=', '!=', '<', '>', '<=', '>=', '~', '!~', 'IN', 'NOT IN', 'IS' and 'IS NOT'. (line 1, character 14)

I tried re-ordering the JQL, changing the category of the cell from General to Text but no luck in getting this working.

Any pointers to what I might be missing?

[Bill Sheboy · Accepted solution]

Hi @UdayKiran 

I wonder if something in the combine/merge of the values from the cells is changing the result, such as collapsing the double quotation marks.  You could check that by referencing your cell H34 in another cell with =H34 and see what it looks like.

Have you tried to in-line to concatenation of the text, like this (doubling up the quotations because the concatenate() collapses/interprets them.

=JIRA.JQL(CONCAT("""status in (""""To Do"""", """"In Progress"""", WAITING) and assignee = """"",cellWithYourUserInfo,""""""""), "key,summary,priority,labels")

Kind regards,
Bill

[UdayKiran]

Hi @Bill Sheboy ,

Thank you, for the help!

Your suggestion of using CONCAT worked and i'm able to get the desired output.

Next step would be getting the Jira Cloud Plugin to work when the excel file is opened on using Excel Online from BOX.

Thanks again.

[Mark Darby]

I'm getting lost in the quotation marks.  Is there a way to see how this will actually render when it passes to Jira?  Thanks, Mark

[Bill Sheboy]

Hi @Mark Darby 

The parameter passed to JIRA.JQL() must be enclosed in quotation marks, and when you want a quotation mark in the expression, you double it (such as with "")

When I tried this, I created the expression first in a spreadsheet cell, and then referenced that cell in another.  That showed me what would be passed to Jira.  Once I had debugged it, I pasted it directly in the JIRA.JQL() function.

Kind regards,
Bill

[Mark Darby]

Thanks, Bill.  I've pasted your formula from above.  I deleted the "waiting" field and added the cell reference to my name.  Getting the "Error in JQL Query.  Expecting operator but got 'To Do'" error.  

Here is the CONCAT statement:  

CONCAT("""status in (""""To Do"""", """"In Progress"""") and assignee = """"",D183,"""""""")

Pulling that out into a separate cell renders as:  

"status in (""To Do"", ""In Progress"") and assignee = ""Mark Darby"""

Any ideas?  

Thanks a bunch,

Mark

[Mark Darby]

The whole jql reads:

=JIRA.JQL(CONCAT("""status in (""""To Do"""", """"In Progress"""") and assignee = """"",D183,""""""""),"key,summary,priority,labels")

[UdayKiran]

Here is the one that works for me:

=JIRA.JQL(CONCAT("status in (""To Do"", ""In Progress"", ""WAITING"") and assignee = """,$J17,""""),"key,summary,priority,Status,labels")

[Mark Darby]

This worked!   Thank you bunches!