Gsheet Rollup App
- Rino Jose
The Gsheet Rollup app rolls tickets up by manager for your organizations using data from a Google Spreadsheet. You can do a simple counts rollup, or you can customize the apps to compute other statistics like story points, ticket age, OOSLA, or anything else you can imagine.
Getting Started
Start by clicking the blue plus button and selecting the "Gsheet Rollup" app.
Click "Create" and then add the name of your rollup.
By default, the app will pull all tickets updated in the past week. It will slice them up by status, and then attempt to roll them up by manager. Since it won't any org data yet, it will roll tickets up to whoever's logged in.
Adding Org Data
The first step is to create a Google Spreadsheet. Next, add a "user" and a "manager" heading.
For each user that you want to include in your report, add a row with the user's Jira Display Name and their manager's Display Name (if you have lots of users, you'll probably want to generate this from your HR system):
Now, click the edit control to bring up the app editor.
Don't worry about this format--we'll go over this as we tweak the app. To pull the org data from your spreadsheet, copy its URL and then paste it into the ORG-GSHEET-URL definition:
To pull fresh data, click the refresh control.
The first time you pull gsheet data, you'll see a window that asks for you to authorize this. Once you grant authorization, the data pull will begin and you'll see the breadcrumbs at the top of the report update to reflect the management chain for the logged in user.
Click "Play" to run the app again. Note how the breadcrumbs at the top reflect the management chain for the logged in user.
Drilling up and down
Drill up by clicking on one of the user breadcrumb items:
Notice how the logged in user's tickets roll up into their manager's report and how the other reports' data also show up here. If the manager also has tickets assigned, they are shown here, too.
You can see the tickets making up each cell or pie slice by clicking on them.
Tweaking the report
Adding fields to the Ticket Details
We can add more columns in the ticket details, by adding them to the FIELDS definition. For instance, to add the created date, add "Created":
We'll need to click "refresh" instead of play because we're pulling new data from jira.
Changing Pie Chart Colors
If you'd like to change the pie chart colors, feel free to use your own colors in the COLORS-BY-GROUP definition. You can either pick colors from Atlassian’s color tokens, or you can just use regular HTML hex colors:
Rolling up by story points
In addition to rolling up issues by count, you can also roll them up by other statistics. The Basic Rollup has an example of how to roll up by story points. In this case, just uncomment out the “Story Points” line in ROLLUP-CONFIGS:
Advanced customization
Once you get comfortable with Forthic, you can customize these reports even further. If we wanted to show the average age of issues, we could use the "Created" field we pulled earlier and then write some definitions to compute this statistic:
: FORMAT-NUMBER 10 * ROUND 10 /;
: AVG-DAYS-OLD "'Created' REC@ >DATE TODAY SWAP SUBTRACT-DATES" MAP MEAN FORMAT-NUMBER;
And you can go even further, customizing how tickets are sliced, doing this for other issue fields like Priority, or even computing your own values dynamically like SLAs that vary based on priority or project.
Publishing Your Report
To save your changes, click the "Save" button.
To publish your report so you can share it with others, Click on the Settings gear to bring up the app’s settings page.
Click on the publish slider and then “Update”:
Customized URLs
The URLs for these reports include the manager being viewed, so you can send custom reports to each manager. This is super helpful because you can highlight what needs attention and tailor to each manager.
There are many more ways to customize these reports.
Getting Help
If you need help, feel free to swing by Forthic Office Hours, or file a ticket.