How to reference cells in JIRA() function for Google Sheets

[Edwin Meijer]

Question: is there a grammar-construct in the JIRA() function to reference the value of a cell?

Example:

I have two cells (C15 and C16) that have the date values (formatted as 'yyyy/mm/dd' and I like to reference them in the JQL similar to this:

=JIRA("status changed to DONE during ($C15, $C16)")

 
I have seen suggestions to use Google's CONCAT() function to assemble a JQL query string and use that as the input of JIRA() function, but that becomes unwieldy very quickly.

[Earl McCutcheon · Accepted solution]

Hello @Edwin Meijer ,

You can get around concatenating the JQL together by using ampersands"&" as an escape character like this:

=JIRA("status changed to DONE during ("&$C15&", "&$C16&")", "Fields", offset, limit)

However, one pitfall I am aware of where the concatenate() function you mentioned might be needed to do references is when there are formatting issues at play that comes with how google sheets handles date values with calculations in the referenced cells.  If any calculations are being done like date 1 is in one field and date two is calculated as date1 + 5 days (or something similar), so as an example the value "2021/01/20" would have a datevalue("2021/01/20") = "44216" and the datevalue() is used to calculate any time shifts then the value output is a formatted as a representation of that value in origional formatitng on a toDate() conversion

So the Google sheet will convert a date to a number that is a datevalue() formatting so the time calculation for cell references as the reference value inside the quotes is a dedicated String, and the value for a cell referance passes the date as a datevalue() formatted string for the destination cell to convert back to a string so the Value passed is not the intended format for the cell. 

You can sometimes get around the errors a bit using a toDate() conversion to change teh datevalue() back for referencing the field that is a calculated date value to change it back into the proper date formatting to be input to the JQL.

But Dates can get really tricky really fast in spreadsheets due to the various formatting options for dates and locals,  So give it a try with the "&" escapes first and let me know if you hit any roadblocks and send me an example of the date ranges and formatting you are using in the cells and I can take a look and see where I can help out further

Regards,
Earl

[Edwin Meijer]

Thanks - finally got around to try this out... it works!

I ended up having to:

• set the format of the date fields that I am referencing in the JQL as "Plain Text"
• enter the date values as "yyyy-dd-mm" 

If you format them as Date, you probably get them as date values as described above... I briefly tried to work around that with the suggested toDate() conversion, but was not successful... using a "plain text" field for the date fields I am referencing works for me and I didn't experiment further.