Scriptable Time-Tracker

Posted on April 26th, 2014

Simple, Lightweight, Universal Time-Tracker

This Simple, Lightweight, Universal Time-Tracker is an easy to use GUI for time-tracking. It’s completely scriptable which makes it an easy fit into any backend! If only I could find a suitable acronym to name it…

Scriptable Time Tracker

Installer for windows 32/64 bit. (Windows XP and up)
Full source code

Every workplace has it’s own way of tracking employees time and somehow it’s never as trivial as it could be.

The Time Tracker is intended to be a single, simple and universal way of tracking how your time was spent. Simply click on the button that describes the task you’re working on and repeat when switching tasks. The Time Tracker will fill out all the required forms for you.

Features:

  • Simple GUI interface to add and manage tasks
  • Javascript interface to completely customize the administrative side
  • Attach notes and colored labels to each task
  • Hides in the system tray and shows/hides the GUI with a keyboard shortcut

The backend of the Time Tracker is a single Javascript file which can easily be adapted to fill out an Excel sheet, enter rows into a database or make http-posts about what you are doing or what you have just done.

The file type and editor for the note for each task is freely configurable. That way a note can be anything from a simple text file to an Excel file template, etc..

Javascript

The empty javascript configuration shows which events can be customized. All events receive one or all of these arguments:

  • oCtx This is a dictionary object that servers as the context. It can be used to store global variables that only need to be initialized once in the ‘onStartup’ event.
  • sName The name of the task
  • sNotePath The path to the file that stores more information about this task.
/*
   Time Tracker v1.0 - Event handlers
*/

function onStartup(oCtx)
{
    // TODO: ...
}

function onShutdown(oCtx)
{
    // TODO: ...
}

function onTaskNew(oCtx, sName, sNotePath)
{
    // TODO: ...
}

function onTaskDone(oCtx, sName, sNotePath)
{
    // TODO: ...
}

function onTaskOn(oCtx, sName, sNotePath)
{
    // TODO: ...
}

function onTaskOff(oCtx, sName, sNotePath)
{
    // TODO: ...
}

Example 1:

This is an example that creates a text file to trace all events.

/*
  Time Tracker v1.0 - Event handlers

  Creates a file that traces all events
  Update settings.xml to enable this example
*/

KEY_FSO = "fso"
KEY_PATH = "path"
APPDATA_SUBDIR = "Time Tracker\\"
TRACE_FILENAME = "trace.txt"

function onStartup(oCtx)
{
  LOCAL_APPDATA = 28

  oShell = new ActiveXObject("Shell.Application")
  oCtx(KEY_PATH) = oShell.Namespace(LOCAL_APPDATA).Self.Path
  oCtx(KEY_PATH) = oCtx(KEY_PATH) + "\\" + APPDATA_SUBDIR + TRACE_FILENAME

  oCtx(KEY_FSO) = new ActiveXObject("Scripting.FileSystemObject")

  trace(oCtx, "onStartup called!")
}

function onShutdown(oCtx)
{
  trace(oCtx, new Date() + "onShutdown called!")
}

function onTaskNew(oCtx, sName, sNotePath)
{
  trace(oCtx, "onTaskNew: " + sName)
}

function onTaskDone(oCtx, sName, sNotePath)
{
  trace(oCtx, "onTaskDone: " + sName)
}

function onTaskOn(oCtx, sName, sNotePath)
{
  trace(oCtx, "onTaskOn: " + sName)
}

function onTaskOff(oCtx, sName, sNotePath)
{
  trace(oCtx, "onTaskOff: " + sName)
}

function trace(oCtx, sLine)
{
  function pad(a) { return("0"+a).slice(-2) };

  oDate = new Date()
  oDateStr = oDate.getFullYear() + "/" + pad(oDate.getMonth()+1)
    + "/" + pad(oDate.getDate()) + " " + pad(oDate.getHours())
    + ":" + pad(oDate.getMinutes()) + ":" + pad(oDate.getSeconds())
  oFile = oCtx(KEY_FSO).OpenTextFile(oCtx(KEY_PATH), 8, true, true)
  oFile.WriteLine("[" + oDateStr + "] " + sLine)
  oFile.Close()
}

Example 2:

This is an example that updates an Excel sheet. In this example Excel runs invisible in the background and opens the worksheet every time a task is switched off to register the time spent.

The example is made slightly more complicated because Excel does not allow opening a worksheet when the system is shutting down or logging off. This is why, when the user is logging off, the example creates a text file. This data in the text file is applied to the Excel sheet when the user next logs in again.

/*
    Time Tracker v1.0 - Event handlers

    Tracks time into an excel file.
    Update settings.xml to enable this example.
*/

KEY_FSO = "fso"
KEY_PATH = "path"
KEY_PATH_PEND = "pathpend"
KEY_EXCEL = "excel"
KEY_SHELL = "shell"
KEY_STARTTIME = "start"

KEY_LOGOFF = "TTSYS_LOGOFF"	// Build-in key to mark a logoff event

APPDATA_SUBDIR = "Time Tracker\\"
TIMESHEET_FILENAME = "Example2.xls"
PENDING_FILENAME = "pending.txt"
MSGBOX_TITLE = "Time Tracker Script"

BT_OK = 1
BT_RETRYCANCEL = 5
IC_STOP = 16
IC_QUESTION = 32
IC_EXCLAMATION = 48

