Jira Cloud for Sheets : use "AND Status WAS ... ON dd...

[Judah Ferst]

OK, I am trying something that should be much simpler, but I can not seem to get to work:

This works:

=JIRA(CONCAT("project=",Project_Data!$B$2), "summary, issuekey, priority, reporter, assignee, timeoriginalestimate, timeestimate, timespent, issuetype, customfield_10009, labels, resolution, status, resolutiondate, created, duedate, customfield_11700, fixVersions, customfield_11100, project")

But this throws an error:

=JIRA(CONCAT("project=",Project_Data!$B$2, " ORDER BY issuekey asc " ), "summary, issuekey, priority, reporter, assignee, timeoriginalestimate, timeestimate, timespent, issuetype, customfield_10009, labels, resolution, status, resolutiondate, created, duedate, customfield_11700, fixVersions, customfield_11100, project")

All I am trying to do is to sort the results by issue key - any ideas? 

[Judah Ferst]

OK, if I make CONCATENATE all caps I get a different error:

Error: Error in the JQL Query: Expecting either 'OR' or 'AND' but got 'asc'. (line 1, character 31) (line 239).

^^^^ looks like a plugin error to me, cause if I put this into JQL in Jira it works. Any thoughts?

[Earl McCutcheon]

Hi @Judah Ferst ,

Thanks for the update and I'm looking at this now but just a heads up, I moved your question and of conversation chain to a new thread so we can focus the answer on an independent post, as the initial question is only partially related to what you are seeing  and this will help others find the different answer easier in the future depending on search terms used.

But The error you are seeing now looks like what would occur if the source cell had a formatting error, and I was able to reproduce the error by pointing a "=CONCATENATE(Sheet1!A1,test)" where sheet1 A1 contained a syntax error already rendering as "#ERROR"

But to get a better understanding of the failure point, What is the query or data point you have in the Project_Data sheet in cell B2? 

Also if you run just the concatenate portion of the formula in the second sheet does the returned value look correct as it might be a spacing issue, try with the following format that adds in a couple white spaces around "project =" and the " Order By" clause from what it looks like is happening in the screenshot:

=CONCATENATE("project = ",Project_Data!B2," ORDER BY issuekey asc")

Regards,
Earl

[Judah Ferst]

Hi - adding those spaces worked! Thanks so much!

[Earl McCutcheon]

Awesome thanks for the confirmation :) 

[Judah Ferst]

Thanks @Earl McCutcheon , 

Now I am running into another error: 

Error: Error in the JQL Query: The character '#' is a reserved JQL character. You must enclose it in a string or use the escape '\u0023' instead. (line 1, character 1) (line 239).

Any ideas?

Thanks, 

-Judah

[Earl McCutcheon]

Hi @Judah Ferst ,

Thanks for reaching out on this, and I think the limitation you are hitting is a argument length constrain in concat() vs concatanate().

Concat only allows for combining 2 values and expects only 2 comma separated arguments total and you have 3 arguments in the second query in the concat string,  while concatenate will allow for 2 or more comma separated arguments.

concat(value 1, value 2) will work but concat(value1,value2,value3) will fail and you would need to use concatanate(value1,value2,value3) in its place to get the last value in.

Regards,
Earl