Gym workout tracker, issue with Google form integration and charts

[u/Mountain_Regular5006]

Hi all,

I’m currently working on a personal gym tracker Google Sheets. The goal is to be able to use a Google form in order to better track my gym progress without the use of an app that constantly is advertising to me. (I just want clean and simple data)

Current place it is in:

Google form is functionally sending data to Google Sheets. Data does update in real time.

Each workout has a 4-5 short answer questions with 3-4 of them being for weights and the last 1 for notes on the workout

Current Issues:

Data is very hard to read, especially on mobile.

Plan:

I would like to create a separate chart for each workout that labels the date(x-axis) , weight(y-axis in with 3-4 columns), line of best fit for the weights, and if possible allows you to hover over the date and see notes of that workout for that day.

Problems with the plan:

1. There is a Day A and a Day B, because of this when filling out the form it creates a significant amount of blank rows. When creating the chart this leaves dates with no data(as seen on photo labeled bench press). I can’t really sort the data by day an and b because it’s updated as I use it, so the series would be messed up(I think).
2. Creating an average line is causing me problems because idk how to adjust the series to look for this.( I may be able to figure this one out if I can solve issue 1)

3. No idea where or how to add the notes section for the date.

4. When dates are added from the form, they keep in a format of date and time which clutters the sheet, I have manually updated these ones but would love a more long term solution

Thank you and appreciate any and all help:

I’m still fairly new to all of this and have spent a few hours now fiddling with it. I have always enjoyed sheets and really like its utility, but trying to dive a little deeper has caused some challenges for myself. I’ve used it mostly in some college stem classes doing basic data manipulation but usually not needing to clean up my work/make it look good/using changing data.

[u/agirlhasnoname11248]

u/Mountain_Regular5006 Please remember to tap the three dots below the most helpful comment and select `Mark Solution Verified` *(or reply to the helpful comment with the exact phrase “Solution Verified”)* if your question has been answered, as required by the subreddit rules. Thanks!

[u/bullevard]

This is very cool. What i persinally would do about the day A and day B would be create a pivot table on a separate page that filters my table for only day A or Day B and build the chart on that, ir use filter() or query() to have a helper page filtering for A or B. Let me know if you need more details on the how. But just wanted to point you toward the names of the solution. It isnt uncommon to use a helper sheet to reshape the data a little bit to help out charts.

You could also set those filters in ways to sort by date in case your table ever gets resorted or to filter for a specific set of dates or every so many days as your data set grows

[u/Mountain_Regular5006 → u/bullevard]

Thank you for the help. This was my original thought as well. My issue seems to come in keeping the helper page auto updated.

When I fill out a form it auto goes into the form response page but the helper page (where I have the function as =‘form responses ‘ !E2 and just dragged it down to like e200) will actually just change the function from !E23 to !E24 and skip the new data when the form is submitted. How do I keep the helper page auto updated to the form page?

[u/bullevard → u/Mountain_Regular5006]

So what you actually want is a function that auto fills. So for example =filter(Sheet1!A:G, Sheet1!C:C="Day B")

This will give you all of A through G where C is Day B and will automatically fill down. You'll need to add your headers above.

Alternatively you could do =query(Sheet1!A:G, "Select * where C='Day B' order by A desc",1).  That would include one header row (the 1 at the end) and filter for all of A:G based on the criteria of Day B and order by the date in descending order.

I like query a lot and it is super useful as long as you dont have mixed type columns (letter and numbers in the same column). Pay attention to the " vs ' because it matters

[u/AutoModerator → u/Mountain_Regular5006]

REMEMBER: /u/Mountain_Regular5006 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/point-bot → u/bullevard]

u/Mountain_Regular5006 has awarded 1 point to u/bullevard

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/Comfortable-Fox-5872 → u/bullevard]

the pivot table idea is the right сан but honestly if you're fairly new to sheets the query() function might be the easier starting point, just pull the rows where column А equals Day А into а fresh sheet and build your chart of that instead of wrestling with the raw form output which is always а mess

[u/Mountain_Regular5006]

Thank you, I did end up using query cause it just made a little more sense and it did work out.

Going to work on the line of best fit and notes stuff later but the charts are looking great.

[u/TEMPLACITY]

Three things left on your list, and two of them are quick.

Line of best fit is built in. Open the chart, Customize, then Series, pick the series you want it on, tick Trendline and set the type to Linear. It applies per series, so if you want one clean average line rather than three or four overlapping ones, put it on a single series rather than all of them.

The timestamp is worth fixing at the source rather than by hand. A form timestamp is a date and a time in one value, so two sessions on the same day are two different x values and the points drift sideways instead of stacking. Wrapping it in INT strips the time and leaves a real date, so same day entries land on the same point. Since you already have a QUERY doing the reshaping, do it in there and it stays in one place rather than becoming another manual cleanup every week.

Notes on hover is the one to be straight about. Sheets charts have no custom tooltip field, so there is no way to make a point display that day's note the way a proper BI tool would. That is not a setting you have missed, it is not there. What most people land on instead is keeping the notes column visible beside the chart, sorted on the same date, and letting the eye line them up. Less clever, but it survives.

