Question: is there a grammar-construct in the JIRA() function to reference the value of a cell?
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.
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
Thanks - finally got around to try this out... it works!
I ended up having to:
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.
Catch up with Atlassian Product Managers in our 2020 Demo Den round-up! From Advanced Roadmaps to Code in Jira to Next-Gen Workflows, check out the videos below to help up-level your work in the new ...
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