<p>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.</p>
<p>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&nbsp;<code>Google Apps Script</code> that enables you to keep track of the time right inside the Google Spreadsheet.</p>
<p>Listed below are the key features of Timesheet Tracker<em><strong>.</strong></em></p>
<ol>
<li>Easy to use, simple and intuitive interface, one-click timer start-stop</li>
<li>Categorize tasks based on projects</li>
<li>Easily generate pivot, charts and data formatting using built-in spreadsheet features</li>
</ol>
<p><img src="/uploads/2023/11/timesheet-tracker.png" alt="Timesheet Tracker"></p>
<h2>How to Install</h2>
<p>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.</p>
<ul>
<li>Create a copy of this <a href="https://docs.google.com/spreadsheets/d/1Df-R2pH-nk0xY8rS6OzAb51Ergi0p3f4kWEAgewUonU/copy" target="_blank" rel="noopener">Google Spreadsheet</a></li>
<li>Once the spreadsheet is open, you'll notice a new menu in the toolbar named "Timesheet Tracker"</li>
<li>Create project entries under the <code>Settings sheet</code>. You can also enable a&nbsp;<code>ticking clock sound</code>.</li>
<li>Click on "Timesheet Tracker." &rarr; "Open" to reveal a sidebar where you can start recording time on a new task.</li>
</ul>
<p><img src="/uploads/2023/11/projects-timesheet-tracker.png" alt="Project list - Timesheet Tracker"></p>
<blockquote>
<p>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.</p>
</blockquote>
<h2>Technologies Used</h2>
<p>As this an open source tool, you can always explore and tweak it according to your requirements. If you need customization, you can&nbsp;<a href="/contact?type=project#contact-form">reach out to us</a>. Here are the technologies used in the script.</p>
<ol>
<li>Vue.js and Bootstrap serves the front-end</li>
<li>Google Apps Script handles the backend</li>
</ol>
<p>The script uses <code>google.script.run</code>, 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.</p>
<p>Here's a basic example of how <code>google.script.run</code> is used with Google Apps Script:</p>
<pre class="language-javascript"><code> saveData: function(){
    google.script.run.saveData(this.timerId,this.projectTitle,this.taskTitle,this.displayTime);
 },</code></pre>
<h2>How are New Records Inserted into the Sheet?</h2>
<p>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.</p>
<pre class="language-javascript"><code>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);
  }
}</code></pre>
<h2>How the Time is Calculated?</h2>
<p>The <code>setInterval</code> which runs every 1 sec, increases the <code>totalSecs</code> variable only if the current status is <code>running</code>. Every 30 secs, the data is synced with the Spreadsheet to prevent accidental data loss.</p>
<pre class="language-javascript"><code>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);
  }
}
</code></pre>

Timesheet Tracker is a Google Spreadsheet tool which allows you to record and manage your time right inside the Google Spreadsheet interface. Easily maintain time for project-specific tasks with its user-friendly interface and leverage the robust capabilities of a spreadsheet for seamless report generation and pivot analysis.

Google Apps Script

Tech Tips, Tutorials & How-to Guides

POSTS

How to Create a Timesheet Tracker in Google Spreadsheet

BROWSE ALL TABGRAF TOPICS

Even Guinea Pig Likes
Fresh Carrots🥕

Quick Links

Curiosity