How to Create a Timesheet Tracker in Google Spreadsheet

Tracking time you spent on tasks can become quite challenging especially when you're working with multiple tasks across serveral projects. While there are numerous online time trackers available, they often lack the user-friendly simplicity found in a spreadsheet.

Google Spreadsheet is a powerful tool having capabilities in both data storage and analysis, but there's not an effective time tracker available on the market that you can integrate with Google Sheets. Hence we've come up with a tool using Google Apps Script that enables you to keep track of the time right inside the Google Spreadsheet.

Listed below are the key features of Timesheet Tracker.

1. Easy to use, simple and intuitive interface, one-click timer start-stop
2. Categorize tasks based on projects
3. Easily generate pivot, charts and data formatting using built-in spreadsheet features

How to Install

This is a GAS (Google Apps Script) utility and a standalone project. You just need to create a copy of the spreadsheet in your Google Drive and run it from the spreadsheet sidebar.

• Create a copy of this Google Spreadsheet
• Once the spreadsheet is open, you'll notice a new menu in the toolbar named "Timesheet Tracker"
• Create project entries under the Settings sheet. You can also enable a ticking clock sound.
• Click on "Timesheet Tracker." → "Open" to reveal a sidebar where you can start recording time on a new task.

This tool uses JavaScript setInterval to compute the time spent on a task. However, there are instances where setInterval may not function as intended, especially if you navigate away from the tab. It is recommended to open the spreadsheet in a new window and ensure that the tab remains active.

Technologies Used

As this an open source tool, you can always explore and tweak it according to your requirements. If you need customization, you can reach out to us. Here are the technologies used in the script.

1. Vue.js and Bootstrap serves the front-end
2. Google Apps Script handles the backend

The script uses google.script.run, a client-side JavaScript API provided by Google Apps Script, primarily used within Google Workspace applications like Google Sheets, Google Docs, and Google Forms. This API allows client-side code (JavaScript in an HTML file) to call server-side functions (Google Apps Script functions) without needing to refresh the page.

Here's a basic example of how google.script.run is used with Google Apps Script:

 saveData: function(){
    google.script.run.saveData(this.timerId,this.projectTitle,this.taskTitle,this.displayTime);
 },

How are New Records Inserted into the Sheet?

First, the row number is determined by the unique id of the task. If found, the script updates the timeSpent. If not found, a new row is inserted at the top of the spreadshet to make it easy for the user not to scroll always to the bottom of the Spreadsheet to view the latest data.

function saveData(id,projectName,taskTitle,timeSpent){
  var spreadSheeet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadSheeet.getSheetByName("Timesheet");
  var rowNum = findRowNum(sheet,id);
  if(rowNum==-1){
     sheet.insertRowBefore(2);
     sheet.getRange(2, 1, 1, 5).setValues([[id,projectName,taskTitle,new Date(),timeSpent]]);
  }else{
     sheet.getRange("E"+rowNum).setValue(timeSpent);
  }
}

How the Time is Calculated?

The setInterval which runs every 1 sec, increases the totalSecs variable only if the current status is running. Every 30 secs, the data is synced with the Spreadsheet to prevent accidental data loss.

function startTimer() {
  if (!this.validate()) {
    return;
  }
  this.timerId = new Date().getTime();
  this.timerStatus = "running";
  var self = this;
  if (this.timeInterval == null) {
    this.timeInterval = setInterval(function () {
      if (self.timerStatus == "running") {
        self.totalSecs++;
        if (self.clockSound == "Yes") {
          self.playAudio();
        }
        if (self.totalSecs % 30 == 0) {
          self.saveData();
        }
      }
    }, 1000);
  }
}