Chart a Jira report in Looker Studio or a pivot table

The app’s tab starts with one line that says when the data was last updated, for example “Updated from Jira 13 Nov 21:05 UTC · 312 issues in the filter”. The column headers are in row 2, and the issues start in row 3. Most chart tools read the headers from the first row they are given.

The simple way: headers in row 1

In the report’s settings in Jira (More options), choose Row 1 of the tab: the headers. The app moves the headers up to row 1 at its next update, and the update time becomes a note on the Key header (hover over it to read it). Charts, pivot tables, filters and Looker Studio then read the whole tab with no range to type.

The app removes row 1 only when it holds nothing of yours. If you typed something there, the line in row 1 says which cell; move it, and the next update moves the headers up. Your formulas and charts move with the rows.

Or keep the update line, and start from row 2

The steps below keep the line in row 1 and give each tool the data from row 2 down.

Looker Studio

1. Add a data source: Google Sheets, then pick the spreadsheet and the report’s tab.
2. In Optional range, type the range from row 2, for example A2:Z. Use a last column far enough to the right to cover the app’s columns and any of your own you want in the chart.
3. Keep Use first row as headers ticked. The first row of the range is row 2, so the field names are the column headers.

New issues are added at the bottom of the tab, so an open-ended range such as A2:Z picks them up by itself.

A pivot table or a chart in Google Sheets

Select the data from row 2 down (click the cell in row 2 of the first column, then press Ctrl+Shift+End), then choose Insert → Pivot table or Insert → Chart. To include issues added later, change the range to an open-ended one, for example 'Report'!A2:Z.

Filters

Select row 2 and choose Data → Create a filter. The filter buttons appear on the headers, and row 1 stays outside the filter.

Count only what is in the filter

An issue that leaves the Jira filter keeps its row, marked “Not in filter since” and the date, so your notes on it are not lost. A chart then counts it too. Two ways to count only current issues:

• In the chart or pivot table, filter the In filter column to Yes.
• In the report’s settings in Jira (More options), choose When an issue leaves the filter: remove its row. Use this for tabs that only feed charts: a removed row takes any notes on it with it.