How do I return only the data and not the column heading in a JQL =jira() call in google sheets?

[Morgan Hall]

I'm using a simple jql call against my project to return a single row result in google sheets.  Unfortunately, it returns the column heading (or label) and the row of data. 

=JIRA("project=""myproject"" AND ""Checklist Text"" ~ ""foo""", "issuekey",0,1)

Instead of populating my cell with the data I want, it returns:

Key

myproject123

Where Key is the header of the issuekey column and the next row is the data with the key to the ticket in Jira.

I've tried adjusting the offset, thinking that the Key is row 0 or row 1 of the returned data, but that doesn't seem to work. I just eliminate the data if I use an offset other than 0.

This overwrites the cell below my target which I don't want to happen.

Short of my own method to trim the label, is there a flag/switch that I'm missing to hide the heading/column name?

Thanks.

[Marc - Devoteam · Accepted solution]

HI @Morgan Hall 

I do assume you have access via the API and get results, but you want to show only the results of the JQL and not a header row?

I don't think this is possible, as Jira users a header row.

Option could be to have another tab in your sheet and convert the information to your desire, use the JQL as source.

But then I'm, done as I'm not a sheet/excel master.

[Morgan Hall]

That seems to be the case.  Even an empty data set returns the header row.  I was trying to avoid importing everything into a sheet and doing a lookup between sheets, but that is another route I've considered.

Thanks for the feedback.

[Chris Branson]

Nest your jira() call in chooserows()

=chooserows(jira("your expression"), 2, 3) will return rows 2 and 3.

The only issue is its not dynamic... but I'd assume there is probably another function you use in a similar way that would only trim the first row?

[Morgan Hall]

This is not the best code, but it does the job.  Probably should rename the method to make it clear that it only returns the first row of data, but this does work for my needs when I'm only requesting a single row from Jira to begin with.

/**

 * Trimming function for eliminating a header row from a multidimensional array result.

 * CAUTION: Only returns the first row of data as currently implemented.

 * @Param - an array

 */

function trimHeader(param) {

  Logger.log(param);

  result = [];

  result[0] = param[1];

  Logger.log(result);

  return result

}

[Dick]

Hi @Morgan Hall , Welcome to the Atlassian Community

Contact between Google Sheets and Jira will go through API (under the hood). If your API call is not using credentials that are known to Jira, no data will be displayed.

You only get what the account may see. 

Dick

By voting for helpful posts and marking answers to your question, you're helping people with similar questions find a solution more quickly. Sharing is caring applies to knowledge as well :)

[Morgan Hall]

I think my choice to show MyData for my example rather than a more realistic data set sent you down the wrong path.  I'm getting data returned from my query. It's just a case of not wanting the header row that is returned with that data.

[Dick]

Ah, understood. Thanks for setting me straight in such kind manner 

:)