Automate Your Gains, Part 3 - Save Form Data to Google Sheets with Apps Script

![[IMG-Automate Your Gains-20250713163417973.png|500]] --- In [[Automate Your Gains, Part 2 - Build a Workout App UI with Apps Script|Automate Your Gains - Part 2]] the structure of the Google Sheet was organized to act as the database and the beginnings of the app's user interface was created with HTML and CSS. The next step is to make the app interactive by writing client-side JavaScript to capture user input and the server-side Apps Script code to save that data directly into the Google Sheet. --- ### **Section 1: Capturing User Input (The Client-Side)** In the `<script>` section of the `LogExercise.html` file, an event listener will be added to fire when the "Submit Exercise" button is clicked. Open the App Script and insert the following into the `LogExercise.html` file. >[!important]- Code Snippet >```js >// From LogExercise.html > >// ... existing code ... >const logForm = document.getElementById( > "logForm"); >const logFormSubmitButton = document > .getElementById( > "logFormSubmitButton"); >const statusDiv = document > .getElementById("status"); > >// ... > >logForm.addEventListener("submit", > function(event) { > event > .preventDefault(); // Prevents the page from reloading on submit > > // --- UI Feedback: Start Loading Animation --- > toggleFormLoading( > true); // Disables form fields > if (logFormSubmitButton) { > logFormSubmitButton > .classList.remove( > "finished"); > logFormSubmitButton > .classList.add( > "active"); > } > statusDiv.textContent = > "Submitting log..."; > statusDiv.style.color = > "#0d6efd"; > > // --- Data Collection --- > const selectedExerciseData = > appState > .currentWorkoutPlanDetails > .find( > (ex) => ex > .exerciseId === > appState > .currentSelectedExerciseId > ); > if (!selectedExerciseData) { > // Handle error > return; > } > > const formData = { > templateId: workoutTemplateSelect > .value, > exerciseId: appState > .currentSelectedExerciseId, > exerciseName: selectedExerciseData > .exerciseAlias || > selectedExerciseData > .exerciseName, > progressionModelId: selectedExerciseData > .progressionModelId, > performedStepNumber: selectedExerciseData > .currentStepNumber, > setsPerformed: document > .getElementById( > "setsPerformed" > ).value, > repsPerformed: String( > selectedExerciseData > .calculatedReps > ) > .toUpperCase() === > "AMRAP" ? > "AMRAP" : > document > .getElementById( > "repsPerformed" > ).value, > actualAmrapReps: document > .getElementById( > "actualAmrapReps" > ).value, > weightUsed: document > .getElementById( > "weightUsed" > ).value, > weightUnit: selectedExerciseData > .weightUnit || > "lbs", > rpe: document > .getElementById( > "rpeValue") > .value, > notes: document > .getElementById( > "workoutNotes" > ).value, > }; > > // --- Calling the Backend (Next Step) --- > google.script.run > .withSuccessHandler( > handleSubmitSuccess) > .withFailureHandler( > handleGenericFailure > ) > .processLogForm( > formData); > }); > ``` This code does three things: 1. **Prevents Default Behavior**: `event.preventDefault()` stops the form from trying to reload the page. 2. **Provides UI Feedback**: It disables the form and triggers the button's loading animation, notifying the user that their submission is being processed. ![[IMG-Automate Your Gains, Part 3 - Save Form Data to Google Sheets with Apps Script-20250624231538060.png|400]] 3. **Collects Data**: It gathers all the values from the input fields and packages them into a `formData` object, ready to be sent to our backend. --- ### **Section 2: The Bridge - Calling the Backend with `google.script.run`** This is the asynchronous bridge that allows our client-side HTML file to execute functions in our server-side `main.js` file. > [!TIP] How google.script.run Works > > It's a three-part command: > > 1. `.withSuccessHandler(functionToRunOnSuccess)`: Specifies the JavaScript function to call if the server-side operation completes without errors. > 2. `.withFailureHandler(functionToRunOnError)`: Specifies the function to call if an error occurs. > 3. `.yourServerFunctionName(dataToSend)`: The actual name of the function in `main.js` you want to run. The `formData` object is passed as an argument. The code calls a server function named `processLogForm` and passes the collected `formData` to it. --- ### **Section 3: Processing and Saving the Data (The Server-Side)** The `main.js` file will be the next focus. The `processLogForm` function will receive the `formData` object, validate it, format it, and append it to our `WorkoutLog` sheet. Add the following functions to your `main.js` file. >[!important]- Code Snippet >```js >// From main.js > >function processLogForm(formData) { > try { > const sheets = getAppSheets(); > > // --- 1. Validate the incoming data --- > const requiredFields = [ > "templateId", > "exerciseId", > "setsPerformed", > "repsPerformed", > "weightUsed", > "rpe", > ]; > for (const field of > requiredFields) { > if ( > formData[field] === > undefined || > formData[field] === > null || > String(formData[field]) > .trim() === "" > ) { > throw new Error( > `Missing required form data field: ${field}.` > ); > } > } > > const weightUsed = parseFloat( > formData.weightUsed); > if (isNaN(weightUsed) || > weightUsed < 0) > throw new Error( > "Invalid 'Weight Used'." > ); > > // --- 2. Format the data for the sheet --- > const workoutLogHeaders = > getHeaders(sheets > .workoutLogSheet); > const logEntry = {}; > workoutLogHeaders.forEach(( > header) => { > logEntry[header] = > null; > if (header === > "LogID") > logEntry[ > header] = > `WLOG_${Utilities.getUuid()}`; > else if (header === > "ExerciseTimestamp" > ) logEntry[ > header] = > new Date(); > else if (header === > "ExerciseID") > logEntry[ > header] = > formData > .exerciseId; > else if (header === > "TotalSetsPerformed" > ) > logEntry[ > header] = > formData > .setsPerformed; > else if (header > .toLowerCase() > .includes( > "repsperformed" > )) > logEntry[ > header] = > formData > .repsPerformed; > else if (header === > "WeightUsed") > logEntry[ > header] = > weightUsed; > else if (header === > "RPE_Recorded") > logEntry[ > header] = > formData.rpe; > else if (header === > "WorkoutNotes") > logEntry[ > header] = > formData > .notes || null; > else if (header === > "LinkedTemplateID" > ) > logEntry[ > header] = > formData > .templateId; > }); > > // --- 3. Write the data to the sheet --- > logWorkoutEntryToSheet(logEntry, > sheets.workoutLogSheet, > workoutLogHeaders); > > // Clear the cache so the next data load is fresh > SCRIPT_CACHE.remove( > `workoutLogData_singleUser` > ); > > // --- 4. Call the progression logic (we'll cover this in Part 5) --- > updateUserProgressionAfterLog > ( /* ... arguments ... */ ); > > return { > message: `${ > formData.exerciseName || formData.exerciseId > } logged successfully!`, > }; > } > catch (error) { > Logger.log( > `ERROR in processLogForm: ${error.message}` > ); > throw new Error( > `Failed to process log: ${error.message}` > ); > } >} > >// Helper function to append the row >function logWorkoutEntryToSheet( > logEntryObject, workoutLogSheet, > headersArray) { > const rowData = headersArray.map(( > header) => > logEntryObject[header] !== > undefined ? logEntryObject[ > header] : null > ); > workoutLogSheet.appendRow(rowData); >} >``` This server-side code performs all the critical backend tasks: - it validates the data - creates a new `logEntry` object that matches the columns in the `WorkoutLog` sheet - `workoutLogSheet.appendRow(rowData)` adds the new workout as a new row. --- ### **Section 4: Closing the Loop with User Feedback** Once the server successfully saves the data, it returns a success message. The `.withSuccessHandler()` from Step 2 calls the `handleSubmitSuccess` function in `LogExercise.html` to handle this response. Add this function to the `<script>` section of `LogExercise.html`. >[!important]- Code Snippet >```js >// From LogExercise.html >/** > * @description Handles the successful submission of the exercise log form. > * @param {*} result > */ >function handleSubmitSuccess(result) { > statusDiv.textContent = result > .message || > "Exercise logged successfully!"; > statusDiv.style.color = "green"; > > // --- Animate the button to a success state --- > if (logFormSubmitButton) { > logFormSubmitButton.classList > .remove("active"); > logFormSubmitButton.classList > .add("finished"); > } > toggleFormLoading( > false); // Re-enable form fields > > // --- Update the UI to show the exercise is logged --- > const exerciseId = > lastSubmittedLogFormData > .exerciseId; > const exerciseListItem = document > .querySelector( > `.exercise-list-item[data-exercise-id="${exerciseId}"]` > ); > if (exerciseListItem) { > const completionIndicator = > exerciseListItem > .querySelector( > ".completion-indicator" > ); > if (completionIndicator) { > completionIndicator > .innerHTML = > "✔"; // Checkmark > } > exerciseListItem.classList.add( > "logged-today-visual"); > } > > // --- Reset the button and status after a delay --- > setTimeout(() => { > if ( > logFormSubmitButton && > logFormSubmitButton > .classList.contains( > "finished") > ) { > logFormSubmitButton > .classList > .remove( > "finished"); > } > if (statusDiv.style > .color === "green" > ) { > statusDiv > .textContent = > ""; > } > }, 3000); >} >``` This function does two things: - It displays the success message returned from the server. - It updates the UI in real-time, adding a checkmark next to the exercise in the summary list so the user knows it's been completed for the day. ![[IMG-Automate Your Gains, Part 3 - Save Form Data to Google Sheets with Apps Script-20250624231538196.png|400]] ### **Conclusion** We've now connected all the dots to create a functional "write path"! Our application can successfully take user input from an HTML form, send it to a Google Apps Script backend, and save it permanently in a Google Sheet. In **Part 4**, we will do the reverse. We'll build the "read path" to fetch historical workout data from our sheet and display it in the app, providing valuable context to the user as they log their workouts. --- ## Resources ### 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)** ### Related Articles Check out the other articles from **Automate Your Gains** series: %% DATAVIEW_PUBLISH_CONVERT start ```dataview LIST WITHOUT ID "**" + file.link + "** " + description + "" 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]]** 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. - **[[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]]** A fitness app project that shows how to plan a smart workout logger using Google Apps Script and Google Sheets to automate your training. - **[[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]]** 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. - **[[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]]** 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." - **[[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]]** 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. - **[[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]]** Elevate the app from a simple logger to a smart training partner. Code automation logic that analyzes user performance (RPE) to recommend workout progressions. - **[[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]]** 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. - **[[07 - Publish - Obsidian/Posts/Series/Automate Your Gains.md|Automate Your Gains]]** Articles related to creating a workout logger web application using Google Sheets and Apps Script %% DATAVIEW_PUBLISH_CONVERT end %%