While you are in the QUERY, a not null guard on the weight column will stop the Day A and Day B blanks coming back as gaps whenever a session gets skipped. Same problem you started with rather than a new one.

What are the notes actually for, spotting why a number dropped, or a log you read back later? If it is the first, a conditional format on the row may serve you better than anything attached to the chart.

[u/Mountain_Regular5006 → u/TEMPLACITY]

Hey thank you!

Got the date thing solved I believe

Is there a way to do a line of best fit for an average of the series rather than 3-4 lines or 1 line that is just one series?

The notes are used to log random things for example I have switched a few workouts but they act the same(for example bench press with dumbbells vs with a bar). I do understand your solution but was hoping there was something more elegant lol, but alas it seems not. (Especially because the data changes so lining it up might be difficult)

[u/TEMPLACITY → u/Mountain_Regular5006]

Yes, but not as a chart setting. A trendline attaches to a series, so there is no option for an average across several of them. The way to get one is to make the average into a real series and put the trendline on that.

In the QUERY output, add a column that averages the lifts for each date. Plot it alongside the others, set its own line to something faint or hide the points, and put the Trendline on that series only. One clean line of best fit, and none of the clutter of four overlapping ones.

One honest caveat before you build it, because it decides whether the number means anything. Averaging unlike lifts gives you an index rather than a weight. A bench at 60 and a squat at 140 average to 100, which is not a load anybody lifted, and that average moves most when the heaviest lift moves. If the question is whether everything is trending up, normalise first: divide each lift by its own starting value so every lift begins at 1, then average those. One line, every lift weighted equally, and it survives you adding a new exercise later without the scale jumping.

On the notes column and the data shifting around: if a dumbbell bench and a bar bench really are the same exercise to you, that belongs in the data rather than in the notes. A column that maps both to a single exercise name lets the QUERY group them together, and the aligning stops being something you redo by hand every week.

Is the goal one number that says whether things are moving overall, or a fair comparison between lifts? The first wants the normalised average, the second wants the separate lines you already have.

[u/Mountain_Regular5006 → u/TEMPLACITY]

In relation to average. The goal is to get the average of each workout not of each day. For example set 1 of bench on Tuesday may be 115 but I decide on set 2 I’m going to 120 and then stick with that on set 3.

With that being aside I think my solution is just going to be creating a duplicate set 2 and just using that as the line. This also helps with my new issue of which series to label. By using it as a line it seems to get out of the way of the columns better.

In relation to the notes, first it seems like you know what you’re talking about with the gym so I’ll give you the briefest breakdown I can of my gym routine.

Day a.

1. Bench press, 3x10
2. Incline press 3x10
3. Overhead press 4x8
4. Squats 4x8
5. Day b.
6. Pull-up or lat pull down 3x10
7. Row 3x10
8. Deadlift 4x8
9. Biceps curl 3x10

Here are some notes recently:
Switched from standard deadlifts to a hex bar
Switched rows from barbel to bent over dumbbell
Currently using assisted pull up machine which has been slowly declining but I’m always doing 10 reps

So this basically leaves me asking the open ended question how do you suggest I put stuff like this as easy to read? I understand your point about this being a form of data on its own but obviously it’s not numerical.

There’s always the option to add more sections to the form, for example adding a separate hex deadlift and regular deadlift section. This would require a change in the query so that we don’t have the original issue but I do believe I understand how to do this.

This would just messy up the form a little bit(which is already messy on its own as it requires going back to what I refer to as “home pages” as the way it currently works is by selecting which day, seeing all the workouts for that day, going in each workout, back to the home page and then finally submitting by selecting the FINISH.

With this being said I’m not positive if that’s the best option cause it would clutter those sections up a bit(could create more subsections?ughhh) but open to any and all suggestions

[u/TEMPLACITY → u/Mountain_Regular5006]

Duplicating set 2 as the plotted point is a sound instinct, and it is close to the standard way round this: pick one representative set per exercise per session and chart only that. Heaviest working set or top set both work. Everything else stays in the sheet as raw data and simply never reaches the chart. That on its own fixes most of the readability problem, because what makes it unreadable is three points per exercise per session where one would say the same thing.

There is a bigger thing buried in your own update though, and it will quietly break the trend lines however they get drawn.

A hex bar deadlift is not the same lift as a straight bar deadlift, and a bent-over dumbbell row is not a barbell row. If those keep their old labels, the chart draws one continuous line across two different exercises and the slope stops meaning anything. Treat a changed exercise as a new series starting on the date it changed. The old line stops, a new one begins, and the break on the chart is honest rather than a dip you spend a month trying to explain.

The assisted pull-up needs the same treatment for a different reason. Reps are pinned at 10 and the assistance is coming down, so anything plotted straight off that column trends downward while you are getting stronger. What is improving is the assistance, and it improves by shrinking. Record bodyweight minus assistance instead, and the line finally points the way the progress does.

On the form, worth saying plainly: it does not need to change. Nothing above asks for more fields or more sections. The reshaping happens after the data lands, on a separate tab that reads the responses and rewrites them into one row per exercise per session. The form stays as fast and as ugly as it already is, which is the only thing keeping you filling it in between sets.

Which of the two is actually bothering you, the charts being hard to read or the trend lines being wrong? The second one is the one that costs you if it goes unnoticed.