Personal Time Tracker for Your Projects in Google Sheets: GAS113

Introduction

In this project, I present a time tracking tool within Google Sheets, empowered by Google Apps Script. Designed for personal projects, this tool offers essential features to monitor your time spent. Additionally, it allows you to generate reports that include billable hours, associated costs, and a detailed list of recorded tasks. These reports can be conveniently shared with clients. The best part? It's absolutely free!

YouTube

For visual learners, a comprehensive video explaining the details is available for your convenience.

For exclusive content and early access to my videos, please consider subscribing to my YouTube channel.

Subscribe

Instructions (6 Steps)

To establish your own project, adhere to the following steps:

Step 1

Utilize the provided link to create a duplicate of my project in your Google Drive.

Make a copy

Step 2

Complete the authorization requirement by clicking the button “Send report” from the menu “GAS113” in the copied Google Sheets.

* If you are not familiar with the authorization process, you can check this guide below.

A Complete Guide to Authorize GAS Project

Step 3

Upon successful authorization, you will gain access to the time tracing tool and its functionalities.

Add your own clients to the table “_clients”.

• Name - Make sure the names are unique since they are used keys in the VLOOKUP formulas
• Email - For sending the report to your clients
• Date Created - For information only

Add your own projects to the table “_projects”.

• Project - Make sure they are unique since they are used in the VLOOKUP formulas
• Client - Select from client list
• Status - For information only
• Hourly Rate - For cost calculation
• Date Created - For information only

Step 4

After preparing some projects, the next step is to incorporate tasks and monitor the time spent on each one.

New Task

• Task - The name of the task
• Project - Select from the project list
• Status - The task status
• Billable - Make sure it’s checked if the task is billable
• Date - For information only
• Client - Lookup from project with array formula
• Hourly Rate - Lookup from project with array formula
• Time Tracked - Lookup from project with formula
• Costs - Lookup from project with formula

To commence the timer, select a task row and press the "Start" button. A timer will be displayed in G1 while the chosen task will be highlighted in yellow.

* When the timer is active, refrain from changing the task's row position. The row position and start time are recorded in cells A1 and B1, respectively.

To stop or cancel a timer, press the "Stop" button. When you do so, the duration spent on the task will be automatically recorded and stored in the "_records" table for future reference.

• Yes - Stop the timer
• No - Keep the timer
• Cancel - Cancel the timer

Records

This table serves as a repository for all of your time tracking information and will be utilized in the report's construction in the subsequent step. 

* The script will manage the records automatically, but you can manually input a record or make adjustments if necessary.
*There are array formulas in row 2, so do not delete them. You may delete rows below row 2.

Step 5

Our report utilizes the "query" formula to extract a list of records from the time tracking records table. To create a customized report, you can employ the filters provided in the report header.

* The query formula is in cell A9, and there are hidden formulas in range D1:E7 to handle the query, you can reset the font color to see them.

* All named ranges in this report are prefixed with “report”, e.g. “reportClient” is a reference to C1. These named ranges can be used later in the email message as placeholder keys.

* The charts are for demo only, you can remove them or build your own charts with the data from the query.

* You don’t have to use this report since it’s more for my personal use case, with the data in the table “_records” you can build your own dashboard easily in the Google Sheets.

Step 6

To customize the email body for your report, you can open the script editor by navigating to "Extensions > Apps Script" within Google Sheets. In the file named "email.html," you can modify the email body. You can incorporate values from the named ranges present in the report tab of your Google Sheets document.

To facilitate testing, you can create a test client using your email address. A similar email will then be sent to this test client.

Other Features

Within the "1.code.gs" file, you'll find several configuration options for the app. However, I strongly recommend leaving these settings as they are unless you're comfortable modifying Google Apps Script code. If you're looking to change the email subject, you can find the relevant settings here.

const CONFIG = {

 RANGE: {

   START: "start", // where the start time of the timer is saved

   TASK: "task", // where the row number of the selected task is saved

   REFRESH: "H1", // a cell used for refreshing the timer (the cell will be cleared by the script)

   REPORT_FROM: "reportFrom", // where the email of the active user will be entered

   REPORT_TO: "reportTo", // where the email of the report should be sent to

   REPORT_BY: "reportBy", // where the name of the sender saved

 },

 SHEET: {

   TASKS: "Tasks",

   RECORDS: "Records",

   REPORT: "Report",

 },

 EMAIL: {

   SUBJECT: "Time Tracking Report to {{reportClient}}", // placeholders (named ranges for report) can be used

   CC: "",

   BCC: "",

 },

};

GoogleAppsScript Playlist (Subscribe for New Projects)

Links

Hire me on Upwork  Donate (PayPal) YouTube Github Twitter / X Instagram