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

[Vagton Ferreira]

The function worked before as above with ";" and if you put the "," she shows: #erro de análise de formula.

[Vagton Ferreira]

I think it may be something with the permission, you can confirm for me the message:

Action not allowed (linha 0).

[Earl McCutcheon]

Hi Vagton,

Google sheets treats the separator as either comma or semicolon based mostly on the locale the user is on. So ideally a semicolon should also work the same as the comma but sheets does a conversion in the background and converts this from a semicolon to a comma to pass the value to jira through the connector, so there is a slight variation in the actions performed when swapping the two out; which could be why the error only triggers when a comma is used but not the semicolon. 

We did some searching on the error you noted and found some problems have been noted with this error when there is a permission issues when connecting to a remote application, as you mentioned.  I recomend creating a new blank sheet and re-adding the jira sheets connector to this page and see if the error is triggered on a newly established connection with the tool.

Regards,
Earl