Jira Weekly Status Report in Google Sheets (with JQL)

A weekly Jira status report in Google Sheets works best as four JQL imports (done, in progress, blocked, overdue), each on its own tab, plus a summary tab built with formulas and a notes tab keyed by issue key. Set the imports to refresh daily, check freshness before the meeting, and share the sheet as view-only with people who don’t use Jira.

Below are the exact queries, the formulas for the summary tab, and the layout that keeps notes and charts intact when the data refreshes.

What the report should answer

Stakeholders reading a status report want four answers, in this order:

1. What finished since last week?
2. What’s in progress, and who has it?
3. What’s stuck or at risk?
4. What’s overdue or newly added?

Each answer maps to one JQL query, and each query gets its own raw tab. A summary tab sits on top.

Tab layout

Tab	Contents	Who edits it
Summary	Counts, short tables, charts	Formulas only
Notes	Issue key in column A, your comment in column B	You
Done	Import: finished this week or last week	Nobody (import)
In progress	Import: open work in progress	Nobody (import)
Blocked	Import: blocked or flagged work	Nobody (import)
Overdue	Import: past due date, not done	Nobody (import)
History	One row of counts per week	Script or you

The rule that prevents most broken reports: never type into an import tab. Refreshes rewrite those rows and can reorder them.

The JQL for each tab

Replace ABC with your project key. Atlassian’s JQL docs now say “space” where they used to say “project”, but its documentation states there are no changes to existing JQL queries and suggests using the old term if a new one doesn’t work yet. project works on every site.

Test each query in Jira’s search first (Filters > Search work items, then switch to JQL), so you know the row counts.

Done this week

project = ABC AND statusCategory = Done AND resolved >= startOfWeek() ORDER BY resolved DESC

statusCategory = Done catches every status in the Done category, whatever your workflow calls it. resolved is the resolution date. If your team-managed or service spaces don’t set a resolution, match on the status change instead:

project = ABC AND status CHANGED TO Done DURING (startOfWeek(), now())

Use your workflow’s exact status name. The CHANGED operator works on status, assignee, priority, reporter, resolution and fix version only.

Done last week (for a Monday meeting)

project = ABC AND statusCategory = Done AND resolved >= startOfWeek("-1") AND resolved < startOfWeek()

startOfWeek() begins on Sunday by default, unless the site has the ISO 8601 date picker turned on in its look and feel settings. To start on Monday, Atlassian documents startOfWeek("+1d"). On a Sunday that expression points to the next day, so run Monday-based reports on weekdays.

In progress

project = ABC AND statusCategory = "In Progress" ORDER BY priority DESC, updated DESC

Blocked or flagged

project = ABC AND statusCategory != Done AND (status = Blocked OR Flagged is not EMPTY)

Use your workflow’s blocked status name. Flagged is a field on most Jira Software sites; if your site rejects it, drop that part.

Overdue

project = ABC AND duedate < now() AND statusCategory != Done ORDER BY duedate ASC

Stale work

project = ABC AND statusCategory = "In Progress" AND updated <= -7d

Anything in progress that nobody has touched for a week is worth a question in the meeting.

New this week

project = ABC AND created >= startOfWeek() ORDER BY created DESC

Current sprint only

Add AND sprint in openSprints() to any query above to limit it to the active sprint. For sprint, release, workload and aging queries beyond this report, see our JQL examples for reports.

Import the queries

Use whichever import method you trust; our guide to exporting Jira to Google Sheets compares them. With Atlassian’s free Jira Cloud for Sheets add-on:

1. Create the tabs from the layout above.
2. On each import tab, choose Extensions > Jira Cloud for Sheets > Open > Get data from Jira, paste the tab’s JQL, pick columns, and select Get data.
3. Use the same columns on every tab, in the same order: Key, Summary, Status, Assignee, Priority, Due date, Updated, Resolved, Parent.
4. Set each import to refresh daily from the sidebar’s schedule tab, early enough to finish before the meeting.

If you prefer formulas, =JIRA("your JQL", "summary,status,assignee,priority,duedate", 500) works in cell A1 of each tab. Since December 2024 the function takes three arguments, so four-argument examples from older posts may fail.

