Retrieving your Strava data with Google App Scripts

I run whenever I can. I use Strava to record my run. I check the speed, my heart rate, and the ranking of my run. I like looking at all the graphs. There is one data I’d like to check but doesn’t exist in Strava. I would like to see the graph of the average heart rates vs runs and the max hearts vs runs.

Strava has an API through which you can retrieve all sorts of data. I’ve been thinking of doing this for months but I’ve been postponing it because you have to connect to the API with OAuth2. It has been always a pain in the arse for me to authenticate through OAuth2. If something goes wrong and you can’t connect, you never receive a meaningful error because of ‘security reasons’.

Nevertheless last week, I finally decided to work on this. I decided to use Google App Scripts to retrieve the data and a Google Spreadsheet to save and visualize the data. To my surprise I found a OAuth2 library by Google for Google App Scripts. I found an example code for Github. I gave it a try. And it worked like a charm.

I retrieved my runs. Extracted the running time, average heart rate and maximum heart rate data. Inserted it in a script. Plotted it in the spreadsheet. To have up-to-date data, I added a trigger that retrieves the data every hour to Google App Scripts.

Here is what you need to do if you’d like to implement this yourself.

  1. Create a Google Apps Script project.
    1. Open Chrome and install the Google Apps Script extension.
    2. Open Google Drive, and click New > More > Google Apps Script.I called the file StravaData.
  2. Create a Spreadsheet for storing the data and for the graph. I named this one StravaData as well.
  3. Now the very big step: Connecting to the Strava API through OAuth2.
    1. I opened the Google Apps Script project I created. This is what you should see:
    2. I copied the sample code and changed it for Strava.
    3. For the code to run we need to add a library. In the Google App Scripts menu go to Resources>Libraries and Add Library with the script id 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF.  (See the Setup part in https://github.com/googlesamples/apps-script-oauth2)
    4. To be able to connect to Strava, I need to have a ClientId and Client Secret from Strava. So I created an application in the Strava Developer Web App.
      1. Login to Strava.
      2. Go to https://www.strava.com/settings/api . Create an app.
      3. Copy the the Client Id and Client Secret into the script.
    5. For OAuth2 to run we need to have a link that we can send to Strava that it can call after the authentication. This is called the callback.
      1. First we need to publish our Google App Scripts project. You can do this by going to Publish > Deploy as Web App. In the window that opens choose Project version as “New”, Execute the app as “Me”, and “Who has access to the app” should be “Anone, even anonymous”, so that the Strava Authentication can call the callback link.
      2. There is one more setting in Strava. You need to tell Strava the domain of the callback link. Go to https://www.strava.com/settings/api . Scroll down the page and enter “script.google.com” for “Authorization Callback Domain”
    6. Now the final step. Run the function that connects to Strava.
      1. In the menu above the Google App Scripts project, choose run and click the Play icon.
      2. If you read the code in the run function, you’ll see that it logs the link for Strava authorization. So now we have to check our logs to find the link. Go to View > Logs. A new window will open up. You’ll see the log and the link. 
      3. Go to the link in the logs. This will authenticate against the Strava API. You should be redirected to the page that says “Success!”
  4. Retrieve the data.
    1. Before you retrieve the data you need to enter two more information about the Spreadsheet. First you need to enter the SPREADSHEET_ID. You can find the spreadsheet id from the spreadsheet url. The part between /d/ and /edit/ is the spreadsheet id. https://docs.google.com/spreadsheets/d/<SpreadsheetId>/edit#gid=0

      var SPREADSHEET_ID = “1-u1X8jS8rfasfasdfasfyozUd9aqKnwftAaoSuIgkJI4”;
      var SHEET_NAME = “Sheet1”;

      The Sheet_Name is Sheet1 if you haven’t changed the default name.

    2. Run the function to retrieve your data. Choose the function “retrieveData” and run it. 
    3. Go and check your spreadsheet. You should be able to see the data.
  5. Now you can easily visualize your data in the Spreadsheet.
    1. Go to Insert > Graph.
    2. I chose the line graph. And this is the result.Yaayyy!
  6. Whenever you’d like to retrieve new data you need to run the retrieveData function. You can also setup a trigger in the Google App Script project, so that the data is retrieved automatically. I setup an hourly trigger.
    1. Go to Edit > Current Project’s Triggers.
    2. Add new trigger. Choose “retrieveData” and the frequency that the function should be called. 

That’s it. Let me know if you run into issues.

 

How to retrieve data other than AvgHeartRate, MaxHeartRate

First find the names of the data you would like to retrieve from this page: https://strava.github.io/api/v3/activities/

Let’s say we would like to add ‘distance’.

You need to add ‘distance’ to the code as below:

 

function convertData(result) {
var data = [];

for (var i = 0; i < result.length; i++) {
if (result[i][“has_heartrate”]) {
            var item = [result[i][‘start_date_local’],
result[i][‘max_heartrate’],
result[i][‘average_heartrate’], result[i][‘distance’]];
data.push(item);
}
}

return data;
}

And you need to fix the header in insertData

function insertData(sheet, data) {
var header = [“Date”, “MaxHeartRate”, “AvgHeartRate”, “Distance”];
ensureHeader(header, sheet);

var lastRow = sheet.getLastRow();
var range = sheet.getRange(lastRow+1,1,data.length,3);
range.setValues(data);
}

If you do this you will realize that the distance is returned in meters. To convert it to kms you would need to divide the distance into 1000. So the code would look like this:

function convertData(result) {
var data = [];

for (var i = 0; i < result.length; i++) {
if (result[i][“has_heartrate”]) {
             var item = [result[i][‘start_date_local’],
result[i][‘max_heartrate’],
result[i][‘average_heartrate’], result[i][‘distance’]/1000];
data.push(item);
}
}

return data;
}

16 Comments

  1. Hi!
    This is brilliant work!

    I ran into a problem though…

    When I select “retrieveData” and run it I get the following:

    The coordinates or dimensions of the range are invalid. (line 112, file “Code”)

    it relates to this line: var dateCell = sheet.getRange(lastRow, 1);

    any ideas?

    • Hi Colin,

      Thank you!

      That happens when the getRange receives invalid input. That was because lastRow’s value was 0, which is a bug in the code. I fixed the gist. The retrleveLastDate function should be changed to:

      function retrieveLastDate(sheet) {
      var lastRow = sheet.getLastRow();
      var unixTime = 0;

      if (lastRow > 0) {
      var dateCell = sheet.getRange(lastRow, 1);
      var dateString = dateCell.getValue();
      var date = new Date((dateString || “”).replace(/-/g,”/”).replace(/[TZ]/g,” “));
      unixTime = date/1000;
      }
      return unixTime;
      }

      And that’s the change:
      https://gist.github.com/elifkus/09cd63b3cfbf4e070ecc83b4a4358eaa/revisions

      Thank you for writing.

      I’m sorry for replying so late.

  2. Drew Wilkins

    Thank you for putting this together! I first saw the github gist and the edits you made to it, but I’m no programmer, so I didn’t feel comfortable trying this. I look forward to giving this a shot this weekend.

    • Drew Wilkins

      Hi Eli, I implemented the script as you showed and it works, however, I only receive 30 activities. If I clear the sheet and rerun the script, I still only get the same 30 activities (they are the first 30 that I ever uploaded back in 2015). Any ideas?

  3. Drew Wilkins

    I found the solution. To get the maximum number of activities per call (200), the activity resource request must include a “per_page=200” argument. So line 80 of the github code would look like this:
    var url = ‘https://www.strava.com/api/v3/athlete/activities?per_page=200&after=’ + unixTime;

    For more than 200, then just run the code a couple of times and it will append the additional activities. I’m not a coder so there may be a better way to do this.

    • Hi Drew,

      That’s how I would do it.

      You also could simply rerun it without doing any changes, the code checks the last element in the spreadsheet and retrieves the activities after that event.

  4. Drew Wilkins

    Hi Elif,

    I came back to the code to pull some more strava data and I get this error when I run the function retrieveData: “TypeError: Cannot find function replace in object 1141467333. (line 116, file “Code5”)
    Here’s line 116:

    function retrieveLastDate(sheet) {
    var lastRow = sheet.getLastRow();
    var unixTime = 0;
    if (lastRow > 0) {
    var dateCell = sheet.getRange(lastRow, 1);
    var dateString = dateCell.getValue();
    var date = new Date((dateString || “”).replace(/-/g,”/”).replace(/[TZ]/g,” “));
    unixTime = date/1000;
    }
    return unixTime;
    }

    Any ideas?

    • My guess is that this line doesn’t return any data.

      var dateCell = sheet.getRange(lastRow, 1);

      That might happen if you added the new data at the beginning of the row rather than at the end. The 1 above is for the column number. Can you also send the code that you changed?

  5. Thanks so much for doing this! I have an elaborate tracking sheet that I made, and was using Jonathan O’Keeffe’s “Strava Annual Summary” tool to populate it. It was a multi-step process, but was better than typing it in. That tool quit working for me, and after a couple of weeks of manual entry I found your code, then this page, and now I have automagic import at the press of a button!

    I made quite a few changes to get the 14 fields I wanted for tracking my cycling, but your documentation was thoroughly helpful. This was my first time doing any code in Google Apps.

    • I’m glad to read this. 🙂

      Let me now if you have any questions. (Though I might be slow to respond like now.)

  6. Thanks a lot for doing this! I do have a quick question tho’. How do I make it retrieve private activities? I’ve tried a few things with properties but couldn’t figure it out.

    • I think I figured it out. I had to call setScope (“view_private”) so now my getService looks like so

      function getService() {

      return OAuth2.createService(‘Strava’)
      // Set the endpoint URLs.
      .setAuthorizationBaseUrl(‘https://www.strava.com/oauth/authorize’)
      .setTokenUrl(‘https://www.strava.com/oauth/token’)

      // Set the client ID and secret.
      .setClientId(CLIENT_ID)
      .setClientSecret(CLIENT_SECRET)

      // Set the name of the callback function that should be invoked to complete
      // the OAuth flow.
      .setCallbackFunction(‘authCallback’)

      // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(PropertiesService.getUserProperties()
      .setScope (“view_private”)
      }

      • Thank you for sending the code.

        I added it to the script.

  7. Hi. Excited to use the script, but I can’t seem to get any data back. My log states ever time i run retrieveData “[18-05-26 10:37:27:878 PDT] No new data with heart rate”. I don’t use a heart rate monitor with Strava, so all my activities will not have heart rate data. I can see the successful api calls when I log into my Strava app, so I know the script is executing the api calls. But no data is being populated in my spreadsheet.

    Any ideas?

    Thanks

    • Hi Jon That’s because I do a check whether the data contains heart rate data in the convertData function, if it doesn’t I don’t use it. You’ll have to remove that check.

      Here is the code:

      function convertData(result) {
      var data = [];

      for (var i = 0; i < result.length; i++) {
      //The line below is where I do the check for the heart rate
      //the closing ‘}’ after data.push(item) belongs to this check

      if (result[i][“has_heartrate”]) {

      var item = [result[i][‘start_date_local’],
      result[i][‘max_heartrate’],
      result[i][‘average_heartrate’]];

      data.push(item);

      }

      }

      return data;
      }

      And here is how it looks when the check is removed:

      function convertData(result) {
      var data = [];

      for (var i = 0; i < result.length; i++) {

      var item = [result[i][‘start_date_local’],
      result[i][‘max_heartrate’],
      result[i][‘average_heartrate’]];
      data.push(item);

      }

      return data;

      }

  8. I figured it out. I have to change the data.length value to match the number if fields I am requesting.

    var range = sheet.getRange(lastRow+1,1,data.length,3);

    Thanks!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.