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

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

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.


1 answer

1 accepted

1 vote
Answer accepted

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

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.

Like Earl McCutcheon likes this

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
TAGS
Community showcase
Posted in Jira Software

Presenting the "Best of 2020" Jira Software roundup!

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

7,120 views 8 28
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