Connecting Strava and Google Sheets using IFTTT

Update (2016-06-19)

Bad news: My cheeky hack to get Strava data into Google Sheets is no longer necessary.

Good news: Things got a whole lot simpler last month when IFTTT officially started supporting Strava! Instead of following the instructions in this article, I strongly recommend using this IFTTT recipe.


Last year (2014) I made a goal of cycling or running to work at least 100 times. I ended up doing 172!

This year, I moved from Canada to London, but not before doing over 252 commutes! (Note: I stretched the definition of commute to include any time I saved at least 5km of driving or transit)

I won’t reach 300 this year (my original goal) but instead of going sans goal until 2016 (I am very motivated by goals, even if they are completely arbitrary), I decided to set a new goal.

Cycle 1000km on my “new” bike before the end of the year.

So I made a quick chart in Excel.

newgoal excel

But then I realized that this would be a great opportunity for some tinkering. You see, the Excel chart above requires the dreaded MANUAL DATA ENTRY. If there was a way for me to use my beloved Strava to automatically update a similar chart, I’d be set.

So I tried to plug into the Strava API. But then got a little overwhelmed when it started asking me about authorization codes and application names.

I then went to my favorite “automate everything” site, IFTTT. While they don’t support Strava directly, they do support RSS feeds, and FeedMyRide allows you to create an RSS feed from your Strava activities. IFTTT allowed me to easily create a recipe for hooking up RSS to Google Sheets.

Add in some parsing, some array formulas, and a nice chart et voila: As you can see here, I now have automatic updating of my progress toward my goal!

1000km complete!

I’m a little behind on my goal as I write this(November 24), so stay tuned to see if I manage to catch up!

Update (01/03/2016)

I did it! I ran into a few hiccups along the way, such as four (mostly) unrelated flat tires in two days, an unexpected house move closer to work, and a cold/flu that lingered for a couple weeks, but my mileage at midnight on December 31st, 2015 was 1011.9km*.

mission accomplished!

Mission Accomplished! Me celebrating hitting the 1000km mark on a sunny New Years Eve day at Tower Bridge

*One little disclaimer: I had to cheat a little by including the approximately 50km I cycled in Copenhagen while on vacation there, so technically my new bike’s 2016 mileage was just under 1000km.

Anyway, I found it a very powerful motivator to have my goal out in public for all to see (even though, if we’re being honest, I’d be surprised if more than two people saw said goal). Also, I really enjoyed trying to keep the blue line above the red line in real time. And I absolutely loved exploring the sights of London. I may not keep up the pace of 500 km per month in 2016 but I can see myself continuing to cycle to work most days.

Advertisements

16 comments

  1. Karen · January 3, 2016

    Very cool that your bike commuting goals inspired you to ride more, Shane. I just started using Strava to track my bike commutes this fall, so I could contribute to city planning input. Now I need to check out your automating system and see if I can set one up for myself! I have wanted to figure out a good use for the IFTTT anyway.

    Like

    • sgryzko · January 5, 2016

      Cool! If you need a hand hooking things up, let me know!

      Like

  2. inseiffolliet · January 9, 2016

    Shane,

    That’s PERFECT! EXACTLY what I’m looking for. I’ve downloaded the recipe, how do I link MY Strava and My gDrive? The latter is already connected to IFTTT but not Strava.

    Ross

    Like

  3. inseiffolliet · January 9, 2016

    Ignore that. Too late at night, just seen it. Will check it all out tomorrow. Great work

    Like

  4. ffolliet (@ffolliet) · January 9, 2016

    Would SO love this to work for me but struggling. Can I email for advice or is it better to post here for others?

    Like

    • sgryzko · January 9, 2016

      Let’s figure it out by email (sgryzko@yahoo.ca) then I’ll update my post to clarify things. Email me any time.

      Like

  5. Dean · February 23, 2016

    This is awesome, I have been doing manual entry for years (oh dear) [http://www.bishopaucklandbiking.co.uk/p/distance.html] and this has just about made my day. I love IFTTT, so thanks for pointing out feedmyride, I had no idea.

    Also, well done on setting and achieving your goal!

    Like

    • sgryzko · February 23, 2016

      Cheers, I hope this helps you! And nice work on racking up those miles!

      Like

  6. Will · March 11, 2016

    Excellent post, thanks for the info! I’ve got the IFTTT recipe working to go from Strava to my Google Sheet and that part is all good. Now I’m trying to figure out a good (i.e. relatively easy) way to parse the data in the sheet. That seems harder than I expected since the data basically comes in as a single row. Can you share any hints or tips on how you parsed the data in to a usable form? Thanks!

    Like

  7. JMRD · July 5, 2016

    If only the IFTTT recipe used moving time instead of elapsed time…. I have absolutely no interest in the time spent waiting for the lights to change.

    Like

    • sgryzko · July 6, 2016

      Can’t that be accomplished by turning on Auto-Pause in Strava? Or are you saying that you do have Auto-Pause but your activities in Strava don’t match what comes out of ifttt?

      More info on Auto-Pause: https://support.strava.com/hc/en-us/articles/216917437-Auto-Pause-iPhone

      Like

      • JMRD · July 6, 2016

        Even with Auto-Pause on (default for rides) Strava saves two different times; Elapsed Time and Moving Time, but the recipe only exposes “ElapsedTime” and “ElapsedTime in seconds”, not moving time which is what I need. When I look at the RSS feed at feedmyride, THEY do contain moving time and NOT elapsed time (I forgot to turn off strava a day in last week when commuting to work so moving time was normal 27 minutes, but elapsed time was 3 hours 😉 So I can use that day to look for..

        Like

      • sgryzko · July 6, 2016

        So what you’re saying, JMRD, is that there is still a use for this article! If Strava doesn’t expose MovingTime but the RSS does, then just follow the instructions here instead of using the Strava channel in IFTTT. Hope that works for you!

        Like

      • JMRD · July 6, 2016

        Precisely. The article is useful as the IFTTT does not work in a meaningful way – I am not sure how it is built, but I’ll poke around in the Strava API and see if I can get it to give me the data I need in a somewhat structured and parseable format. But the RSS way is a good start

        Like

  8. Pingback: The UX of a great presentation | Shane Gryzko
  9. Shannon · January 27

    Interesting information. A bit over my head at the moment, however, always hope!
    Congratulations on your biking success! Looks like your making the most out of London town & Europe. Great to see! Keep having fun!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s