How I Track My Hikes & Paddles Using Google Sheets

I own multiple smart watches and standalone GPS devices and yet I don't bring them with me when I'm doing long distance hikes. This is because they are a hassle to keep charged and I always seem to forget to start the track when I leave camp leaving my stats for the day off by however many miles.

 

My general advice to new folks is just get a new ish modern smart phone and use that. If FarOut exists for the trail it's everything you need to hike. If it doesn't exist I generally find a gpx for the trail and load it into Caltopo or GaiaGPS. Both applications allow you to generate waypoints every x miles along a track which I then use to make mile markers. I then use these mile markers in conjunction with a Google Spreadsheet.

 

Previously I made this spreadsheet by hand every year and it was bloody annoying. Being the lazy ex software engineer I am I've now created a template complete with a script to generate all the manual bits. Easy eh! See below the cut for how to use it yourself.

First you need to make a copy of the template for yourself. Make sure your already logged into Google Sheets and then open the template from here.

 

 

 

Open the file menu and click Make A Copy 

 

 

Change the name to something that makes sense to you. I usually use something like "<Trailname> <Year> Tracker"

 

You should now have your copy of the template open in a newly created tab. 

 

Because I was tired of the manual process of creating a row for each month and day I created an app script to do it for me.

 

You should see a menu item on the right labeled "Custom Tools" with a single entry  "Generate Yearly Calendar" Click this and you may have to say yes to some authorizations.

 

 

These are just because this is a private script I wrote myself. It doesn't do anything harmful but you're welcome to verify it yourself if you'd like. 

 

If you want to verify what the script is doing you can go into Extensions -> Apps Script and see what all it's doing.
 

There are two functions. The first onOpen() creates the menu item used to run the script. generateCalendar() takes a user input of a four digit year and creates a mileage tracking sheet with a row for every day and a bunch of calculated values.

 

 

These popups only happen the first time you run it. The warnings are there because the script creates a new sheet in the current spreadsheet.
 

 Click Advanced

 

 

 This will expand the dialog. Now click Go to Mileage Tracker Generator (unsafe)

 

 Click Continue to authorize the script. 

 

 

Having authorized the script or if this isn't the first time you've run it you should now see the above prompt. Enter the 4 digit year you want to generate a daily tracker for and hit ok.

 

The script will now take a bit to run. It's creating a row for every day of the year from January 1st to December 31st along with some formatting and formulas.

 

 You should now see a freshly created sheet at the bottom called "Daily <Year>" based on what you entered in the prompt.

 

Note that you can rerun this as many times as you want. Each time it will generate a new sheet.

 

 

Now you have a daily tracker sheet how do you use it? 

 

Month and day should be self explanatory. Days are bolded for weekends since I find that useful and hard to keep track of when I'm out on trail.

 

CNT is just a count of how many days into the trail I am. When you enter a 1 on any given row it should increment everything below that. 

 

Section is just a text label. I use different things for different trails. On the AT I used the state, on the PCT I used logical sections since California is massive and can be divided into Desert, Sierra, and Norcal. Other trails often have other logical dividers. For a shorter trail like the JMT I might just put JMT.

 

Start and End are the main things you'll be filling out every day. After you fill them out for the first day Start will automatically grab the value of End from the row above but you can override this manually if for example you take an alternate or you're on a route like the ADT that enters and exits multiple trails.

 

Mod is how I keep track of off trail miles and reroutes. Additional miles are a positive value while a shortcut is a negative. Total is them End-Start+Mod.

 

For example, if I started at mile 127, finished the day at mile 145, but did a 4 mile total off trail in order to resupply I would have a mod of 4. Total = 145-127+4 which is 22

 

Daily is a calculated field which is how many miles I did that day. As I mentioned above it's calculated as End-Start+Mod.

 

Total is the total number of miles I've done for the entire trail. It takes the Daily value and adds it to the total taken from one row above. 

 

Goal is a manual field and is how many miles I hope to do that day. So for example if it's eary days I may have it set to 15.

 

Delta is calculated from the Goal and Daily fields. It will be positive if Daily exceeds Goal, negative if Daily is under Goal. This just gives me a general feel for how I'm doing.

 

Week is a calculated field adding up all the Daily values Monday to Sunday. This is the most accurate way I've found through experimentation of tracking how I'm doing since any sort of running average was too inaccurate as I did zeros.

 

Video you can delete if you don't want to use. I use this to keep track of what episode of my daily videos corresponded to this day. 

 

Note is just a general note field for whatever memorable thing happened that day. It's useful when I'm looking back at the trip later.

 

 

You can now go in and delete all the dates that you won't be hiking. For example last time I did the JMT I started August 6th. So I would go in and delete all the rows through the end of September. If you have a hard finish date you can do the same for rows after your planned end date or just leave them until you finish.

 

 

 

Each day when I get to camp at the end of the day or the next morning I record whatever the mile marker is in the End field for that day. This should fill out all the grey calculated fields. For example if the first day I made it in 12 miles before I camped but I'd been hoping to make it 15 I would fill it out like so:

 

 

You'll notice a few other sheets in the spreadsheet. I do all of these manually but the Summary sheet is key to my planning.

  

For example here is the Summary sheet from my American Discovery Trail Hike. I started by setting up the states and figuring out how many miles each one was. I then filled out the planned start date, estimates how many miles I was aiming to do each day, and then calculated how many days that would take under Expected End. I could then use that to calculate start dates for each section along with some additional columns at the end playing around with longer or shorter days.

 

I manually fill out the Actual Start / Actual End dates as I go along with Actual Days and then calculate the Actual Average. This is really useful for getting a birds eye view of how the hike is going and keeping an eye on when I can expect to finish.

 

The same concept can also be adapted to any other trail.

 

Gear List is something I've been adding for some of my older trips just as a reference. I've traditionally used something like lighterpack.com but I'm less confident in those staying up over the longer term.

 

Data sources is just where I list any source I come across for the given trail incase I have to refer back to it.

 

We'll also sometimes make a sheet to plan out our resupplies but I've yet to generalize that enough to add it to the template. For an example see here. 

 

 

Feel free to modify anything in the script or individual fields to suit your particular needs. For example when I did the Mississippi Source to Sea river miles count down to the gulf so I had to adjust some of the mileage calculations to account for Start being greater than End. Still the script generating all the days should save some significant time.

 

You can also add any additional fields. If you look back on my various mileage trackers from past trails I often had multiple goal columns or additional CNT fields for when I'd come back to trail after a longer break. You should also be able to rearrange column orders without breaking things. If you do break any of the formulae and get stuck ChatGPT can be a great resource for some of the more tricky conditionals or modifying the script.

 

I used to build these by hand so if this new template has any issues please let me know so I can fix it. I consider this a work in progress and intend to maintain it as I do further trips.