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

Judah Ferst December 12, 2019

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? 

3 answers

0 votes
Judah Ferst December 13, 2019

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
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
December 13, 2019

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 December 13, 2019

Hi - adding those spaces worked! Thanks so much!

Like Earl McCutcheon likes this
Earl McCutcheon
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
December 13, 2019

Awesome thanks for the confirmation :) 

0 votes
Judah Ferst December 13, 2019

Thanks @Earl McCutcheon , 

 

Now I am running into another error: 

Project Reporting_V3 - Google Sheets (1).png

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

0 votes
Earl McCutcheon
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
December 13, 2019

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

Suggest an answer

Log in or Sign up to answer