JQL Syntax in Google Sheets

[Rena Lourie]

I'm hoping to transpose a Jira Epic and Issue into google sheets, but having trouble with the syntax translation into Google Sheets. The sample formula gives: 

JIRA("project = MYPROJ order by created desc", "issuekey, summary, status, assignee, created", 0, 10)

But I need to import an epic and subtasks as well. When I try using JQL, the spreadsheet throws an error. For Example: 

JIRA("project = MYPROJ "Epic Link"=XXXX-4404 OR (issuetype = sub-task AND "parentEpic" IN (XXXX-4404))", "issuekey, summary, status, assignee, created", 0, 10)

 Has anyone come across a similar situation? Google sheets doesn't seem to like the quote marks around "Epic Link", but I'm not sure if there's another way to call what I'm looking for. Any help is appreciated, thanks!

[Riley Venable (Atlas Bench) · Accepted solution]

You are correct that Google Sheets does not like the quote marks around "Epic Link". To get around this, you can use single quotes instead, like this: JIRA("project = MYPROJ 'Epic Link'=XXXX-4404 OR (issuetype = sub-task AND 'parentEpic' IN (XXXX-4404))", "issuekey, summary, status, assignee, created", 0, 10) This should allow you to import the epic and subtasks as well.

[Rena Lourie]

Hi Riley,

Wow, thank you so much for the incredibly fast answer! I tried that, and now it's throwing a new error:

"Error in the JQL Query: Expecting either 'OR' or 'AND' but got 'Epic Link'. (line 1, character 18)"

Any thoughts? Thank you so much for your help!

[Riley Venable (Atlas Bench)]

Sorry for my mistake, "project = MYPROJ AND 'Epic Link'=XXXX-4404.

[Rena Lourie]

Amazing, that worked! Much appreciated, here's the full query for anyone who needs it:

=JIRA("project = MYPROJ AND 'Epic Link'=XXXX-4404 OR (issuetype = sub-task AND 'parentEpic' IN (XXXX-4404))", "issuekey, summary, status, assignee, created", 0, 10)