It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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

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

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

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?

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

Hi - adding those spaces worked! Thanks so much!

Like Earl McCutcheon likes this

Awesome thanks for the confirmation :) 

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you