Dec 26, 2010

Google Spreadsheet + Apps Script = Best Task System Ever

Probably it's same for a lot of people out there.  I always have a lot of chores to do by certain date and I always wanted to have a good program which keeps track of my task list with deadline.  Also it should notify me a daily or weekly summary, preferably via email.

So I've tried a bunch of different things, including GMail Tasks, Google Calendar and some mobile apps.  But neither of those satisfied me. For example, with GMail Tasks, you can't set up email notification, so you will have to remember to open your task list regularly to see if you have anything due this week.  (and trust me, you will always forget it..... well at least I did) With Google Calendar, you can set up email notification, but it makes your Google Calendar looks so messy if you also use it for your personal calendar and you can't easily get a nice list of all the TODO list which you can easily sort in any way you want.  With mobile apps, .... uh..... I'm just not so much mobile phone person and I hate to enter anything on mobile phone.  Gimme a nice full-size keyboard. Then my chubby fingers will be happy...

So with all this failure, I actually gave up.  And I decided to put all my tasks in Google Spreadsheet, instead.  If I had to regularly open a webpage to see my tasks, I wanted it to be just a Google Spreadsheet: I was ready to suck it up.  But later I found there's something called Google Apps Script which allows you to write a JavaScript code with Google API Accessibility.  With this, you can access any of your google docs and do whatever you want to do.  You can even run the script on scheduled time.  So I just made my own script which goes through all the tasks in my TODOs spreadsheet, and emails me the list of tasks which are due in 7 days(or already due in the past).  I get only one email per week, but I can easily change it to daily notification if I see the need.

This is the most efficient personal task program(?) I have had used so far.  It doesn't require me to open a program or go to a webpage to see my task list: all I need is just my webmail account open, which is the case most of the time anyways.

Sometimes, it feels so good to be a programmer lol

Steve asked for the script.  So here it is.  First, the spreadsheet looks like this:

Note: you can put "done" for tasks you've finished.  These tasks won't be included in your digest email.  Or just delete the row once you are done.

Now the script:

function todoAlert() {
  // tasks due in these days will be shown
  var days = 14;  

  var date = new Date();

  var key = "YOUR_SPREAD_SHEET_KEY";
  var docLink = "" + key;
  var sheet = SpreadsheetApp.openById(key );
  sheet.sort(2, true);
  var range = sheet.getDataRange();
  var data = range.getValues();

  var upcoming = "<h3>In " + days + " Days</h3><ul>";
  var nodate = "<h3>No Dates</h3><ul>";
  var sendNodate = false;
  var sendUpcoming = false;
  for ( i = 1; i < range.getLastRow(); ++i )
    var task = data[i][0];
    var deadline = data[i][1];
    var status = data[i][2];
    var deadlineDate = new Date(deadline);
    // empty
    if ( status != "done" )
      if ( deadline <= 0 )
        sendNodate = true;
        nodate += "<li>" + task + "</li>";
      else if ( deadlineDate <= date )
        sendUpcoming = true;
        upcoming += "<li><b>(" + (deadlineDate.getMonth() + 1) + "/" + deadlineDate.getDate()+ ")</b> - " + task + "</li>";

  upcoming += "</ul>";
  nodate += "</ul>";
  var text = "";
  if ( sendUpcoming )
    text += upcoming + "<br/>";
  if ( sendNodate )
    text += nodate;
  // if set to true, only logger message
  var debug = false;
  if ( text.length > 0 )
    text += "<a href='" + docLink + "'>See TODO List</a>";
    if ( debug )
      MailApp.sendEmail("", "TODOs", text, {htmlBody: text});

You need to fill in YOUR_SPREAD_SHEET_KEY and YOUR_EMAIL_ADDRESS.  You can find the key from your spreadsheet's URL look for "&key=blaablaablaa".  Once you add this script in the script editor, you just need to set the trigger.  That's it.


  1. Nice idea, can I use your script?

  2. K updated the post with the script :)

  3. I like the idea, but there's a few features missing. E.g. scheduling returning tasks.

    PS: you can make separate 'overlays' in google calendar :P

  4. you are definitely right. Anonymous :) But for me it was more important to have a list view instead of calendar view. That's why I chose this way.

    But I stopped using my way since I bought myself an Android phone. Now I'm using an app called GTask which supports google tasks pretty well