Using Google Docs for Issue Tracking

In all software or web site development projects you need some process to keep track of issues, i.e. feature requests, to-dos and bugs. Often it happens by email, but since the amount of issues in a small project already can be overwhelming, it'll be close to impossible to remember what has been requested. Even though some say "read them, throw them away, and forget them.", I still feel that keeping track of them helps you to see where you stand.

Bug Tracking Software

Usually, the first step to organize up-coming tasks is to write them down in an Excel sheet. But after having experienced lock-outs and lost macros when someone saved the file with Open Office I know that this system doesn't really scale for team work. And try to share it with the customers!

At the other extreme there are a lot on-premise issue tracking systems, such as JIRA, and even more complex Software change and configuration management (SCCM) tools. Somewhere on the middle ground lie simple web-based tools like Basecamp and Pivotal Tracker. The latter service is for free and with the former you get started with at $24/month. Both offer similar feature sets that are adequate for projects of all sizes. Then there is yet another online service: Google Spreadsheets.

Bug Tracking using Google Spreadsheets

Google Spreadsheets is an Excel-like program that comes as a part of Google Docs, a free web-based office suite. It has two good characteristics that make it easy to adopt. First, it's simple. The user interface is familiar for everyone who has ever used Excel. Besides, its limited functionality makes it less cluttered than Excel or OpenOffice. Still, it let's you write complex calculations and present graphs. Second, by sharing all the data in real time it makes collaboration extremely simple - all you need to do is to create a new document and invite people. No need to pass an Excel file around as an email attachment, or to worry about version compatibility.

Its ease of use, configurability and online collaboration make it a perfect solution for small projects and dispersed virtual teams. It's easy to customize for the needs of each project: sometimes you need only issue summary and status, and then sometimes you need to keep track of versions, milestones, hours and so on. It's easy to adapt for communicating with clients' project members, since it's easy to introduce also to clients' non-technical personnel.

A few people have explained how Google Docs can be leveraged:

In addition, there are some public (probably many more private) projects like TEDTalks that use Google Docs for reporting bugs. I'll build on top these older articles and explain few new tricks.

Bug Tracking Spreadsheet Template

Here's a sample issue tracking spreadsheet:

Google Spreadsheets issue tracking sample

Up in the header there are some statistics about the issues and below there's a long list of issues with their essential properties. In this sample the issues have the following attributes: Id, Type, Priority, Milestone, Estimated hours, Status, Target completion date, Summary and Notes & Testing results. Naturally, all of the attributes can customized based on the needs of your project - you can see that the projects that I linked to above have other attributes, like Operating system, Browser version and What were you doing. Id property is important for ensuring that you talk about the same issue in emails or in more detail in Google Documents.

The interesting part here is in the header: Completion rate and Billable hours per milestone and month. That's pretty primitive compared to the reports generated by a complex issue tracking software, but it should help you to report project status and send your invoices. Of course, this can be easily enchanced with further calculations and graphs. Anyway, there's a bit of work in getting those numbers out of the data, so let's have a closer look at the formulas.

Estimated working hours


The formula sums estimated working hours in F column in case that milestone in E column matches given version.

Completed hours

=(ARRAYFORMULA(SUM(($E7:$E86=G1)*($G7:$G86<>"New")*($G7:$G86<>"Open")*$F7:$F86)) / G2 )

This formula operates on arrays and consequently requires the calculations to be wrapped into an arrayformula function (comparable to Excel Ctrl+Shift+Enter function). It sums hours in F column in case version is correct AND status is not "new" OR "open". Note that the calculation is based on the way Spreadsheets evaluates comparisons: failure = 0; success = 1 (0 * 1 * 1 * 4 = 0). Finally the sum is divided by total hours to gt a percentage.

Billable hours

=ARRAYFORMULA(SUM(($E7:$E186=G1)*(ISNUMBER(SEARCH("request", $C7:$C186)))*$F7:$F186))

The formula to calculate billable hours is similar to calculating completed hours, but with one trick: search function returns FALSE if there was no match and then isnumber turns this into either 0 or 1.

Highlighting cells based on value

To make the spreadsheet easy to scan you can color some cells. There's no function to do that, but instead you can apply formatting rules by right-clicking a selected cell-range. Then you can build your own rules to match cell content as shown below:

Google Spreadsheets changing cell background color based on cell value


Based on this article you should be able to build your own issue/bug tracking system. You can have a look at my Google Spreadsheet issue tracking sample (view only, no login required). This kind of document is good for sharing among trusted invitees. If you want to open it for the public, you might want to consider keeping document itself private, and to build a web form enabling users to only commit issues without letting them to change them. Have a look at the form below by TEDTalks for inspiration. Plus, you could theme the form as you wish, or to integrate the form in to your website or to even integrate the whole spreadsheet.