Building a Health & Fitness Dashboard with Google Data Studio

I’ve been making a real effort to improve my health and fitness this year (it’s going pretty well!) and one thing that’s helped is to track key metrics — calories, sleep, exercise etc. For the most part I’ve been using a big spreadsheet, and every morning putting in the entries from various sources:

  • Strava
  • Withings Smart Scale
  • Whoop Activity Tracker
  • MyFitnessPal Food Diary
  • Plus a couple of other bits (e.g. water / alcohol intake)

This has gradually become more and more of a chore to do, but I’ve stuck with it because seeing the data go the right way keeps me motivated, and occasionally seeing it go the wrong way ensures I do something about it sooner rather than later.

I knew there must be a better way though, so I looked for a way to automate some of this, and present it all in a nice fitness dashboard — let’s call it a fitboard.

I was initially inspired by this Ben Collins article showing how to get Strava data into Google Sheets via Google Apps Script — which is kind of a simplified JavaScript. The key difference is that it’s not asynchronous so you trade some performance for simplicity, and for me (not a programmer) not getting lost in callback hell is well worth it!

Anyway, Ben clearly lays out how to grab data from the Strava API using the OAuth Apps Script module. I made a couple of modifications in order to:

  • Check my existing spreadsheet for the last date in order to fetch only new data.
  • For each activity, call an additional activity detail API that returns calories burned for that activity.

The second bit is a bit inefficient initially when getting that first batch of data but when you’re running it every day or two it works OK.

So at this point I had my Strava data in a spreadsheet. Next up was the Withings Smart Scale.

Like Strava, Withings presents it’s (your) data as a publicly documented API, secured via OAuth. Which is great, and in theory should be able to use very similar code to the Strava stuff above. Like any of these things, there are always a few subtleties and it wasn’t quite that straightforward to authorise the application. Fortunately, as ever, someone else had already done the hard work and I came across this sample code here. It was then just a case of updating the input date range, similarly to Strava and I had a sheet with all of the relevant data coming in.

Whilst Whoop doesn’t have a public documented API, the web app does use a client-side, token authenticated API approach. It’s essentially one API that you call that returns everything between the dates that you input — sleep, recovery, activity etc. Again, it looks like someone has done the groundwork here in authenticating the API in an OAuth-like fashion. The reddit article here links to a spreadsheet prototype, as well as a documented API in github.

Again, I just made sure that the date range is limited to new data, and pull everything in to the spreadsheet. So at this point, I’ve got all of my activity, recovery, sleep and weight metrics and just need the data from myfitnesspal to grab calories & macros and I’m done.

When I started looking into this I had a scan of all of the services I’d need to connect to and came to the conclusion that MyFitnessPal seemed to have the most mature API, was publically accessible and would be no problem whatsoever so left it until last. On those last two counts I was wrong, and it seems like they only allow commercial partners to access the API — not pesky customers (boo). My next option was to look for an IFTTT integration — must be supported right? Nope.

Fortunately, someone had done something about all this and created an awesome Python library here. Now my Python skills are even more limited than my JavaScript skills but with the addition of the pygheets library I was able to piece together a rough desktop app that grabs the calorie and macro data from MyFitnessPal and plonks it into the spreadsheet.

Hooking the spreadsheet data up to Google Data Studio is really simple. Each set of metrics had it’s own worksheet within the overall master spreadsheet, with a new row for each day. All the data is organised into the relevant columns, and I make sure any transformation (e.g. into miles / minutes or whatever I needed) was done before plonking into the spreadsheet so Data Studio just needs to do the presentation.

After that it was just a case of adding some basic charts and counters, some trendlines and conditional formatting and I’ve got a nice daily fitboard to inspire me every day!