Failing to fetch issues using JIRA Cloud for Sheets

[maganti.sreekar]

I have connected my JIRA to Google Sheets, and am trying to fetch issues using Basic querying, but I'm seeing a persistent error:

Error message: "Error in JQL Query: Expecting either a value, list or function but got 'IN'. You must surround 'IN' in quotation marks to use it as a value. (line 1, character 35)"

The same query was successful in populating the Google Sheet with JIRA issues in the past

I tried pasting the JQL query in the Filter search in JIRA to verify if the syntax is right, and this is showing that everything is fine with the query syntax:

Please clarify what the issue is here.

Thanks,

Sreekar

[Andy Heinzer · Accepted solution]

Hi Sreekar,

I tried to recreate this problem based on your screenshots, but so far I have not been able to.

One way to try to work around this would be to go to this search in Jira first (such as in your 2nd screenshot).  Then run this search at least once.  Once you do this, you can then save this search as a filter.  Save that filter.  When you do, the URL in the address bar will change to show you the id of that filter.  It will look something like this

.atlassian.net/issues/?filter=10073

One thing you can try to get around this problem is to then go back to google sheets and enter in this for the JQL search

filter=10073

You will need to change that number to whatever number you found when saving your filter.  This should at least let you continue to pull those Jira issues into this sheet without this error.

However, I would also be interested to see if we can gather the complete JQL query here.  It looks like it is getting truncated by your screenshots here, so perhaps the great length of the query is a factor in the problem here.  Interested to try to learn more here.

Andy

[maganti.sreekar]

Hi Andy, thank you for your response!

I tried your approach but found that the same error was occurring in the JIRA advanced issue search as well.

While I know it isn't ideal to be asking for fixes to my search formatting, could you please help here?

Full query for your reference:

PROJECT IN (IDEA, PAID, SPR, DST, IN, NCED) AND TEXT ~ "\\\"Ads\\\" \\\"Advertising\\\"" AND ISSUETYPE IN ("Bug", "Channel Maintenance", "Development", "Documentation", "Epic", "Idea", "Improvement", "L3 Support", "Infrastructure", "New Feature", "Refactor", "Regression", "Story", "Requirements") AND STATUS IN ("Archive", "Backlog(Dev)", "Closed", "Closed/Duplicate", "Dev Blocked", "Dev Unit Test", "Duplicate", "Hibernated", "In Dev Review", "In Design", "In Design Review", "In Progress", "In Review", "Need Confirmation", "Need More Info", "Need Review", "Needs OneList Review", "Needs Product Review", "No Current Plan", "QA Blocked", "QA IN PROGRESS", "QA Rejected", "QA Verified(Main)", "Ready For Production", "Ready for Dev Test", "Ready for QA", "Requirement", "UAT", "UAT Pending", "UX Audit", "Under Review/On Hold", "Waiting For Build") ORDER BY cf[20023] ASC

Thanks,

Sreekar 

[Andy Heinzer]

Thanks for that info!  I can see now that you have a project key called "IN".  Since this is also a reserved JQL term, the parser in JQL is getting confused.  You need to surround this with either single or double quotes for this to work.

Take this first part of your query:

PROJECT IN (IDEA, PAID, SPR, DST, IN, NCED)

and change it to

PROJECT IN (IDEA, PAID, SPR, DST, "IN", NCED)

Then run the search.  Once you do that, you should then able to use that same JQL over in Sheets as well.

[maganti.sreekar]

Hi Andy, that worked for me.

Thanks for the help, appreciate it!

-Sreekar