Google Sheets

Posted August 07, 2025

The Google Sheet can serve as both your database and data validation layer. But... it is the core component and the first item to be created, as this data drives the Looker Studio Visualisation Dashboard

Data Structure Design

The Google Sheet serves as both your database and data validation layer. The schema is designed for maximum flexibility while maintaining data simplicity.

Core Data Model

This spreadsheet can contain whatever you wish, but I suggest starting with a three-tier Categorization System:

• Category - High-level grouping (e.g., “Work”, “Personal”, “Learning”)
• Task - Specific activity type (e.g., “Development”, “Meetings”, “Research”)
• Client - Context or recipient (e.g., “Project Alpha”, “Personal Study”, “Team Beta”)

This hierarchical structure provides multiple reporting dimensions while keeping data entry simple and consistent.

This is the system that will be used throughout this set of tutorials.

Time Tracking

This is managed using the following fields:

• Date - Automatic date stamping for trend analysis
• Start Time - Precise activity start timestamp
• End Time - Activity completion timestamp
• Duration (Minutes) - Auto-calculated for granular tracking
• Duration (Hours) - Decimal conversion of the minutes for reporting purposes (e.g., 18 min = 0.30 hrs)

Contextual Data

Notes - The field provided free-form text for qualitative insights and context

Design Rationale

Why Three Tiers?

This structure balances granularity with usability. Most time tracking fails because it’s either too simple (losing important context) or too complex (creating entry friction).

For a personal system, three levels provides enough detail for meaningful analysis without overwhelming the user.

Dual Duration Fields

Minutes give precise tracking for short activities, while conversion to a decimal representation of the hours enables clean aggregation in reports and charts.

Looker Studio can easily sum 0.25 + 0.50 + 0.75 hours making it easier to demonstrate hours per category in aggregate reporting.

Notes Field Strategy

This field provides qualitative data - which will transform and enhance the raw time logs enriching insight. Notes can capture context that numbers can’t - mood, obstacles, breakthroughs, or environmental factors affecting productivity.

Simple Data Entry in real time via Sheets

Filling in the data using data entry via google sheets is child’s play:

• Date and Time Fields can be added real time using keyboard short cuts.
  • ctrl + ; will set the current date
  • ctrl + shift + : will set the current time
• Duration and Duration in Hours can be populated with a formula
  • =C[row]-B[row] can be used for Duration
  • =( HOUR(D[row])*3600 + MINUTE(D[row])*60 + SECOND(D[row]) ) / 3600<br /> can be used for Duration in Hours
• Category, Task and Client/Context will become easier to fill out as you collate data. These fields will auto fill (based on the previous entries) allowing for tab complete.

Data validation

After a week or so of tracking data, your data set will take shape.

At this point, you can elect to set data validation rules via sheets.

For my own personal project, I removed data validation at this point, as I wanted to enable faster/easier change management.

Extensibility / Maintainability / Change Management

This schema scales easily as tracking habits evolve.

The tool is responsive to change management and works on simple data sets with built in tools like filter, find, replace.

Google sheets allows you to collects data in a way that supports both quick daily logs, but will also provide detailed project analysis over longer periods of time.

TUTORIAL 1 EXERCISE:

Set up a Google Sheet with the following headers:

Date | Start | End | Duration | Duration in Hours | Category | Task | Client/Context | Notes

Hint: Make sure you set the format of your fields appropriately:

- Date is a Date field
- Time is a Time field
- Duration is a duration field
- Duration in Hours to 2dp
- The rest can be standard open text fields