I’d like to introduce my experimental repository, which can calculate workout scores on a Spreadsheet with Google Apps Script compiled by clasp. This can leave workout logs in a Spreadsheet and calculate scores automatically.
Problem Statement
While numerous applications handle workout logs, especially for mobile devices, very few of them are integrated with BI tools like Looker Studio. As I aggregate various information in Looker Studio, I would like to be able also to view my workout logs within Looker Studio.
Development Motivation
- Visualize my workouts in Google Looker Studio.
- Avoid the hassle of database management as much as possible.
- Develop this within a free range as I will be using it for personal purposes only.
- Quantify the intensity of my workouts based on weight and reps.
- Not create the UI for entering weight and reps from scratch as it would take a lot of time.
Tech Stack
- Typescript
- Clasp
- Google Apps Script
- esbuild
- prettier
What I’ve Done
Implemented the aggregation of scored workout logs in a Google Spreadsheet using Google Apps Script.
- To facilitate local development, I used Clasp and Typescript to push the code to Google Apps Script (the template used is from https://github.com/howdy39/gas-clasp-starter).
- By executing the pushed script on Google Apps Script, it automatically creates a Google Form and integrates it with the spreadsheet.
- I also implemented batch processing, where a trigger set in Google Apps Script automatically converts the weight x reps logs collected through the Google Form into daily scores.
- New workouts can be added by adding properties to the
workoutTitlesobject.
const workoutTitles: Record<string, WorkoutEntities> = { 'Incline dumbbell press': { category: 'chest', youtubeUrl: '8iPEnn-ltC8&ab_channel=ScottHermanFitness', }, . . . // add properties if you want
}
How to use
Here’s how to use this repository after git clone.
1. Open a blank Google Spreadsheet
2. Open the script editor
3. Copy the script ID from the URL
Copy the script ID from the URL of the script editor which is like the below.
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 updateWorkoutLogs function
6. Set the trigger
Set the trigger to run the main function every 5 minutes. The main function can calculate the scores with the workout logs based on each weight and count.
7. Check the scores
The calculated scores can be integrated with BI tools like Google Looker Studio. Check the maximum weights and daily scores
Challenges
- The handling of timezone and locale is not robust. There are places where I’ve tried to convert the string date values in the spreadsheet cells to Date objects using new Date(). However, depending on the timezone and locale settings, the spreadsheet may change the date format, causing the new Date() function to fail and result in errors.