« Previous

Using Google Docs to master your task list

Published on 13/07/11
by Steve

Tracking tasks in multi-platform, gadget-overloaded environment is a challenging activity. There is my desktop computer at work, the company notebook I use when meeting with clients, the Blackberry that (unfortunately) is always with me and my private MacBook Pro and iMac. See where this is going? With a plethora of different systems come many different tools to collect tasks and take notes with.  How the heck do I track tasks and responsibilities effectively in that environment?

It is obvious that a fat client does not do the job, unless there exists a client for each of the platforms. Needless to say that I also own an iPhone. So, unless you can recommend a usable cross-platform task tracking utility this does not seem to be an option. What’s left then? The least common denominator: the Web.

Apparently, there is one universal tool that can do all that and is available for virtually all devices and platforms, sometimes even for free. For me it all comes down to using a Google Spreadsheet as a To-Do list. Augmented with some scripting it turned out to be quite useful, supporting automated sorting and coloring.

Preparations

To be able to track tasks in a sensible manner we need to define a couple of columns:

• Date Created: when did the task end up in your inbox.
• Subject: what needs to be done.
• Due to: when does it need to be done.
• Remind me on: when do you want to be reminded that the task is due. This column is especially useful for delegated tasks
• Who: who has the task been delegated to.
• Status: is it open, are you waiting for someone else, is it done?
• Notes: could contain the date of a call you did regarding the task.

You may want to reduce or expand the list depending no your needs.

If you do not have a Google Account create one. Next, create a Spreadsheet document, type the names of the previously defined columns in the first row. To make changing the status easy I’ve created a second sheet and named it “Lists”. It basically holds various status values prefixed by a number to allow sorting: 01 open, 02, in progress, 03 waiting, 04 postponed, 09 done.

My status values for my tasks

To make those values available in our to do list, select all rows in that column and choose “Validation” from the “Data” menu.

Select “Items from list” from the criteria dropdown list and click the “Select a range” button. While in select mode, navigate to the Lists sheet, select the 5 status values, return to the to do list and save the configuration. Now, you’ll be able to select a status from a dropdown list. That was easy right?

Automated Sorting and Coloring

As a visual being I find it extremely helpful to use colors for certain states a task can have. The following picture shows a sample task list with each state having associated a color. And it’s sorted, btw.

My colorized to do list

Sorting

Let’s start with sorting the to do list. It is possible to trigger a sort on behalf of some arbitrary action, say, on entering or changing a value in column 2. I am doing that for coloring my task list. Every time the status is changed a script colorizes the current row. So, for sorting, you’ll have to do it manually. But don’t be afraid, I have a script for that as well, which allows you to sort a range by simply selecting a custom menu item.

How does it work? All we have to do is to do is insert the following JavaScript methods using Google Docs’ integrated script gallery (see the “Tools” menu):

function onOpen() {
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "Sort Selection", functionName: "sortSelection"}];
  s.addMenu("Sort Selection", menuEntries);
}
 
function sortSelection() {
  var rangeToSort = SpreadsheetApp.getActiveRange();
  sortIt(rangeToSort);
}
 
function sortIt(range) {
  range.sort([
    {column: 6, ascending: true},
    {column: 5, ascending: false}
  ]);
}

The method onOpen() creates a new menu in the toolbar only for this spreadsheet that when clicked sorts the current selection. That’s pretty much all there is to it. It is worth mentioning, though, that you have to configure the sortIt() method to allow the script to sort by the correct columns. In my case, column 5 represents the status column and 6 is the due date.

Colorizing Tasks by Status

As with sorting we will have to employ some JavaScript to do the job for us. Unlike the previous approach the colorRow() method will be called on each edit, checking the value in the status column and colorizing the row accordingly.

/**
 * Applies coloring rules to all rows of the active sheet.
 * Called by onOpen().
 */
function colorAll() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var endRow = sheet.getLastRow();
 
  for (var r = startRow; r

The methods are used for two different scenarios. The first is that we want the to do list to be sorted whenever the spreadsheet is opened (that’s what onOpen() is for). The second scenario handles the case when the status changes (onEdit() is the handler of that event).

These methods have one drawback: once you change the structure of your sheet or the values of your status list you’ll have to adjust the methods to reflect the changes, otherwise it may not work correctly.

Additionally, there seems to be a bug in Google’s scripting engine preventing a more thoughtful design instead of nesting if-else statements, but I was not able to get it to work with anonymous methods, case statements and associative array. If you have a refactored solution, I’d be happy to feature it on this site.

Conclusion

Using Google docs for my to do has been a huge relief. No more worries about syncing to do list to different clients. It’s available every time, everywhere. The JavaScript part is what might get a bit difficult, but once you have set it up successfully you won’t have to touch it very often.

Please feel free to share your thoughts, ideas and improvements in the comment section.

Trackback URL: Using Google Docs to master your task list