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 the 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. (Strava changed its authentication mechanism, check step 7 too.)
    1. Go to Edit > Current Project’s Triggers.
    2. Add new trigger. Choose “retrieveData” and the frequency that the function should be called. 
  1. Strava updated its authentication mechanism. They used to give you tokens that were forever valid. The new token is valid for 6 hours, and if you want to access your data without authenticating again, you need to refresh after the 5th hour until the 6 hours are over. I use an hourly trigger.

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

How to retrieve data other than AvgHeartRate, MaxHeartRate

Find the names of the data you would like to retrieve from this page (On the right side, under Sample Response):  https://developers.strava.com/docs/reference/#api-Activities-getLoggedInAthleteActivities

Let’s say we would like to add ‘distance’. Add it to the end of the DATA_FIELDS list. Also add a heading for the item in the sheet at the end of the HEADING_FOR_DATA_FIELDS list.

var DATA_FIELDS = [‘start_date_local’, ‘max_heartrate’, ‘average_heartrate’, ‘distance’];
var HEADING_FOR_DATA_FIELDS = [‘Date’, ‘MaxHeartRate’, ‘AvgHeartRate’, ‘Distance’];

Strava returns the distance in meters. I added a code to convert it to kms. If you don’t want that you should remove the code below which is in the convertData function.

//distance is returned in meters, that’s why I divide it into 1000 to have kms
if (field_name == ‘distance’) {
single_data = single_data/1000;
}

Retrieving more data and filtering by type

I updated the code so now you can:

  • retrieve data without heart rate just by setting a variable at the top of the code,
    • var ONLY_WITH_HEARTRATE = false;
  • retrieve more details about the activity, such as the ‘description’ field,
    • var RETRIEVE_DETAILS = true;
  • filter to retrieve activities by only one type.
    • var FILTER_BY_TYPE = “Ride”;

/* If you want to retrieve details such as ‘description’ you need to make the value of RETRIEVE_DETAILS = true; */
var RETRIEVE_DETAILS = false;
var ONLY_WITH_HEARTRATE = true;
/* If you want to retrieves only one type of activity, you should write the activity type below. Ex: var FILTER_BY_TYPE = “Ride”; */
var FILTER_BY_TYPE = “”;

