Own time tracker with Google Sheets and Apps Script – Project Management

The objective is to have our own time tracker for our tasks totality free using Google Sheets and Apps Script, to do it we will use a little script with Google Apps Script that will help us to execute this functionality. The functionality is basic but it could help us to build something more powerful if we like coding, at the end of the post you can find the link to the google sheet to clone the sheet.

These are the columns that our excel sheet will have on Google Sheet

• Status (On hold, Start, Pause, Continue, Finished)
• Task (Title)
• Start date
• Closed date
• Tracked Time (hours : minutes)

We will create the first task in our Google Sheet file filling only the following columns

• Status column, in this column we will create a dropdown using the Data validation filter, this way we will use only the status values defined above.
• Task column, this column will be filled with the title of the task.

– Optionally we can hide the Z column

We will add the Apps Script code that is necessary to run the time tracker

To do it, we will clic on Tools, Script editor and we attach the code in the file Code.gs.

Here is the code for the Google Apps Script:

const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const currentSheet = spreadSheet.getActiveSheet();

function onEdit(e){
  var range = e.range;
  var column = range.getColumn();
  var row = range.getRow();

  var startDateCell = currentSheet.getRange(row,3);
  var finishedDateCell = currentSheet.getRange(row,4);  
  var clockCell = currentSheet.getRange(row,5);  
  var timerCell = currentSheet.getRange(row,26);  

  var dropdown = currentSheet.getRange(row,1).getValue();  
  var currentTime = new Date().getTime();
  var lastTime = timerCell.getValue();
  var msTime = 0;  

  if(column != 1){
    return;
  }

  switch (dropdown) {
    case 'Start':
      startDateCell.setValue(new Date()).setNumberFormat('dd/MM/yyyy');
      finishedDateCell.setValue('');
      clockCell.setValue('');
      timerCell.setValue(currentTime);      
      break;

    case 'Pause':
      if (lastTime > 1000000000000){
        msTime = currentTime - lastTime;
        timerCell.setValue(msTime);      
      }      
      break;

    case 'Continue':
      if (lastTime < 1000000000000){
        msTime = timerCell.getValue();
        timerCell.setValue(currentTime - msTime);              
      }
      break;

    case 'Finished':
      var min = 0;
      var sec = 0;
      var hr = 0;
      var minStr = "";
      var hrStr = "";

      if (lastTime > 1000000000000){
        msTime = currentTime - lastTime;
        timerCell.setValue(msTime);
      } else {
        msTime = timerCell.getValue();
      }

      sec = Math.floor(msTime / 1000);
      min = Math.floor(sec / 60);
      hr = Math.floor(min / 60);
      min = Math.floor(min % 60);

      minStr = min > 9 ? String(min) : "0" + String(min);
      hrStr = hr > 9 ? String(hr) : "0" + String(hr);
      clockCell.setValue(hrStr + ":" + minStr);
      finishedDateCell.setValue(new Date()).setNumberFormat('dd/MM/yyyy');
      break;
  }
}

We add a Project Trigger

Finally on Script Editor of Google Apps Scripts we add a Project Trigger for our onEdit function: on Script Editor we clic on Edit menu then clic on Current project’s triggers, then on the new window we clic on New Trigger and then we attach the trigger to our onEdit function, then on type of event we select on edit.

Here is the code for the Google Apps script on github
Here is the link for the sheet on Google Sheets to clone the Google Sheet if you need it: Google Sheets

Clic aquí para encontrar está entrada en su versión en español.