![[IMG-Automate Your Gains-20250713163417973.png|500]]
---
In [[Automate Your Gains, Part 1 - Plan a Custom Workout App with Google Sheets]], we outlined a plan to build a workout logger application to remove the tedious task of manual spreadsheet input. In this post we will focus on building a user interface that looks like the image below and laying the ground work for some advanced logic handling.
</br>
![[IMG-Automate Your Gains, Part 2 - Build a Workout App UI with Apps Script-20250624231504495.png|300]]
</br>
In this tutorial, we will build the two components of for the workout logger web app:
1. **The "Database":** This will be a Google Sheet and will include everything from exercises to progression models.
2. **The User Interface:** This will be the HTML and CSS for the web app's front-end that uses Google Apps Script.
</br>
---
## **Section 1: Structuring the Google Sheet "Database"**
The main power of this web app will be from creating a well-organized Google Sheet.
Create a new Google Sheet and add the following eight tabs. The `README.md` on [GitHub](https://github.com/drusho/workout-logger-google-apps-script) provides a full breakdown of every column and sheet tab that is required.
</br>
> [!TIP] Tip: Sheet Names Matter
> The Apps Script code we write will refer to these sheet names exactly. Ensure your tab names match the ones listed below.
Here are the key sheets and the columns they need to contain:
### `ExerciseLibrary`
This sheet acts as the master list of every exercise for the app.
**Sample of `ExerciseLibrary`**
| **ExerciseID** | **ExerciseName** | **ExerciseAlias** | **BodyGroup** | **EquipmentType** | **UnitOfMeasurement** |
| -------------- | ------------------- | ----------------- | ------------- | ----------------- | --------------------- |
| EX001 | Barbell Bench Press | Bench Press | Chest | Barbell | lbs |
| EX002 | Bodyweight Squat | BW Squat | Legs | Bodyweight | reps |
### `WorkoutTemplates`
This sheet defines the workout plans or splits.
**Sample of `Workout Template`**
| **TemplateID** | **TemplateName** | **Description** |
| -------------- | ------------------------ | -------------------------------------------- |
| WT01 | 5/3/1 - Cycle 1 - Push A | Week 1 Push Day for 5/3/1 cycle. |
| WT02 | Bodyweight HIIT | A 20-minute high-intensity interval routine. |
### `TemplateExerciseList`
This sheet links exercises from the `ExerciseLibrary` to the `WorkoutTemplates` and assigns a progression model to each one.
**Sample of `TemplateExerciseList`**
| **TemplateExerciseID** | **TemplateID** | **ExerciseID** | **OrderInWorkout** | **ProgressionModelID** |
| ---------------------- | -------------- | -------------- | ------------------ | ---------------------- |
| TEL001 | WT01 | EX001 | 1 | PM_8StepRPE_001 |
| TEL002 | WT01 | EX005 | 2 | PM_8StepRPE_002 |
</br>
### Other Key Sheets
The remaining sheets (`ProgressionModels`, `ProgressionModelSteps`, `UserExerciseProgression`, `WorkoutLog`, and `Users`) contain the rules for progression, track exercise progress, and log workout history.
</br>
---
## **Section 2: Creating the Apps Script Project**
**Steps to create the web app.**
1. Open Apps Script from the ‘Extensions’ menu.</br>
![[IMG-Automate Your Gains, Part 2 - Build a Workout App UI with Apps Script-20250624231504672.png|300]]</br>
2. In the script editor, you will see a `Code.gs` file. You can rename this to `main.gs`.
3. Click the `+` icon next to "Files" and select "HTML". Name the new file `LogExercise.html` </br>
![[IMG-Automate Your Gains, Part 2 - Build a Workout App UI with Apps Script-20250624231504763.png|300]]</br>
You should now have two files: `main.gs` for our backend logic and `LogExercise.html` for our UI.
</br>
### **Backend Code: (`main.gs`)**
The first step for the web app will to instruct Apps Script to display the HTML file when the page is visit. Add the `doGet()` function to `main.gs` file to your script.
>[!important]- JS Code Snippet
>```js
>// Web App Entry Point
>function doGet(e) {
> Logger.log(
> `>>> doGet called. Parameters: ${JSON.stringify(e.parameter)}`
> );
> return HtmlService
> .createTemplateFromFile(
> "LogExercise")
> .evaluate()
> .setTitle(
> "Rusho's Workout Logger")
> .setXFrameOptionsMode(
> HtmlService
> .XFrameOptionsMode.ALLOWALL)
> .addMetaTag("viewport",
> "width=device-width, initial-scale=1"
> );
>}
>```
</br>
The `doGet()` function is a special trigger that runs whenever the app's URL is accessed. It finds our `LogExercise.html` file and displays it in the browser.
</br>
### **Frontend Structure (`LogExercise.html`)**
The next step is to build the skeleton of the UI using `LogExercise.html`. For this project we will use the Bootstrap 5 framework for responsive, mobile-first styling and Font Awesome 6 for its icons.
Open the `LogExercise.html` in Apps Script and the following code block. This will populate a header, a dropdown for workout plans, a container to show the plan summary, and the main form for logging an exercise.
>[!important]- HTML Code Snipppet
>```html
><!DOCTYPE html>
><html>
>
><head>
> <base target="_top" />
> <link
> href="https://cdn.jsdelivr.net/npm/
[email protected]/dist/css/bootstrap.min.css"
> rel="stylesheet" />
> <link rel="stylesheet"
> href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.2/css/all.min.css" />
> <style>
> body {
> padding: 15px;
> font-family: sans-serif;
> }
>
> .app-header {
> display: flex;
> align-items: center;
> margin-bottom: 20px;
> }
>
> .app-header h4 {
> font-size: 1.75rem;
> }
>
> #workoutDisplay {
> margin-top: 10px;
> padding: 15px;
> border: 1px solid #ddd;
> border-radius: 0.375rem;
> }
> </style>
></head>
>
><body>
> <div class="app-header">
> <i
> class="fa-solid fa-dumbbell header-icon me-2"></i>
> <h4>Rusho's Workout Logger</h4>
> </div>
>
> <div class="mb-3">
> <label
> for="workoutTemplateSelect"
> class="form-label">Workout
> Plan</label>
> <select class="form-select"
> id="workoutTemplateSelect"
> name="workoutTemplateSelect"
> required
> onchange="loadAndDisplayWorkoutPlan()">
> <option value="" disabled
> selected>Loading
> templates...</option>
> </select>
> </div>
>
> <div id="workoutDisplay">
> <div id="workoutDetailsList">
> </div>
> </div>
>
> <hr />
>
> <h6>Log Exercise</h6>
> <form id="logForm"></form>
>
> <script
> src="https://cdn.jsdelivr.net/npm/
[email protected]/dist/js/bootstrap.bundle.min.js">
> </script>
> <script>
> // Client-side JavaScript will go here
> </script>
></body>
>
></html>
>```
---
## **Section 3: Bringing the UI to Life**
The UI can be made dynamic by loading the workout plans from our Google Sheet into the dropdown menu. This requires a three-step process: the client (`LogExercise.html`) asks the server (`main.gs`) for data, and the server the responds.
### The Trigger ( `LogExercise.html`)
The `DOMContentLoaded` event listener will run as soon as the page loads.Add this inside the `<script>` tag at the bottom of `LogExercise.html`.
>[!important]- HTML Code Snippet
>```js
>document.addEventListener(
> "DOMContentLoaded", () => {
> loadWorkoutTemplates();
> });
>
>function loadWorkoutTemplates() {
> google.script.run
> .withSuccessHandler(
> handleWorkoutTemplatesSuccess
> )
> .withFailureHandler(
> handleGenericFailure)
> .getAvailableWorkoutTemplates();
>}
>```
</br>
> [!TIP] Tip: What is `google.script.run`
> This allows the client-side JavaScript to call functions on the server-side (`main.gs`) code.
> - `.withSuccessHandler()` specifies which function to run if the call succeeds
> - `.withFailureHandler()` handles any errors.
</br>
### The Server-Side Logic (`main.gs`)
Create the `getAvailableWorkoutTemplates()`, this function reads the `WorkoutTemplates` sheet and returns the names and IDs on the sheet. Add this code block to your `main.gs` file.
>[!important]- JS Code Snippet
>```js
>function getAvailableWorkoutTemplates() {
> try {
> // These helper functions get the sheet by name and read its data.
> // We will define them fully in a later part of the series.
> const sheets = getAppSheets();
> const {
> data: templateData,
> headerMap
> } =
> getSheetDataWithHeadersAndMap(
> sheets
> .workoutTemplatesSheet,
> "workoutTemplatesData"
> );
>
> // Ensure the sheet has the required columns
> if (
> headerMap.TemplateID ===
> undefined ||
> headerMap.TemplateName ===
> undefined
> ) {
> throw new Error(
> "WorkoutTemplates sheet is missing TemplateID or TemplateName header."
> );
> }
>
> // Map the data into a clean array of objects to send to the client
> return templateData
> .map((row) => ({
> templateId: row[
> headerMap
> .TemplateID
> ],
> templateName: row[
> headerMap
> .TemplateName
> ],
> }))
> .filter((t) => t
> .templateId && t
> .templateName);
> }
> catch (error) {
> Logger.log(
> `Error in getAvailableWorkoutTemplates: ${error.message}`
> );
> throw new Error(
> `Failed to get workout templates: ${error.message}`
> );
> }
>}
>```
</br>
### The Success Handler ( `LogExercise.html`)
When `getAvailableWorkoutTemplates` successfully returns the data, the `.withSuccessHandler()` calls our `handleWorkoutTemplatesSuccess` function. This function takes the list of templates and builds the HTML `<option>` elements for our dropdown. Add this to the `<script>` tag in `LogExercise.html`.
>[!important]- JS Code Snippet
>```js
>function handleWorkoutTemplatesSuccess(
> templates) {
> const workoutTemplateSelect =
> document.getElementById(
> "workoutTemplateSelect"
> );
> workoutTemplateSelect.innerHTML =
> '<option value="" disabled selected>Select workout plan...</option>';
> if (templates && templates.length >
> 0) {
> templates.forEach((
> template) => {
> workoutTemplateSelect
> .add(
> new Option(
> template
> .templateName,
> template
> .templateId
> )
> );
> });
> }
> else {
> workoutTemplateSelect
> .innerHTML =
> '<option value="" disabled selected>No templates found.</option>';
> }
>}
>
>function handleGenericFailure(error) {
> console.error("Server Error:",
> error);
> // You can add user-facing error messages here
>}
>```
</br>
## **Conclusion**
Set up for the data structure and the static UI is now complete. Also the client-server-client data loop to dynamically populate a dropdown from your Google Sheet has been generated.
In [[Automate Your Gains, Part 3 - Save Form Data to Google Sheets with Apps Script|Part 3]], we will build out the rest of the web form and write a script that logs a workout and saves it directly to the `WorkoutLog` sheet.
</br>
---
## Resources
</br>
### Github and Demo
You can find the completed code for the entire project, including all features and documentation, on GitHub.
- **[View Project on GitHub](https://github.com/drusho/workout-logger-google-apps-script)**
- **[Try the Live Web App Demo](https://script.google.com/macros/s/AKfycbwiQyKHvKap9oiKqSpAhFdbq9xH36wOZCr0a6QRZEgSL0ErCWXhaUoVAIPcqD1zM_2I/exec)**
</br>
### Related Articles
Check out the other articles from **Automate Your Gains** series:
%% DATAVIEW_PUBLISH_CONVERT start
```dataview
LIST WITHOUT ID
"**" + file.link + "** </br>" + description + "</br></br>"
FROM "07 - Publish - Obsidian"
WHERE
publish = true
AND file.name != "About Me"
AND file.name != "Home"
AND file.name != "Series - Automate Your Gains"
AND series = "Automate Your Gains"
SORT date DESC
```
%%
- **[[07 - Publish - Obsidian/Articles/A Deep Dive into the 'Automate Your Gains' Workout App UI & Features.md|A Deep Dive into the 'Automate Your Gains' Workout App UI & Features]]** </br>Take a tour of a custom workout logger built with Google Apps Script. See its mobile-friendly UI, dynamic workout planning, "last workout recall," and automated progression features in action.</br></br>
- **[[07 - Publish - Obsidian/Articles/Automate Your Gains, Part 1 - Plan a Custom Workout App with Google Sheets.md|Automate Your Gains, Part 1 - Plan a Custom Workout App with Google Sheets]]** </br>A fitness app project that shows how to plan a smart workout logger using Google Apps Script and Google Sheets to automate your training.</br></br>
- **[[07 - Publish - Obsidian/Articles/Automate Your Gains, Part 2 - Build a Workout App UI with Apps Script.md|Automate Your Gains, Part 2 - Build a Workout App UI with Apps Script]]** </br>Turn a Google Sheet into a database and build a mobile-friendly UI with Google Apps Script. A step-by-step guide to creating the foundation for the workout logger.</br></br>
- **[[07 - Publish - Obsidian/Articles/Automate Your Gains, Part 3 - Save Form Data to Google Sheets with Apps Script.md|Automate Your Gains, Part 3 - Save Form Data to Google Sheets with Apps Script]]** </br>Connect front-end to your back-end. This guide covers using google.script.run to capture HTML form data and save it directly to Google Sheets, creating a complete "write path."</br></br>
- **[[07 - Publish - Obsidian/Articles/Automate Your Gains, Part 4 - Read & Display Data from Google Sheets in Your App.md|Automate Your Gains, Part 4 - Read & Display Data from Google Sheets in Your App]]** </br>Close the data loop for the workout app. Fetch, filter, and sort data from a Google Sheet backend and display it dynamically into web app's UI for a richer user experience.</br></br>
- **[[07 - Publish - Obsidian/Articles/Automate Your Gains, Part 5 - Code Smart Automation for Your Fitness App.md|Automate Your Gains, Part 5 - Code Smart Automation for Your Fitness App]]** </br>Elevate the app from a simple logger to a smart training partner. Code automation logic that analyzes user performance (RPE) to recommend workout progressions.</br></br>
- **[[07 - Publish - Obsidian/Articles/Automate Your Gains, Part 6 - When to Scale Your Google Apps Script Project.md|Automate Your Gains, Part 6 - When to Scale Your Google Apps Script Project]]** </br>A complete review of the workout app project. Covers the pros and cons of using Google Sheets as a database, ideas for future features, and how to know when it's time to migrate.</br></br>
- **[[07 - Publish - Obsidian/Posts/Series/Automate Your Gains.md|Automate Your Gains]]** </br>Articles related to creating a workout logger web application using Google Sheets and Apps Script</br></br>
%% DATAVIEW_PUBLISH_CONVERT end %%