IN FUNTION JIRA() OF EXCELL

Vagton Ferreira

IN integration USE function from Excel (https://docs.google.com/spreadsheets)

=JIRA("category = Helpdesk AND duedate >= "&TO_TEXT($G$2-4)&" AND duedate <= "&TO_TEXT($G$2)&" ORDER BY due DESC"; "issuekey, assignee, duedate, resolved")

That worked before.
RETURN now

#ERRO! - Action not allowed (linha 0).

CONNECTION IS OK

Earl McCutcheon

Hi Vagton,

Thanks for reaching out and the duedate nested in queries can get really complicated fast, due to conversions that are happening in the cells in the background trying to convert the text values to a data formatting in the cell

I have been playing around with this for a bit and from the portion of the formula you have added "&TO_TEXT($G$2-4)&" it seem like you are passing in a date value over one of the other supported functions such as startofday() and endofday(), and looking this over I first want to clarify what the variable is in the field G2 on your sheet.  Are you using a date value or are you using another value?

Also you noted that this worked before, do you mean that this exact query without modification was previously functioning on your sheet and stoped working or that you had previously passed a variable using TO_TEXT() on another sheet in a similar way, but a different query altogether?  And if the latter is the case and it was just the function that worked in an alternate scenario, what variable was passed, and do you have that query as an exe?  If it was this exact query that stopped working something may have changed in date formatting in google sheets recently that is causing this to occur.

So If you're using a date value formatted as yyyy/MM/dd, this looks a lot like a post I answered recently viewable at the following Link, relating to how the nested formula is picking up the slashes "/" and the quotation marks " with how the sub formula was passing them into the query and we were able to use a CONCATENATE() function to nest in the date values to properly escape the quotes slashes in the date, Noting the Queries I uses as examples below are modified from the sheet originally used in this post:

• https://community.atlassian.com/t5/Jira-Core-questions/Jira-Cloud-for-Sheets-use-quot-AND-Status-WAS-ON-dd-mm-yyyy/qaq-p/1157105

Doing a bit more testing as well using the ampersand connector "&" format you have been using over the previous concatenate example i used in the precious thread, something i found that does work is to still use the same layout to seperate the yyyy/MM/dd value into seperate fields and then make a main referance cell as "=B1&E1&C1&E1&D1" then using that cell in the query in place of the to_text() formula like the following:

• a1 = Project name
• a2 = Status name (relevant to the alternate post)
• b1 = Year 
• c1 = Month
• e1 = Day
• d1 = /
• M21 = "=B1&E1&C1&E1&D1" and renders as the date
• And then modifying the formula from the other post to include this new referance noting the quotes are in a group of 4 and and we can pass in &M21-4&

=jira("project="&A1&" AND duedate <="&""""&M21-4&""""&" order by created DESC","issuekey,issuetype,duedate,parent,summary,status",0,100)

A follow up Note as well the &M21-4& value takes the concatenated result set and outputs this in a DATEVALUE format.  You might be able to leverage the to_date and date value conversions as well to masage the field data as needed.  Converting a date to a datevalue number string will also pass into the query but still require the quad quotes.  EXE m22 = 43692 (the datevalue of m21-4) so using &""""&M22&""""& would also work as a conversion off the direct date cell which can be input as the actual date not a concatenated value, EXE in the following G1-4 where g1 is the date value formatted as yyyy/MM/dd rather than the concatenated m21 value:

 

Regards,
Earl