Jira Time in Status in Google Sheets, Without a Plugin

Jira Cloud doesn’t report how long each work item spent in each status, so time in status in Google Sheets has to come from the status history: get every status change with its timestamp, then subtract consecutive timestamps. A free Apps Script can pull that history for hundreds of items through Jira’s bulk changelog endpoint and write one row per status visit. A QUERY formula then gives average hours per status, and two lookups give cycle time per item.

Below: what Jira already shows (often enough for a retro), the JQL that finds stuck work without any export, the script, and the formulas. Facts are as of October 2026.

What Jira shows natively

Per-item time in status has been requested for a decade. Atlassian’s suggestion JRACLOUD-90221 was created in May 2016 and is still “Gathering Interest” with 243 votes. Here’s what you get instead:

Tool	Where	What it shows	Limits
Control chart	Reports, company-managed spaces	Elapsed time in the statuses you pick, per item and as a rolling average	Company-managed only
Cumulative flow diagram	Reports	Items per board column over time; a widening band marks a bottleneck	No per-item numbers
Days in column	Card settings, company-managed boards	Dots on each card for days in its current column, cumulative across visits	On the board only; on by default for kanban, off for scrum
statusCategoryChangedDate	JQL	When an item last changed status category	Category, not status; Cloud only

If you need one number for a retro, the control chart is the quickest. Atlassian’s docs suggest removing outliers by labeling them and filtering with a quick filter, which is worth doing before you quote an average.

Skip the [CHART] Time in Status column

Full CSV exports can include a column called “[CHART] Time in Status” with values such as 3_*:*_1_*:*_0_*|*_10003_*:*_1_*:*_40208. It’s tempting to parse. Don’t build on it:

• It’s an internal field for the Average Time in Status dashboard gadget. In June 2025 Atlassian added a warning to the custom field creation screen saying the field isn’t shown on work items because it’s designed for that gadget (JRACLOUD-71384).
• Community members who decoded it disagree on whether the durations are seconds or milliseconds, and one reported that it only updated when the gadget was drawn.

The status history is the reliable source.

Find stuck work with JQL first

Before measuring anything, these queries answer “what’s stuck right now?” without an export.

In progress for two weeks or more, as a category:

project = ABC AND statusCategory = "In Progress" AND statusCategoryChangedDate <= -14d

In one status for three days or more:

project = ABC AND status = "In Review" AND NOT status CHANGED AFTER startOfDay("-3")

Sent back from review this month, a common source of hidden rework:

project = ABC AND status CHANGED FROM "In Review" TO "In Progress" AFTER startOfMonth()

Our JQL examples for reports has more aging and flow queries.

Decide what you’re measuring

Three different numbers get called “time in status”, and they answer different questions:

• Time in status: hours an item spent in one status, summed across visits. It shows where work waits.
• Cycle time: from the first move into an in-progress status to the last move into Done. It shows how long work takes once someone starts it.
• Lead time: from creation to Done. It shows how long a requester waits, backlog included.

Write your definition on the report, including which statuses count as started and finished. Two teams quoting “cycle time” from different definitions can be weeks apart, and nobody notices until the numbers are compared.

Free add-ons that calculate it

• Jira Cloud for Sheets (Atlassian) can import changelog fields, but API-768, with 30 votes, reports that it can’t fetch all changelogs once an item has 40 or more. Long-running items, the ones you most want to see, are the ones that get cut off.
• Jira Data Fetcher for Google Sheets is free; its listing describes time in status with business-hours metrics, changelog data and a daily, weekly or monthly schedule. It had 488 installs in October 2026.
• Project Aid for Jira is free with 308K+ installs; its listing offers a changelog report, and you calculate the durations.

We compare these and paid import tools in Jira Cloud for Sheets alternatives.

Pull the status history with Apps Script

The script finds work items with JQL, then asks POST /rest/api/3/changelog/bulkfetch for their status changes, up to 1,000 items per request, filtered to the status field. Why not the changelog option on search? Atlassian’s API reference says search returns at most 40 changelog entries per item and points to bulk fetch for more, the same 40-entry threshold that API-768 describes.

Setup:

1. Create an API token at id.atlassian.com/manage-profile/security/api-tokens with Create API token (not the scoped option, so the script can call your site URL). New tokens expire after at most a year.
2. In your sheet, open Extensions > Apps Script. Under Project Settings, add script properties JIRA_SITE (like your-site.atlassian.net), JIRA_EMAIL and JIRA_TOKEN. Anyone who can edit the sheet can read them.
3. Paste the script, change the JQL, and run importStatusTimes once to authorize it.
4. Under Triggers, add a daily or weekly time-driven trigger.

