=JIRA() Function Syntax in Google Sheets to update data by specific key in existing spreadsheet

[Eric Peterson]

I have a large existing Spreadsheet, maintained for years.

I would like to have a few fields automatically update according to data found in JIRA.

I have successfully activated the =JIRA)() function in this google sheet. 

I have successfully updated gotten a single field to update directly from JIRA based on the key found in another existing column. 

=JIRA("key = "&$A2&"","assignee")

I place this code in cell B1 and it successfully reads my existing text key in column A2 and displays the name of the person found in JIRA assigned that ticket into cell B2.  

But how do I get it to work as an array, a loop that reads all the Keys in the cells of Column A and places the extracted JIRA data into all the adjacent cells in Column B?  

Assuming I could find a way to make a loop or array, I attempted another test by placing the following code into cell B2, directly below the working test code.

=JIRA("key = "&$A3&"","assignee")

This caused a cell B1 ERROR of #REF with the note "Array result was not expanded because it would overwrite data in B2." even though the cell B3 did populate properly with the correct JIRA data.  It seems that when using this code, it places the name of the JIRA field being called into the cell of the query, and places the JIRA data being requested into the cell below.  Since I put the new code in the cell below, it blocks the code above it since this query returns 2 cells of data.  This is not what i want.

Can I get it to return the JIRA data into the same cell where the code is at, and without returning the field name in any cell?   

OR is there a different syntax that will allow me to read  A2-A10 and return the JIRA field data into the adjacent cells of column B, using the keys found in the A column?

The context value for this solution is allow users to only work in JIRA, updating fields on their tickets, without ever having to go outside JIRA into Spreadsheet, but still having the spreadsheet have a few JIRA fields of interest automatically remain updated when JIRA data changes.   

Can anyone help get me the answer or at least some directional ideas to succeed at this? Thanks.


UPDATE:  I created a solution.  I used a the goggle sheet's JOIN function to input the range / array of keys as issuekeys, then used the JQL "IN" function to read and act on all of the keys in that array order.   It does read and return the correct data, but not in the correct order.  Here is the code i have so far:

=JIRA("issuekey in ('" & JOIN("', '", A2:A10) & "')", "assignee")

The problem is when using this specific code, the JIRA comes back into my sheet cells in the wrong order, not aligning or matching to the keys that the Data must match.  I verified that JIRA is force ordering the data return according to the descending order of the alphanumeric value of the keys in the range submitted.  This is bad for my use case, and of no value to us.  

UPDATE 2:  I found another solution which is working for me despite the JIRA Bug, so Ill share it here for anyone who may feel this solves this for them as well.

Solution: 
1. I imported the fields I wanted from JIRA into a new blank google sheet tab.  
2. In the main sheet where I wanted JIRA data to update automatically, I placed vlookup to simply find and copy the JIRA field data I want into those cells.   

Since I already had a matching Key (the JIRA Ticket IssueKey) in a column in the main spreadsheet, there was no problem updated all the fields I needed from JIRA into our manually organize master sheet.  I'll set it to autoupdate on an schedule, and hopefully the vlookups should ensure we are up to date, removing the manual update of those fields, since they were already updated in JIRA by their users.

It was less elegant, and certainly didn't take much code, so I imagine others will likely adopt this solution for their similar use case, until Atlassian can fix their bug.  Enjoy!~