Issue Tracking with Google Docs

It's been a lot of time without a new entry. Sometimes we have the need of tracking issues between the members of a small development team. I know there are a lot of online tools designed to perform this task. Even if they are free or paid some license, we have to adapt to their rules so there is consistency in the displayed data. it may happen that the data to be tracked is so specific that it doesn't fit with the model proposed by the most of these online tools, and the agility of the information requires to see everyone in real time. This is not the mainstream case but it's a possibility and it should be taken into account for small tasks.

Another goal for this post is to bring to the light the new features that Google is offering with the online Docs, in this case the Spread Sheet, these are online collaborative work tools that allow several users to edit the same document at same time. what if we can extend this behavior by adding our own scripts and react to specific spread sheet events? as I am talking about issues tracking, the potential users of this are developers! and as a developer I became very happy when I discovered I could do it. It reminds me someway to VBA in Microsoft Office, but saving the distances, of course.

Yes we can add our scripts to Google Docs, for more information about this scripting language see this page https://developers.google.com/apps-script/ Back to our example with the simple issue tracker, scenario: we have a team of four developer that receives the bug list every day and must have up to date the status as closer as possible to real time. Colors for the rows is good for know at glance the status (i.e Red for some issues that are blocked for some reason) and we also want that those blocked or waiting for external information to be at top of this spreadsheet, as well as those already closed or assigned to others to be at bottom.

As a plus, we want also to take advantage of the data validation for the columns such as the developer assigned to the task and the status of the issue, so for solving this we have second sheet (it might be in the same sheet but we don't want to see the list of status nor the developers) where we place those lists and refer to them in the data validation form.

The same procedure for the list of developers. When go to the Tools menu option Script Editor... then get the following dialog

Then choose Spreadsheet and we are ready to start coding, even have some kind of intellisense when typing Ctrl+Space bar, the Help menu is also available and the pages have a lot of documentation with examples.
In this example it has been used the function onEdit which is executed by the spreadsheet after any cell is edited.

The code is quite simple, it determines the data range, loops through every row and set color according to status, cell to cell in the same row, then sorts this range using as criteria the column D values from Z to A so conveniently chosen the status names as we wanted they to be displayed. Here's the whole code

function onEdit(event)
{
 
  var sheet = SpreadsheetApp.getActiveSheet();
  // just do nothing if not in the right sheet
  if (sheet.getName() != "Main") return;  
  
  var range = sheet.getDataRange();
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  var values = range.getValues();
  
  // determine the actual amount of data rows
  var actualDataRows = numRows;
  for (var i = 1; i < numRows; i++) {
    if (values[i][0] == "") { // Column A
      actualDataRows = i-1;
      break;
    } 
  }

  // for each data row set background color according to status
  for (var i = 1; i < actualDataRows; i++) {
    var cell = values[i][3]; // Column D
    var color = "";
    switch (cell) {
      case "Waiting": 
        color = "#ea9999";
        break;
        
      case "Closed": 
        color = "#cccccc";
        break;
        
      case "In Progress": 
        color = "#6D9EEB";
        break;
        
      case "Resolved": 
        color = "#B6D7A8";
        break;
        
      case "Cannot Reproduce": 
        color = "#FFD966";
        break;
        
      case "Assigned to Others": 
        color = "#E69138";
        break;

      default:
        color = "#ffffff";
        break;
    }    
    
    if (color != "") {
      // getRange(row, column, optNumRows, optNumColumns)
      var row = sheet.getRange(i+1, 1, 1, numCols);
      row.setBackgroundColor(color);
    }
  }
  
  // sort after having changed something
  var tosort = sheet.getRange(2, 1, actualDataRows-1, numCols);
  tosort.sort({column: 4, ascending: false});
  tosort.setBorder(true, true, true, true, true, true);

}

The sample spreadsheet can be found at this link, it's public so anyone can play with it. I hope this text has been useful and encourage you to dig into these scripts capabilities.