45 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!

    • Thank you for pointing it out. I’ll fix it in the blog post.

  9. Is there a way to pull data from other people? I coach runners and would love to easily pull information from other people’s public data.

    • Hi John, I’m replying quite late but if you’re still interested:

      I checked the API documentation. It’s not possible to get another athlete’s activity even though the activity is public. But it’s possible to get activities of a club. If you have a club or if you manage to make them subscribe to a club you create, it is possible. If you can, let me know. I’ll have a look in how to implement it.

  10. Strava User

    This script was very helpful. Thanks.

    I’m trying to retrieve my run activities’ descriptions. Using the ‘description’ field as an item in the script, I get a return of “undefined” though I expected to see my manual entries I entered on Strava.com’s site.

    Is there a way to retrieve this information?

    Pardon if the solution is obvious as I don’t write code.

    • Hi there,

      The solution is not obvious at all, because the link (method) that returns the activity list doesn’t return the ‘description’. So I had to make another call to another link (method) that returns the activity detail. I updated the code.

      You can see my changes at https://gist.github.com/elifkus/09cd63b3cfbf4e070ecc83b4a4358eaa/revisions?diff=unified . It’s the second change on May 11th, 2019.

      All you have to do is to copy the code, and change the line containing RETRIEVE_DETAILS to:

      var RETRIEVE_DETAILS = true;

  11. Great work, I was lokking for this for a long time. Finally I can export my Strava data and makes the graphs I want.
    I only still struggle with two items. I want to export only my rides, how can I do that? And when I ask for the description of the activitys it says always undefined even when there is a description. Can you help me out?

  12. Hi, I was able to run the script but the returning data for Distance, Time & Pace shows “undifined”, appreciate much if you have some idea’s. Thanks & regards

    • Hi Gerry,

      There must be an error in the code somehow. Unfortunately I can’t guess it without seeing the code.

      But I have good news for you: I updated the code. If you want to get other data than the three I retrieve, you can now set them in the two variables in the header:

      var DATA_FIELDS = [‘start_date_local’, ‘max_heartrate’, ‘average_heartrate’];
      var HEADING_FOR_DATA_FIELDS = [‘Date’, ‘MaxHeartRate’, ‘AvgHeartRate’];

      DATA_FIELDS should match the data field names returned by the Strava API. The length of HEADING_FOR_DATA_FIELDS should match the length of DATA_FIELDS.

      Please check the code for more details: https://gist.github.com/elifkus/09cd63b3cfbf4e070ecc83b4a4358eaa

  13. Charlie Pearson

    Hi Elif,

    I love your script – it is exactly the kind of thing I was hoping to do so that I can integrate data from multiple sources into one dashboard. Unfortunately I am getting an authorisation error when I click the authorisation link from Strava after running “run”. The error message is:

    Error: Error retrieving token: Authorization Error (line 515, file “Service”, project “OAuth2”)

    Any ideas?

    Thanks,
    Charlie

    • No. 😐

      I checked the library version for the OAuth2 library. There are newer versions. To check if my code still runs with it, I created a project and tried to reauthorize. And I could. So there’s nothing wrong with the library.

      You did publish your web app?

      And you entered the script domain name where you created the STRAVA app to get your client_id and client_secret?

  14. Maarten Drenth

    Hi Elif,

    Thanks to your work I also managed to get the Strava data into google sheets! Many thanks for that.

    Could you also help in setting up the coding for the Polar Flow Data?

    I would realy appreciate you help.

    Best Regards,

    Maarten

    • Hi Maarten,

      You’re welcome. 🙂

      I could answer your questions about the Polar API, yet I am not really motivated to prepare a script like this one. I don’t have a Polar Device. But I’ll do my best to answer your questions.

  15. Thanks so much for this Elif!

    Quick question. I see in the code that you’ve put something in place to only return activities that have heart rate included. It took me a couple minutes to figure it out but I had some activities that weren’t being pulled which puzzled me. Then I remembered that you were looking for heart rate specific activities which likely left everything else out. I can see the parts in the code that refer to heart rate values, I’m just not sure what to take out to include all activities without breaking the code.

    I’m starting my triahtlon training after a long hiatus and I was looking for a way to centralize my data into Google Sheets. This works great!

    Thanks so much for all the help.

    Cheers,

    K

    • Hi Karl,

      I’m glad you could retrieve your data. I have updated the script now. If you take the latest version and set ONLY_WITH_HEARTRATE to false, it will retrieve all of the data.

      var ONLY_WITH_HEARTRATE = false;

      Cheers

    • Wow, it looks good. What do you use for visualizing it?

      • Jonathan Melton

        I’m using Power BI. Using the Strava API to pull a wide range of fields into Google sheets, and then pumping that into Power BI for the data model and visualizations. Love being able to set goals and get detailed insight into how I’m tracking towards them, plus other cool insights.

      • I’d never think of using of PowerBI, as I always encounter it in business settings. It always makes me think of boring table like reports. I’ll consider it next time I need it. Thanks!

  16. IF_Strava

    This code is very helpful. Thanks for keep it updated. I’m not sure if Strava support is best to answer this question but thought I’d pose it here in case others have a similar problem.

    Using your above code, I’ve been able to retrieve my activities I’ve made visible to other users. I’m trying to get private activities visible only to me using ‘.setScope(“view_private”)’ and it seems to not retrieve any of my private activities.

    I noted that Strava is requiring OAuth endpoints to use different authorizations and scopes (see here https://developers.strava.com/docs/oauth-updates/ ). So in addition to “view_private”, I’ve tried using ‘.setScope(“activity:read_all”)’, ‘.setScope(“read_all”)’, and ‘.setScope(“read”)’ and none seem to retrieve my private activities.

    Please let me know if you can provide instructions how to retrieve private activities and, if using short-lived tokens, whether your triggers instructions above will no longer apply.

    Thanks.

    • I tried to retrieve my private activities with “view_private” and it really didn’t retrieve my private activities. Now as I write I realize why that might be. I need to get a new token first. Because the scope is set as you take the token. You can try running the reset() function. Because the script saves the token and re-uses it. When you call the reset function it deletes this token (At least it should).

      I could retrieve my private activities with the new authentication.

      I checked the new oauth documentation and tried it in the script. I found out that OAuth2 takes care of all the refreshing functionality. All I had to do was to change the scope. In the new oauth as you said the tokens will be short-lived, namely for 6 hours. The new token (the one after the first) is given after the 5th hour. So I added an hourly trigger.

      • Strava User

        Many thanks for the response. Your directions helped resolve the issue.

        For the benefit of other users, I’m listing some additional steps needed to download private activities.

        1) Use “.setScope(“activity:read_all”)” to make requests for private and public activities; using “.setScope(“read_all”)” and a couple other scopes didn’t work for me but that may have been due to other factors.

        2) Execute the script with your Strava.com Client Secret.

        3) After execution ends, view the log by pressing control-enter or, in the dropdown menu, go to View->Logs.

        4) In the pop-up log, copy the text of the hyperlink to Strava.com and open it in a new tab. The Strava.com site will be requesting your authorization for retrieving private data; grant the API permission and “Success!” should appear.

        5) Run the script again with the same Client Secret and your private activities should be listed.

        I hope that helps.

  17. This is awesome. I got it all working. How big of a change would it be to grab segment leader boards with this script rather than athlete data?

    • Do you want to retrieve a certain single segment leader board or several certain segment leader boards?

      • I’d like to be able to add leaderboards as time goes on. I’d also like to be able to filter the leaderboards by a specific date range.

      • I can get the json response i want in the log when i run it with a modification but I can’t get the the data to populate the spread sheet. It should simply overwrite anything in the spreadsheet since the leaderboard always has all of the current data. Here is the call I’m making: https://www.strava.com/api/v3/segments/12377279/leaderboard?gender=M&club_id=1039&per_page=100

        Here’s the response:

        [19-05-29 20:33:31:179 MDT] {
        “effort_count”: 6,
        “entry_count”: 6,
        “kom_type”: null,
        “entries”: [
        {
        “athlete_name”: “Zach T.”,
        “elapsed_time”: 7167,
        “moving_time”: 7079,
        “start_date”: “2016-07-23T13:44:22Z”,
        “start_date_local”: “2016-07-23T07:44:22Z”,
        “rank”: 1
        },
        {
        “athlete_name”: “Brian S.”,
        “elapsed_time”: 7280,
        “moving_time”: 6203,
        “start_date”: “2016-09-02T23:09:52Z”,
        “start_date_local”: “2016-09-02T17:09:52Z”,
        “rank”: 2
        },
        {
        “athlete_name”: “Matt A.”,
        “elapsed_time”: 9126,
        “moving_time”: 8444,
        “start_date”: “2016-08-13T15:26:08Z”,
        “start_date_local”: “2016-08-13T09:26:08Z”,
        “rank”: 3
        },
        {
        “athlete_name”: “Bryan L.”,
        “elapsed_time”: 9780,
        “moving_time”: 8878,
        “start_date”: “2016-08-14T15:03:51Z”,
        “start_date_local”: “2016-08-14T09:03:51Z”,
        “rank”: 4
        },
        {
        “athlete_name”: “Ian M.”,
        “elapsed_time”: 10018,
        “moving_time”: 8481,
        “start_date”: “2016-08-27T18:15:55Z”,
        “start_date_local”: “2016-08-27T12:15:55Z”,
        “rank”: 5
        },
        {
        “athlete_name”: “Jeff J.”,
        “elapsed_time”: 10031,
        “moving_time”: 8636,
        “start_date”: “2016-08-27T18:15:48Z”,
        “start_date_local”: “2016-08-27T12:15:48Z”,
        “rank”: 6
        }
        ]
        }

        Would this be a simple modification to get this into sheets?

  18. Strava User

    The retrieveData trigger set for each hour seems to get and record onto the spreadsheet the last activity repeatedly until there is a new activity and repeats the process again resulting in dozens of rows in the spreadsheet for the same activities.

    I’m guessing this may be related to an error when I run retrieveLastDate and get a response “TypeError: Cannot call method “getLastRow” of undefined. (line 158, file “Code”)”

    My line 158 is: ” var lastRow = sheet.getLastRow();”

    Is there a way to only resolve this?

    Thanks.

  19. This code super. I got it working and i’m not a programmer.
    At this moment i’m using it to collect standard data like distance just to see if I can reach my year goal. Due to the fact I had no big challenges this year. This is now change because a couple of friends and I want to do a sprint triathlon.
    I’m building a training plan and i want to see if I achieved my training goal for the training. This I want do with te lap function.
    How can I incorporate the lap data in the code?

Leave a Reply to Andy Stow Cancel reply

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