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

[emmanuel_dolley]

Hello

With Jira Cloud / Google Sheets integration is it possible to use the filter "AND Status WAS ... ON dd/mm/yyyy? 

I have tried  =JIRA("project=MYPROJECT AND status was New on "10/08/2019" order by created DESC";"issuetype,status";0;10)

 but I get a formula analysis error.

[Earl McCutcheon · Accepted solution]

Hi Emmanuel,

I can see three conflict points in the formatting, first you are using semicolons ";" as the seperator and this should be a comma ",".

Next, the quotation marks around the date need to be escaped and in the "Jira for sheets" application, it uses a second set of quotes to do this, so ""date""

And finally with the date once the double quote escape character is in place the order of the date is incorrect and if you run it in the current formatting "dd/MM/yyyy" with the double quotes you will get this error:

Date value '10/08/2019' for predicate 'on' 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'.

So if you reorder the the date to be ""yyyy/MM/dd"" like the following you should be all set:

=jira("project=MYPROJECT AND status was New on ""2019/08/10"" order by created DESC","issuetype,status",0,10)

Regards,
Earl

[emmanuel_dolley]

Hi Earl,

Thanks a lot for your answer. It works very well.

I have one more related question : I just try to use this formula with a date located in [A;2] in a googlesheet (assuming that 2019/08/10 is in [A;2])

I have tried :

=jira(CONCATENATE("project=MYPROJECT AND status was New on ",CHAR(34),CHAR(34),TEXT(A2,"yyyy/mm/dd"),CHAR(34),CHAR(34)),"status",0,3)

The error message is :

Error in JQL request : 'OR' or 'AND' is anticipated but '2019' is mentioned instead (line 1, character 43) (line239)

[emmanuel_dolley]

For traceability : I have found the answer to my second question.

2 remarks :

• the character "/" in the date seems creating an issue. Replace it by "-" can fix it.
• the quotation marks seem not necessary in this case.

The right formula is: 

=jira(CONCAT("project=MYPROJECT AND status was New on ",TEXT(A2,"yyyy-mm-dd")),"status",0,3).

Thanks again to Earl for the support provided.

[Earl McCutcheon]

HI Emmanuel,

Thanks for the confirmation and follow up with the second solution I'm sure it will help out others if they run into this thread.

And just to provide a little clarification on whats occuring is that the main thing you run into is that the input for the JQL needs to be recognized as a string so throwing in a concatenate to combine the text inside quotes and the cell references outside quotes does the trick for a cell referance that contains text, but to complicate this a bit further the DATE value when passing the field containing 2019/08/20 is formatted as a date table rather than a string so when concatenated converts to a string value of "43697", so passing it in as a TEXT value via text(cellReference,"yyyy/mm/dd") clears this up for the concatenation to recognize the date as a plain text string rather than the date value conversion that is originally taking place.

Overall concatenate() is super powerful here but you just have to look out for special formatting applied to referance cells and thanks again for the extra details

Regards,
Earl

[emmanuel_dolley]

Hi Earl,

Thank you for this clarification. It will help me for my next queries too.

Best regards,

Emmanuel

[Earl McCutcheon]

Hi Emmanuel,

Sure thing, and happy to help out.

Also, I was playing around with this query a bit more as it was bugging me that CONCAT() only allowed passing 2 options where CONCATENATE() would allow for as many as you wanted to pass in, but the TEXT() variable would not come through when passing into the CONCATENATE() formula.

As TEXT() would not work in CONCATENATE(), what I was able to come up with is to set up a Year, Month, Day, & "\" value in independent fields that can be used for the cell referance pass through to the date to the JQL statement I also added in a Project and Status field as well to make the full JQL editable from the field referance values

Side Note: For the references to project and status I'm also thinking you could get even more elaborate and use drop downs with preset values in these fields to make it look nicer as well and have quick toggle options rather than editing the filter directly but I did not make it that far

Then nested a second CONCATENATE() in the first to combine the variables, and it works really well. 

See the following screenshot as an EXE:

The Query I set up has the referance Cells set up as follows:

• a1 = Project name
• a2 = Status name
• b1 = Year 
• c1 = Month
• e1 = Day
• d1 = /

And the Query:

=jira(CONCATENATE("project=",A1, " AND status was ", A2, " on (""",CONCATENATE(B1,E1,C1,E1,D1),""") order by created DESC"),"issuekey,summary,status",0,10)

And you could just as easily set up the field's as a variable to pass in as well and do a coma separated list of fields names for your column values in a single field.

Regards,
Earl

[emmanuel_dolley]

Hi Earl,

Thanks you a lot for this new answer, that's really helpfull and simplify my queries.

I can perform them in a single step now. Thanks again,

Best regards

Emmanuel

[Vagton Ferreira]

ok

[emmanuel_dolley]

Hi Earl,

Thanks a lot for your answer. It works very well.