nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

Earl McCutcheon

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

All comments

Earl McCutcheon

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""

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.

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira-cloud

jira-core

sheet