Fitness Logging: An Experimental Project with Google Apps Script and clasp

This is a translation of the article I posted below!

• https://dev.to/xxxuutaxxx/building-a-google-apps-script-workout-logger-track-and-visualize-your-fitness-progress-with-spreadsheets-and-looker-studio-3505

Monitoring Workout Logs with Google Looker Studio

I go to the gym regularly.

There are many applications for handling workout logs, especially for mobile devices, but very few can be integrated with BI tools like Looker Studio. Since I aggregate information such as household accounts in Looker Studio, I wanted to be able to view my workout logs within Looker Studio as well.

What I Did

I'm introducing an experimental repository that allows you to calculate workout scores on a spreadsheet using clasp, which enables local development of Google Apps Script with TypeScript. This allows you to record workout logs in a spreadsheet and automatically calculate scores.

• https://github.com/uuta/gas-workout-logs

Motivation for Development

• I want to visualize my workouts in Google Looker Studio
• I want to minimize the effort of database management as much as possible
• Since I'm using it for personal use only, I want to develop within the free tier
• I want to quantify workout intensity based on weight and repetitions
• Creating a UI for inputting weight and reps is a hassle, so I want to avoid it as much as possible

Technical Stack

• TypeScript
• Clasp
• Google Apps Script
• esbuild
• prettier
• Google Forms

Implementation Details

I implemented the aggregation of scored workout logs on Google Sheets using Google Apps Script.

• To facilitate local development, I used Clasp and TypeScript to push code to Google Apps Script (the template used was from https://github.com/howdy39/gas-clasp-starter).
• By running the pushed script on Google Apps Script, a Google Form is automatically created and linked with the spreadsheet.
• Additionally, I implemented a batch process that automatically converts weight × reps logs collected through Google Forms into daily scores using triggers set in Google Apps Script.
• New workouts can be added by adding properties to the workoutTitles object.
  • https://github.com/uuta/gas-workout-logs/blob/71d76f7494b23f5dc5e85ff7c28e1588193fd2f9/src/template/workoutLogs.ts#L9-L73

const workoutTitles: Record<string, WorkoutEntities> = {
  'Incline dumbbell press': {
    category: 'chest',
    youtubeUrl: '8iPEnn-ltC8&ab_channel=ScottHermanFitness',
  },
  . . .
  // Add properties as needed
}

Below is the Google Form added by running the Google Apps Script.

How to Use

After cloning with git, follow these steps to use this repository.

1. Open an empty Google Sheet

2. Open the Apps Script editor

3. Copy the Script ID from the URL

Copy the script ID from the script editor's URL as follows.

https://script.google.com/home/projects/<scriptId>/edit

4. Build and push the project

Run the following command in the project directory.

$ yarn run push

5. Run the updateWorkoutLogs function

6. Set up triggers

Set up a trigger to run the main function every 5 minutes. The main function calculates workout log scores based on each weight and repetition.

7. Check the scores

The calculated scores can be integrated with BI tools such as Google Looker Studio. Check the maximum weights and daily scores.

Issues

The handling of time zones and locales is not robust. There are places where string date values in spreadsheet cells are attempted to be converted to Date objects using new Date(). Depending on the time zone and locale settings, the spreadsheet might change the date format, potentially causing the new Date() function to fail and result in an error.

Repository

• https://github.com/uuta/gas-workout-logs