Build the Summary tab

Counts at the top. If each import tab has a header row and keys in column A:

Done this week:   =COUNTA(Done!A2:A)
In progress:      =COUNTA('In progress'!A2:A)
Blocked:          =COUNTA(Blocked!A2:A)
Overdue:          =COUNTA(Overdue!A2:A)

Work in progress by person, assuming Assignee is column D:

=QUERY('In progress'!A1:I, "select D, count(A) where A is not null group by D order by count(A) desc label count(A) 'Open items'", 1)

Clickable keys, so readers can open an issue (adjust the site name):

=HYPERLINK("https://your-site.atlassian.net/browse/" & Done!A2, Done!A2)

A short “Done” list for the top of the report, with your notes alongside. Put the list in A10, then put the notes formula in C10 and fill it down:

A10:  =FILTER(Done!A2:B, Done!A2:A <> "")
C10:  =IF(A10 = "", "", IFERROR(XLOOKUP(A10, Notes!A:A, Notes!B:B), ""))

Keep notes attached to the right issue

The most common complaint about Jira report sheets is notes and formulas ending up next to the wrong rows after a refresh. Imports can change row order, add rows and remove rows, and a comment typed in column J of the import tab stays where it is while the issues move.

Keep every manual comment on the Notes tab, one row per issue key, and bring it in with XLOOKUP as in the C10 formula above. The comment follows the key, wherever the key lands.

Track week-over-week trends

Imports overwrite themselves, so last week’s numbers disappear. A short Apps Script saves them. Open Extensions > Apps Script, paste this, and add a weekly time-driven trigger for Friday afternoon:

function snapshotCounts() {
  const ss = SpreadsheetApp.getActive();
  const counts = ss.getRange('Summary!B2:B5').getValues().map(function (r) { return r[0]; });
  ss.getSheetByName('History').appendRow([new Date()].concat(counts));
}

It appends the date plus the four counts from Summary!B2:B5 to the History tab. After a few weeks, a line chart on History shows whether blocked and overdue work is shrinking.

For service desks: SLA status

Jira Service Management sites get JQL functions for SLAs. Atlassian’s Sheets add-on shows each SLA as one number, positive while time remains and negative once breached (API-387), so split SLA states into tabs with JQL instead:

project = HELP AND "Time to resolution" = breached() AND resolved >= startOfWeek()
project = HELP AND "Time to resolution" = completed() AND "Time to resolution" != breached() AND resolved >= startOfWeek()
project = HELP AND "Time to resolution" < remaining("2h")

The first finds this week’s resolved requests whose most recent SLA cycle missed its goal, the second those that met it, and the third open requests that will breach within two hours. Use your own SLA names, and everBreached() if a request breached at any point. For met rates by priority and the goal and elapsed time of each request, see how to build a Jira SLA report in Google Sheets.

Make sure the numbers are fresh

A status report is only useful if the data is from today. Before the meeting:

1. Check the newest value in each tab’s Updated column. On an active project, if it’s more than a day old, the import didn’t run.
2. If a scheduled refresh failed, run it manually and see our Jira Cloud for Sheets troubleshooting guide. Scheduled updates that silently don’t run are a known, open bug in Atlassian’s tracker.
3. Add a “Newest change in this data” line to the Summary tab with =MAX(Done!G2:G, 'In progress'!G2:G), pointing at your Updated columns, so readers can see it too. This works when the Updated column imports as dates; if it arrives as text, convert it first.

Share it safely

If you use =JIRA() formulas, every query runs through the sheet owner’s Jira account, and anyone who can edit the sheet can write new queries with that access. Share the report as Viewer with stakeholders, and keep the editor list to the people who maintain it.

Where Rowduet fits

Rowduet (in development) is a Google Sheets add-on being built for reports like this one: JQL imports with every field you report on, hourly or daily refresh, and an email when a refresh fails, so a stale number never reaches the Monday meeting. It isn’t available yet. If you build this report every week, you can join early access.

When the meeting turns into decisions, such as new assignees, priorities or due dates, see how to bulk update Jira issues from a spreadsheet.