function importStatusTimes() {
  const props = PropertiesService.getScriptProperties();
  const site = props.getProperty('JIRA_SITE');
  const auth = 'Basic ' + Utilities.base64Encode(
    props.getProperty('JIRA_EMAIL') + ':' + props.getProperty('JIRA_TOKEN'));
  const jql = 'project = ABC AND (resolved >= -90d OR statusCategory != Done)';

  const items = {};
  const issues = searchIssues(site, auth, jql, ['created', 'status']);
  issues.forEach(function (i) {
    items[i.id] = { key: i.key, created: i.fields.created, status: i.fields.status.name, changes: [] };
  });
  for (let n = 0; n < issues.length; n += 1000) {
    const body = { issueIdsOrKeys: issues.slice(n, n + 1000).map(function (i) { return i.key; }),
      fieldIds: ['status'], maxResults: 1000 };
    do {
      const data = callJira(site, auth, '/rest/api/3/changelog/bulkfetch', body);
      data.issueChangeLogs.forEach(function (log) {
        log.changeHistories.forEach(function (h) {
          h.items.forEach(function (it) {
            if (it.fieldId === 'status' && items[log.issueId]) {
              items[log.issueId].changes.push({ at: new Date(h.created), from: it.fromString, to: it.toString });
            }
          });
        });
      });
      body.nextPageToken = data.nextPageToken;
    } while (body.nextPageToken);
  }

  const rows = [['Key', 'Status', 'Entered', 'Left', 'Hours']];
  const now = new Date();
  Object.keys(items).forEach(function (id) {
    const item = items[id];
    item.changes.sort(function (a, b) { return a.at - b.at; });
    let status = item.changes.length ? item.changes[0].from : item.status;
    let entered = new Date(item.created);
    item.changes.forEach(function (c) {
      rows.push([item.key, status, entered, c.at, (c.at - entered) / 36e5]);
      status = c.to;
      entered = c.at;
    });
    rows.push([item.key, status, entered, '', (now - entered) / 36e5]);
  });
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName('Status time') || ss.insertSheet('Status time');
  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}

function searchIssues(site, auth, jql, fields) {
  const issues = [];
  let token = null;
  do {
    let path = '/rest/api/3/search/jql?maxResults=100&fields=' + fields.join(',') +
      '&jql=' + encodeURIComponent(jql);
    if (token) path += '&nextPageToken=' + encodeURIComponent(token);
    const data = callJira(site, auth, path);
    Array.prototype.push.apply(issues, data.issues);
    token = data.nextPageToken;
  } while (token);
  return issues;
}

function callJira(site, auth, path, body) {
  const options = { headers: { Authorization: auth, Accept: 'application/json' },
    muteHttpExceptions: true };
  if (body) {
    options.method = 'post';
    options.contentType = 'application/json';
    options.payload = JSON.stringify(body);
  }
  const res = UrlFetchApp.fetch('https://' + site + path, options);
  if (res.getResponseCode() !== 200) {
    throw new Error('Jira returned ' + res.getResponseCode() + ': ' +
      res.getContentText().slice(0, 300));
  }
  return JSON.parse(res.getContentText());
}

Each row is one visit to a status: when the item entered it, when it left, and the hours in between. The first status runs from the item’s creation date. A blank Left cell means the item is still there, with hours counted up to the run time.

Turn visits into a report

Average hours per status, counting finished visits only:

=QUERY('Status time'!A:E, "select B, avg(E), count(A) where D is not null group by B order by avg(E) desc label avg(E) 'Avg hours', count(A) 'Visits'", 1)

Total hours per item in each status, as a grid:

=QUERY('Status time'!A:E, "select A, sum(E) where A is not null group by A pivot B", 1)

Cycle time per item, on a tab called Cycle. Put =UNIQUE('Status time'!A2:A) in A2, then these in row 2 and fill down:

B2 (started):  =MINIFS('Status time'!C:C, 'Status time'!A:A, A2, 'Status time'!B:B, "In Progress")
C2 (finished): =MAXIFS('Status time'!C:C, 'Status time'!A:A, A2, 'Status time'!B:B, "Done")
D2 (days):     =IF(OR(B2 = 0, C2 = 0), "", C2 - B2)
E2 (workdays): =IF(D2 = "", "", NETWORKDAYS(B2, C2))

Change the status names to match your workflow. NETWORKDAYS counts whole weekdays including the start and end days, so a same-day item counts as 1. For forecasting, the 85th percentile is more useful than the average: =PERCENTILE(FILTER(Cycle!D2:D, Cycle!D2:D <> ""), 0.85) gives the number of days within which 85% of items finished.

Pitfalls

• Renamed statuses. Changelog rows keep the name a status had when the change happened. Map old names to new ones before you average.
• Items moved between spaces. Their history includes another workflow’s statuses.
• Calendar time. The script counts every hour, nights and weekends included. Use the workdays column, or treat the hours as calendar hours and say so.
• Scope. The sample JQL takes items resolved in the last 90 days plus everything open. Widen it carefully: each run must finish within Google’s 6-minute limit, and Jira can refuse bursts of requests.

For service desks, SLA clocks already measure response and resolution time with calendars and pauses; see our Jira SLA report guide instead of rebuilding that here.

Where Rowduet fits

Rowduet (in development) is a Google Sheets add-on being built to import time in status alongside your other Jira fields, refresh on an hourly or daily schedule, and email you when a refresh fails. It isn’t available yet. If cycle time is a number you report every sprint, you can join early access.