#85: Google Sheets to plan and track a Strava challenge of running 100 km per month
Visma Labs Spain, the company I work for, organized in December (2022) an event for our Strava Running Club: for every km ran/walked (and registered in Strava), the company would donate 0.5 euro to the Madrid Food Bank , with a max. of 1000 euro. So to get to this 1000 euro, we should run in total 2000 km. So if 20 people of our Strava Running Club would participate, that would mean 100 km per runner. So I set for myself this goal: run 100 km in Dec. I saw in Strava there was also this challenge "December Running Challenge, 100 km of running in one month", in which I also participated.
Soon in December, it was clear from the Strava weekly totals from our Strava Running Club, that we would not make the 2000 km in Dec., and it was decided to give us another month, January, for which I also set then a 100 km goal. In January we also did not reach the 2000 km, but in the last extension in February, we reached the goal, so we got the 1000 euro for the Food Bank. Great work, fellow-runners!
For this 2x 100 km challenge, I made a spreadsheet in Google Sheet 2 that has time series charts: one for the planned cumulative distance (with approx. 3 km a day) and another with the real cumulative distance, so it would be easily visible if I was still on track. Here an example with the status of my 100 km challege on 15/1/2023:
NB:
-yellow line: planned cumulative distance
-red line: real cumulative distance, which is on 15/1/2023 under the red line, so I was on track.
-blue line: real distance per day
- the pie-chart shows % distance left (red) vs distance run (blue).
NB: % and km ran/left are same values as goal-distance = 100 km.
Here the sheet/tab in which I entered each day the km's of each run (in red):
The G-Sheets version for Jan.2023 has also data for a 2nd Strava-challenge in which I participated, which goal was
not distance related (100 km in a month), but time-related (230 minutes of activity in 3
weeks). This data is stored in columns I, J etc. and the related sheets/tabs have name '..goal2'
After completing of the 2x 100 km challenge, I made also another G-Sheets char to see the total km's of these 2 months:
This G-Sheets imports the data from the Dec.22 and Jan.23 G-Sheets by using function IMPORTRANGE
and combines the data of these 2 month (sheets) by using the function QUERY. For more info about these 2 functions, see:
https://support.google.com/a/users/answer/9308940?hl=en
https://blog.coupler.io/combine-sheets-into-one/#Combine_sheets_into_one_using_QUERY_Google_Sheets
When I run the function Explore (that uses Machine Learning to help to get insights in the data), it gave me this result/answer:
So it says that my median distance was higher in Jan. than in Dec., which is correct, because in Dec. there were days when I did several runs on a day, each run of a short distance (which I did not do in Jan.).
To make the 100 km challenge of Jan.2023, I did one big run at then end of the month, 15 km, in the city where I grew up, Zwijndrecht (Netherlands), which I also uploaded on this nice Google Maps mashup Wikiloc (a Spanish product that has now 11M members sharing routes):
https://www.wikiloc.com/running-trails/rondje-zwijndrecht-15k-run-125423358
And 2 other specials runs where these 2 races (see also references R1,2):
*1
XXXVIII Cross de invierno A.D. Ciudad de los Poetas 2022, Parque Dehesa de la Villa, Madrid, 6K run in a park close to where I live:
https://www.strava.com/activities/8261806442
*2
San Silvestre Vallecana 10k 2022 31 Dec. Madrid, 10K run, the best christmas-gift from my work (who paid the registration-fee and also for the children of employees who wanted to run the
San Silvestre Mini):
https://www.strava.com/activities/8318453460
This race is not just for fun, it also supports foundations that fight agains childhood cancer (Unoentrecienmil) and childhood obesity (Gasol Foundation):
https://www.sansilvestrevallecana.com/dorsal_solidario_en.php
Thanks to this post on Wikipedia I learned that this 'last race of the year' tradition is not just something in Madrid, but also in other countries as Brazil (where it has its roots), Portugal and Italy.
And in this article of Runnersworld I saw that also in the Netherlands (Soest) there is a Sylvester by Night run/cross.
The goal of running 100 km a month was challenging, but knowing it was a good cause (the food-bank), I was determined to complete it. As I heard Jordan Peterson saying in this talk How to Set Goals the Smart Way (min.9:35):
"He who has a why can bare almost any how", a line from Nietzsche. I checked for the original phrase (in German) which has a nice addition:
"Hat man sein warum? des Lebens, so verträgt man sich fast mit jedem wie? - Der Mensch strebt nicht nach Glück; nur der Engländer tut das."
If my spreadsheet is usefull for you, it would be nice if you could share how you used it in a (non-anonymous *) comment below.
* I ask for non-anonymous comments because when I allowed also anonymous comments, I got a lot of spam.
source pic: https://rb.gy/mqqxl2
Credits
The runner-icon that I used in my G-Sheets is from:
https://www.flaticon.com/free-icon/running_233064?related_id=233064&origin=search
Downloads
NB:
I made the G-Sheets on the G-Drive of my work (to share it with other collegue-runners), but unfortunatley it was not possible to copy the file to my personal G-Drive in the original format (.gsheet). When downloading it, it was converted to Excel, and when I then uploaded to my personal G-Drive and saved it back to G-Sheets format, some things were not exactly the same as in the original G-Sheets (e.g. pivot-charts).
G-Sheet Dec.2022
NB: see also "Embeded G-Sheets" below, and for more info on how to publish a G-Sheets (generated the embedded code/i-frame), see:
https://www.youtube.com/watch?v=cHXpCaZA7Bw
References
[R1]
Race Dehesa de la Villa:
http://adcpoetas.blogspot.com/p/xxxi-cross.html
https://www.flickr.com/photos/adcpoetas/52571583846/in/album-72177720304540380/
https://www.flickr.com/photos/adcpoetas/52580444869/in/album-72177720304677763/
[R2]
Race San Silvestre
https://www.sansilvestrevallecana.com/popular_en.php
https://www.facebook.com/sansilvestrevallecana/videos/565301878370053/
https://www.facebook.com/100064108532446/videos/3079732918986087/?__so__=permalink
https://www.flickr.com/photos/158376798@N03/52601367997/in/album-72177720304924953/
https://www.flickr.com/photos/158376798@N03/52602372703/in/album-72177720304924953/
https://www.marca.com/atletismo/san-silvestre-vallecana/2022/12/28/63ac03d1268e3e39138b45ad.html
[R3]
https://www.chasetheladder.com/
The free version of Strava keeps the total km-run per week, so on Monday you start at 0 again.
But this nice Strava add-on solves that, it adds to each activity (run) some stats as 4-week
summary.
[R4]
SMART goal setting
https://www.mindtools.com/a4wo118/smart-goals
https://www.youtube.com/watch?v=PCRSVRD2EAk
[R5]
Running 100 km a month
https://www.asinglestep.co.uk/resources-and-inspiration/running-100km-in-30-days/
https://medium.com/@ikemoobioha/6-life-lessons-i-learned-from-running-100k-in-a-month-29e75a900aa8
Embedded G-Sheets
