/
Combine Time in Status + Status Count in Google Sheets (Dynamic JSON Feed)

Combine Time in Status + Status Count in Google Sheets (Dynamic JSON Feed)

🤔 User Problem and Context:

  1. Stakeholders need a simple way to see which work item types spend the most time in each status, over a rolling time window, without manual CSV exports.

  2. Jira gadgets or ad-hoc queries aren’t giving a repeatable, shareable, and always-current view—especially for non-Jira users.

  3. Analysts want to group by Work Item Type, get a count of issues, and average status times, with an easy option to express results in working days.

🤔 User Problem and Context:

  1. Stakeholders need a simple way to see which work item types spend the most time in each status, over a rolling time window, without manual CSV exports.

  2. Jira gadgets or ad-hoc queries aren’t giving a repeatable, shareable, and always-current view—especially for non-Jira users.

  3. Analysts want to group by Work Item Type, get a count of issues, and average status times, with an easy option to express results in working days.

💡 Solution

1) Create and save matching presets (Time in Status)

  1. Open Time in Status.

  2. Configure the report (columns, filters, Work Items Period, Report Period, etc.).

  3. Save as Preset (for Time in Status).

  4. Repeat for Status Count with the same scope and columns.

image-20250912-132908.png

Keep it dynamic

  • Avoid hard date limits in JQL/Filters (e.g., “created in last 30 days”) if you want rolling updates.

  • Do use Work Items Period and Report Period (e.g., Last 30 days, Last month) to keep the JSON feed current.

2) Generate JSON Data Feeds

  1. Open each saved preset in Time in Status.

  2. Click Create JSON Data Feed.

  3. Copy each URL: one for Time in Status and one for Status Count.

image-20250912-134333.png
image-20250912-134346.png
image-20250912-134400.png

 

3) Bring the data into Google Sheets

  1. Create a new Google Sheet.

  2. Follow the app’s steps to fetch the JSON feed into a sheet.

  3. Repeat this procedure twice – once to create a table with the dynamic report Time in Status, and once for Status Count.

4) Join the two reports by Issue Key

Synchronise the data from both reports. Select one of them as the basis. In this case, we export the data to the Time in Status report.

  • Pull the matching Status Count from sc_raw using VLOOKUP (preferred):

    =arrayformula(iferror(vlookup(MATCH( $B$1:$B,sc_raw!$B1:$B,0),{(ROW(sc_raw!$B1:$B)),sc_raw!J1:J},2,0))) , where Sc_raw is a table with the Status Count report, B is the column with the data you are synchronising.
image-20250912-134804.png

You will end up with two columns: one with the time spent in the status, and the other with the number of times a specific task has been in that status.

Create another column to calculate the average time in status based on the number of transitions to this status using the formula:

IF(ISNUMBER(H2), H2/I2, ‘’), where H2 is the time in status, I2 is the number of transitions to status.

Repeat these steps for each status in the report.

image-20250912-134824.png

  • This yields the average hours per transition for that status, per issue.

  • If a count is zero/blank, the result is blank.

Repeat for each status you want to combine (e.g., To Do, In Progress, Review, Done).

Based on the results, you can build pivots, for example, to obtain summary data broken down by Work Item type.

image-20250912-134847.png

📈 Outcomes: A single Google Sheet that combines Time in Status + Status Count, calculates average time per transition, and powers pivoted views by Work Item/Issue Type—kept automatically up to date via JSON feeds.

📈 Outcomes: A single Google Sheet that combines Time in Status + Status Count, calculates average time per transition, and powers pivoted views by Work Item/Issue Type—kept automatically up to date via JSON feeds.

If you need help or want to ask questions, please contact SaaSJet Support or email us at support@saasjet.atlassian.net

Haven't used this add-on yet? Try it now

Request a demo call