The Scenario
It is Tuesday morning. The engineering director is about to allocate two new features across the team for Q2 — but she has no idea who is already overloaded. Open issues are scattered across three Jira projects: WEB, API, and INFRA. There is no single view that shows per-developer open issue counts without clicking into each project separately.
The bad version:
- Open Jira, navigate to project WEB, filter by assignee, click the first developer's name.
- Note the open issue count, switch to a sheet, type it in.
- Repeat for API, repeat for INFRA, multiply by the number of developers on the team.
- Realize two developers share a display name variation in different projects and your count for "Alex" is wrong.
Getting a clean workload snapshot across three projects for twelve developers is a thirty-minute exercise before you can even start the allocation conversation.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet. It reads the sheet and through the Jira integration queries all three projects in one pass and groups the results by assignee.
Search Jira for all open issues in projects WEB, API, and INFRA, group them by assignee, and write a summary table into my sheet with columns: assignee name, project, open issue count.
What You Get
- One row per assignee-project combination, showing the open issue count for that person in that project.
- A clean table: assignee name, project key, count.
- Unassigned issues appear under an 'Unassigned' row so you can see how many tickets have no owner.
- The result is ready to sort or pivot immediately.
What If the Data Is Not Quite Ready
You have the developer emails in column A and want counts next to each name
The Team Capacity sheet already lists the twelve engineers. You just need the open issue counts filled in.
For each developer email in column A of my Team Capacity sheet, fetch their open Jira issue count across all projects and write the count into column B.
You want a breakdown by project within each developer's row
Instead of one row per assignee-project combination, you want one row per developer with separate columns for WEB, API, and INFRA counts.
Search Jira for all open issues in projects WEB, API, and INFRA. For each unique assignee, write one row in my sheet with columns: assignee name, WEB count, API count, INFRA count, total.
Some developers appear under different display names in different projects
Two developers have display name variations between projects. You want them merged under their email address.
Search Jira for all open issues in projects WEB, API, and INFRA grouped by assignee email (not display name). Write a summary table with columns: email, display name, project, open issue count.
Kill chain: fetch workload, flag anyone over 15 issues, and sort by total descending
Search Jira for all open issues across projects WEB, API, and INFRA. Group by assignee and write a row per person with their total open issue count across all three projects. Sort the rows by total descending so the most overloaded people are at the top. In column C, flag anyone with more than 15 open issues with 'At capacity'.
One prompt builds the sorted, flagged capacity view ready to share in the allocation meeting.
Try It
Get the 7-day free trial of SheetXAI and open your team capacity Google Sheet before the next resource allocation session, then ask it to pull open issue counts per developer across all your active Jira projects. See also: how to build a cross-project executive issue report and how to pull sprint velocity into a sheet.