function onStartup(oCtx)
{
  // create some object we (might) need
  oExcel = new ActiveXObject("Excel.Application")
  oCtx(KEY_EXCEL) = oExcel

  oWShell = new ActiveXObject("WScript.Shell")
  oCtx(KEY_SHELL) = oWShell  

  oFso = new ActiveXObject("Scripting.FileSystemObject")
  oCtx(KEY_FSO) = oFso

  // construct paths for the excel file and the 'pending' file
  oAShell = new ActiveXObject("Shell.Application")
  oCtx(KEY_PATH) = oAShell.Namespace(28).Self.Path	// 28 = local appdata folder
  oCtx(KEY_PATH_PEND) = oCtx(KEY_PATH)
  oCtx(KEY_PATH) = oCtx(KEY_PATH) + "\\" + APPDATA_SUBDIR + TIMESHEET_FILENAME
  oCtx(KEY_PATH_PEND) = oCtx(KEY_PATH_PEND) + "\\" + APPDATA_SUBDIR + PENDING_FILENAME

  // see if there is something pending ..
  sPath = oCtx(KEY_PATH_PEND)
  if (oFso.FileExists(sPath))
  {
    oFile = oFso.OpenTextFile(sPath, 1, false, true)
    if (oFile)
    {
      sName = oFile.ReadLine()
      nMonth = parseInt(oFile.ReadLine())
      nDay = parseInt(oFile.ReadLine())
      rHours = parseFloat(oFile.ReadLine())
      oFile.Close()

      updateTimesheet(oCtx, sName, nMonth, nDay, rHours)

      oFso.DeleteFile(sPath)
    }
   }
}

function onShutdown(oCtx)
{ // when logging off quitting excel won't work
  if (oCtx(KEY_LOGOFF) == true) return
  oExcel = oCtx(KEY_EXCEL)
  oExcel.quit()
}

function onTaskNew(oCtx, sName, sNotePath)
{
}

function onTaskDone(oCtx, sName, sNotePath)
{
}

function onTaskOn(oCtx, sName, sNotePath)
{   // remember when work started
    oCtx(KEY_STARTTIME) = (new Date()).getTime()
}

function onTaskOff(oCtx, sName, sNotePath)
{
  oDate = new Date()

  // get objects from context
  nStart = oCtx(KEY_STARTTIME)

  // calcuration duration in hours (90 mins is 1.5 hours)
  nDuration = oDate.getTime() - nStart
  nSeconds = Math.floor(nDuration / 1000)
  rHours = Math.floor(nSeconds / 3600)
  rHours += (1 / 3600) * (nSeconds % 3600)
  rHours = Math.round(rHours * 100) / 100

  // get date information
  nMonth = oDate.getMonth()
  nDay = oDate.getDate()

  if (oCtx(KEY_LOGOFF) == true)
  {  // the user is logging of, we can't open a worksheet anymore...
    oFso = oCtx(KEY_FSO)
    sPath = oCtx(KEY_PATH_PEND)
    oFile = oFso.CreateTextFile(sPath, true, true)
    oFile.WriteLine(sName)
    oFile.WriteLine(nMonth.toString())
    oFile.WriteLine(nDay.toString())
    oFile.WriteLine(rHours.toString())
    oFile.Close()
    return
  }      

  updateTimesheet(oCtx, sName, nMonth, nDay, rHours)
}

function updateTimesheet(oCtx, sName, nMonth, nDay, rHours)
{
  oExcel = oCtx(KEY_EXCEL)
  oShell = oCtx(KEY_SHELL)
  oPath = oCtx(KEY_PATH)

  sRangeProjectNums = "G3:Y3"
  sSheetNames = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]

  // First part is a sequence number; second part the project name,
  // anything other is description
  aFields = sName.split(/[,;]/)
  sName = (aFields.length > 1 ? aFields[1]: "").replace(/^\s+|\s+$/g,"");

  // open workbook; option to retry when it fails
  var oHours = null
  while (oHours == null)
  {
    try
    {
      oHours = oExcel.workbooks.open(oPath)
    } catch (err)
    {
      sInfo = "Trying to book (an additional) [" + rHours + "] hours to [" + sName + "]"
      nButton = oShell.Popup("Can't open excel sheet.\n\nError: [" + err.message
        + "]\n\n" + sInfo, 0, MSGBOX_TITLE, BT_RETRYCANCEL
        + IC_EXCLAMATION)
      if (nButton	== 2) return
      oHours = null
    }
  }

  // book hours; option to retry when it fails
  try
  {
    oSheet = oHours.Sheets(sSheetNames[nMonth])	

    // find project name or next empty space
    oColumns = oSheet.Range(sRangeProjectNums).Columns
    var cellProjNum = null
    for (i=1;i<=oColumns.Count;i++)
    {
      cellProjNum = oColumns(i)
      if (cellProjNum.Formula == "") break
      else if (cellProjNum.Formula == sName) break
    }

    // Message when there's no more room to add the project to the sheet
    if (cellProjNum.Formula != "" && cellProjNum.Formula != sName)
    {
      oHours.close()
      throw "Too many projects!"
    } else if (cellProjNum.Formula == "")
    {
      // if the column does not exist yet, create it
      cellProjNum.Formula = sName
    }

    // book time
    cellHours = cellProjNum.Offset(nDay, 0)
    rCurrentHours = parseFloat(cellHours.Formula == "" ? 0: cellHours.Formula)
    cellHours.Formula = rHours + rCurrentHours
  } catch (err)
  {
    nButton = oShell.Popup("Problem booking hours.\n\nError: [" + err.message
      + "]\n\n" + sInfo, 0, MSGBOX_TITLE, BT_RETRYCANCEL
      + IC_EXCLAMATION)
  }

  // save and close
  try
  {
    oHours.close(true)
  } catch (err)
  {
    nButton = oShell.Popup("Can't close excel sheet.\n\nError: [" + err.message
      + "]\n\n" + sInfo, 0, MSGBOX_TITLE, BT_RETRYCANCEL
      + IC_EXCLAMATION)
  }
}

Downloads

Installer for windows 32/64 bit. (Windows XP and up)
Full source code