Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

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

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

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.

2 answers

1 accepted

1 vote
Answer accepted

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

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)

Like Earl McCutcheon likes this

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.

Like Earl McCutcheon likes this

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

Like emmanuel_dolley likes this

Hi Earl,

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

Best regards,

Emmanuel

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:

Screen Shot 2019-08-22 at 8.48.06 AM.png

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

Like emmanuel_dolley likes this

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

Like Earl McCutcheon likes this

Hi Earl,

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Posted in Jira Core

How to manage many similar workflows?

I have multiple projects that use variations of the same base workflow. The variations depend on the requirements of the project or issue type. The variations mostly come in the form of new statuses ...

4,018 views 11 5
Join discussion

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