How to connect the Strava API with Google Sheets and Data Studio

74 thoughts on “How to connect the Strava API with Google Sheets and Data Studio”

1. Aw man, this is FANTASTIC! Thanks so much for your work! Agreed–love Strava, but I am a data nerd and have it go to SmashRun automatically. Love the thought of getting my data to Google Data Studio.

   But…I got stuck at this step: Login to Strava and go to Settings > My API Application

   So I have no option for My API Application. I have My Apps, but nothing where I can add a custom API, I am guessing.

   1. Hi Chris,

      Hmm, it should be an option in the list of options on the left side , under “My profile”, “My Account”, …etc. and it’s the final choice. This is on desktop btw. It’s at the url: https://www.strava.com/settings/api

      There you should find a “Create App” button…

      Hope this helps.

      Cheers,
      Ben

2. Hi ben, tnx for the nice post.. tried but I had a “Cannot call method “getRange” of null” error on data acquisition..what could have happened?
   Thanks

3. Resolved, a problem within the name…thn the same

   1. how is that?

      I have this in line 36: sheet.getRange(sheet.getLastRow() + 1, 1, stravaData.length, stravaData[0].length).setValues(stravaData)

      And the error is “TypeError: Cannot read property ‘getRange’ of null (línea 36, archivo “Código”)

4. Hi Ben,

   Is this still working for you after the October 15 change at Strava?
   https://developers.strava.com/docs/oauth-updates/

   I only get the data into Google Sheets if I have the access code and pass it directly with the URL:
   var params = ‘?after=1546300800&per_page=200&access_token=2f9…

   The weird thing is that the OAuth from Google doesn’t seem to work for me, I am never confronted with the pop-up.

   So I bypass
   if (service.hasAccess()) {

   and use:
   if (scope == “activity:read_all”) {

   And then it works.

   So I do have clearly an authentication problem…
   Not sure if you have any idea…

   1. Hey Ralph – It’s possible! It’s still fetching all my data (picked up my last walk on Thursday last week) at the moment. I’ll revisit when I get a chance 🙂

      Cheers,
      Ben

5. I bet it is broken.

   I found this:
   https://github.com/gsuitedevs/apps-script-oauth2/pull/83

   I will do more digging on the weekend…

6. Thanks Ben, I went over all the oAuth stuff on the weekend. My problem can’t be that hard to fix. I will give it a new shot this week.

   cheers!

   1. Hey Ralph,

      Judging from your Twitter posts I think you got this working right? Feel free to share a link if you’ve posted working code anywhere like GitHub.

      I haven’t had time to revisit my code yet!

      Cheers,
      Ben

   2. Hello Ralph,

      I’m having the same issue after the October end. I can’t find where is the problem. I guess it is related with the refresh token and how the library handle it but, I’m stuck. Could you share the solution in the case you found it?

      Thanks in advance.
      Jorge.

      1. Hi,

         Well, I found the solution to the issue. After October end, the scopes changed. It’s necessary change the old ones by the new ones (more info on https://developers.strava.com/docs/oauth-updates/#migration-instructions).

         In my case, I replaced the old scope:

         .setScope(‘public’)

         by the new ones:

         .setScope(‘read’)
         .setScope(‘activity:read’);

         and it worked!!
         Jorge.

         1. Hi Jorge, Ben

            I was so busy getting this work that I didn’t check back here.

            I think the scope was indeed the problem, I did very few changes and this was one of them. Since then it is working.

            Ben, code is not yet published but will be here by the end of the week:
            https://github.com/spandl/DS-strava-connector

            1. Great work, Jorge and Ralph! Thanks for sharing your findings and code here.

7. I haven’t been able to get data since the migration period ended in Oct. I’m not a strong coder so I look forward to someone with actual coding skills to figure out how to adapt Elif’s code for me. I left her a note as well and will report back if she responds with a fix.

8. Excited to try this out. I got as far as authenticating, but came back to Google Sheets and no “Stava App” menu option.

   1. Solved the Strava App button. Not sure what happened, but I ran it and now appeared.

   2. I had the same problem. I did not clear the function text.
      function myFunction() {

      }
      I actually did clear the text but then got an error and put it back and then everything seemed to work.

      What I found is clear the text and then look for the little Save Project menu item. I wasn’t saving the project because I thought it was saving automatically, like everything else does. This save is manual.

9. Hi Ben

   Thanks for your script and it works and I am able to get all the data I need. However, is it possible to get the Strava authorization URL from log and pop up to the browser? (like the google authorization). Since I want to share the sheet to someone else.

   1. did you find solution ? I got the same problem, i want to share with a friend a new sheet do follow his activity but i meet some problem with token.

10. I am having an issue with the first lines of code:
    Exception: Cannot call SpreadsheetApp.getUi() from this context. (line 4, file “Code”).

    Does it have something to do with Google deprecating containers?

11. Hi Ben

    Absolutely love this whole project and I’ve used it to connect to my Strava account which will hopefully help motivate me with my training!

    For some reason when I first used the ‘Get data’ option from the custom menu inside the Sheet before authentication, it gave an error message (TypeError: Cannot read property ‘forEach’ of undefined at getStravaActivityData(Code:24:8)) and when I checked the logs they were blank. Then I ran the callStravaAPI function manually from the Script File and it worked fine and I could get the authentication URL from the Logs. I’m not sure if this is just me (sometimes my logs take a while to load?) or something other people have found?

    Thanks so much for sharing this.

    P.S An online course on Data Studio would be great!

    1. Great procedure Ben, many thanks.
       I’ve followed the instructions, however I’m getting the same error
       “Cannot read property ‘forEach’ of undefined at getStravaActivityData.” and can proceed further.
       Any fix or section to validate please?

       1. I think this is the step where you need to copy the URL from the log and paste into another tab to authorize the App on Strava’s side. I just did this and I think that was the last error I had before I got it working.

       2. I had a similar error and managed to fix it as follows:
          – Open a browser tab and go to script.google.com
          – Click on “My executions” on the left
          – Click on any of the executions that are listed as “failed” which should expand to show some log info
          – Copy the URL from there into a new browser window

          Hope this helps.. .I am a runner with zero experience with programming and APIs..

    2. Thank you! I ran in the same problem [TypeError: Cannot read property ‘forEach’ of undefined] but your workaround proved to work!

12. Hi Ben,
    Thanks for this, it has totally changed how I track my Strava Activities. I was able to get this working the first time through, even though I have never coded or made a script before in my life. I used your instructions elsewhere on your blog to set up a trigger to automate this daily, but it imports (duplicates) the old data along with the new data, over and over again. You said:

    “You’ll want to filter out the old data to prevent ending up with duplicate entries. You can use a filter loop to compare the new data with the values you have in your spreadsheet and discard the ones you already have.”

    Can you describe how to go about doing this?

    Thanks!

    1. I just implemented the base strava code above! Thanks Ben. I have the same question as sfire. How do I only update the strava activities, not import all activities again?

13. Ben this is a great project and I have implemented it for my own activites.

    Question – is there a way that I can utilise multi-user authentication?

    Would like to pull in extra data from my running club, just a small user base so they would be happy to provide authorisation for me to access their data the issue is how I can handle that via this method? Thought is I can loop through list of userIDs to pull each atheletes data. Any tips much appreciated! I have used this method to interogate the clubs functionalilty of the API but the data it returns doesn’t have a timestamp which impedes my usage.

    1. Hi Robert,

       Were you able to get this going?

       I am in the same boat and need to extract every member in the clubs’ activities.

14. This is awesome and I have it working now.

    If I wanted to do the same thing for a segment leaderboard would it be a simple change?

15. Should this work for extracting Strava Club activities as well? I keep getting an error message saying that the service is not defined. I’m probably doing something stupid, this is all new to me!

16. hello, i want to get retrive data start latlng and end latlng that have 2 value for each column . for start latlng the value is 3.0251736 and 115.43636273. i call it but it only retrieve the first value. how can i get both of the value ?

    1. Hi there,

       Im not sure if you ever got this to work but you might want to try this?

       segments.start_latlng[0],
       segments.start_latlng[1],
       segments.end_latlng[0],
       segments.end_latlng[1]

17. I’m still getting this error when I run the script.

    TypeError: Cannot read property ‘getRange’ of null

    I’ve read through every post in this thread and followed all the tips but I haven’t been able to get it to work. Any help is appreciated…

    1. Sheet1 – name sheet

       1. Thank you!

18. For Strava Club data, I have changed a line in the call Strava API to:
    var endpoint = ‘https://www.strava.com/api/v3/clubs/{id}/activities’;

    Where {id} is your 6 digit Club ID from the link to your club page on Strava.

    I can then populate the spreadsheet with activity.type, activity.distance, activity.moving_time, activity.elapsed_time, activity.athlete.firstname, activity.athlete.lastname.

    That is really helpful for me but lastname is only an initial and I can’t get activity dates. I’ve done this by trial and error – can anyone with more knowledge figure out how to extract additional club activity data?

    1. Thank You Dan, your comment help me alot

    2. Still getting the same issue of type error like Matiiss. Any advice?

19. Hi,
    Struggling to get the data as on running the as on running function : getStravaActivityData

    I get the error TypeError: Cannot read property ‘length’ of undefined

    its for line : sheet.getRange(sheet.getLastRow() + 1, 1, stravaData.length, stravaData[0].length).setValues(stravaData);

    any ideas?

    1. Please try replacing this part:

       var response = JSON.parse(UrlFetchApp.fetch(endpoint + params, options));

       With this:

       var response = JSON.parse(UrlFetchApp.fetch(endpoint + params, options).getContentText());

       1. Hi John, I’ve tried your fix but still get the same error as Matiss!

20. Hi Ben,

    Thanks for this detailed explanation on this setup.

    I keep struggling though when clicking on “Get Data” as the app keeps displaying the following error message:
    “ReferenceError: getStravaService is not defined”

    Can someone support me with this? It might be very basic as my tech knowledge is pretty limited.

    Many thanks !

21. Hi Ben, thank you very much for your enormous work.

    I have read both cases of OAuth API calls, this one and GitHub one. Why do you have two different callback url? In Strava was just “script.google.com” and for GitHub was “https://script.google.com/macros/d/{SCRIPT ID}/usercallback”

    Are there any criteria to use one or the other?
    Thank you.

22. Hi Ben. I have been struggling making a POST using fetch. I am getting the error “grant_type is a required parameter” but I believe my code it is fine. In this case grant_type is actually an “authorization_code” . So I will really apreciate if some one can point why is it not working. Here es my code:

    function makeRequest() {
    var driveService = getDriveService();
    var response = JSON.parse(UrlFetchApp.fetch(‘https://api.mercadolibre.com/oauth/token’, {
    method: ‘POST’,
    headers: {
    ‘accept’: ‘application/json’,
    ‘content-type’: ‘application/x-www-form-urlencoded’
    },
    body: ‘grant_type=authorization_code&client_id=……………client_secret=…………..&code=……..&redirect_uri=https://docs.google.com/spreadsheets/d/………….’
    }));
    return response
    }

23. Thanks for this guide! This guide has made me more comfortable with using APIs and creating my own personal training tracker 🙂

    I linked it into my Google Calendar to create an Event of each activity with a link to the Strava activity + stats in the description. It was super satisfying having Strava validate the program’s results at the end of the year with the 2020 Year In Sport.

    I recently exceeded the limit of the 200 activities per page and figured I should put my solution here if anyone else ran into the same problem as me.

    I modified params to include page=1 and then added
    var params2 = ‘?after=1492364007&page=2&per_page=200’ //(use the same epoch timestamp you used in params)

    Then later on when it fetches the responses, I just used a loop through each output from the 2nd page’s JSON parse, then pushed each list item to a new variable. This is my modified code below:

    var response1 = JSON.parse(UrlFetchApp.fetch(endpoint + params, options))
    var response2 = JSON.parse(UrlFetchApp.fetch(endpoint + params2, options))

    var responseCombined = response1
    for (var i = 0; i < response2.length; i++) {
    responseCombined.push(response2[i])
    }
    return responseCombined;

    1. Hi Mitchell,

       What do I have to write when I want to add more than 2 pages?
       So if I go to 3 or 4 pages.

       Thx

       1. Replace each “X” with the page number you want to include. You will need to do this for EACH additional page.

          1. Add an additional params variable for each additional page and only change the variable name and the page number within the string (“page=3” and on)
          ex.
          var paramsX = ‘?after=1492364007&page=X&per_page=200’

          2. Add an additional response variable for each additional page, referencing the corresponding params variable:
          var responseX = JSON.parse(UrlFetchApp.fetch(endpoint + paramsX, options))

          3. Create a new for loop for each additional page to append its data to the combined response variable, and place them AFTER (not within) the previous loop. Only change the response variable names. These loops must be placed in ascending order by page 1,2,3,etc.

          for (var i = 0; i < responseX.length; i++) {
          responseCombined.push(responseX[i])
          }

    2. Here is another way to go about this that might be helpful to someone.

       In the Get Activity Details function I have my script check my spreadsheet to see what the most recent activity is and get that date:

       function getStravaActivityDetails() {

       // get the sheet
       var ss = SpreadsheetApp.getActiveSpreadsheet();
       var sheet = ss.getSheetByName(‘Sheet1’);

       // get the latest timestamp
       var lastdate = SpreadsheetApp.getActiveSheet().getRange(lastrow, 2).getValue();
       Logger.log(lastdate);
       var lastdateepoch = (((new Date(lastdate)).getTime()) /1000);
       //Logger.log(lastdateepoch);

       Then, when the API is called, I just insert lastdateepoch as into my params:

       if (service.hasAccess()) {
       Logger.log(‘Activity data app has access.’);

       var endpoint = ‘https://www.strava.com/api/v3/athlete/activities’;
       var params = ‘?after=’+lastdateepoch+’&per_page=99’;

       I’ve set this to trigger every couple of days so it just grabs all the latest activities automatically. If you were backfilling, you could just set it to trigger every hour (or whatever interval keeps you under the daily API call limit).

    3. Nice Mitchell, your method helped me a lot. I iterated upon it to produce a looped version. Probably not the cleanest method, but I was able to get it to work. Basically, used a while loop to continue fetching new pages while the size of the response>0. The code below starts a bit above my loop to show how I initialized the variables ahead of the loop. The daybacktime variable used in params is a variable used to store the epoch time for the date to which i’d like to go back, a selectable date in my spreadsheet. Still pretty green when it comes to app scripts, so I’m sure there are better ways to do just about everything I have on here.

       //initialize variables to be used in call loop
       var i = 1
       var responsesize = 55
       var responseCombined = []
       var headers = {
       Authorization: ‘Bearer ‘ + service.getAccessToken()
       };
       var options = {
       headers: headers,
       method : ‘GET’,
       muteHttpExceptions: true
       };
       var endpoint = ‘https://www.strava.com/api/v3/athlete/activities’;

       while (responsesize>0) {
       //update page number with each loop, starting with i=1
       var params = ‘?after=’ + daysbacktime + ‘&per_page=200&page=’ + i;

       //call strava API with loop-specific params
       var response = JSON.parse(UrlFetchApp.fetch(endpoint + params, options));

       //store number of keys in response size to allow for loop exit
       responsesize = +Object.keys(response).length;
       //ERROR CHECKING: console.log(‘responsesize is ‘ + responsesize);
       //ERROR CHECKING: console.log(‘number of rows in response is ‘ + Object.keys(response).length);

       //for loop to add look-specific response data to combined response
       for (var j=0; j<response.length;j++){
       responseCombined.push(response[j])
       }

       //iterate the counter to jump to next page
       i=i+1
       }

24. This post is awesome and really great! After some research here is what I discovered.

    If you are interested in gathering data for a Club, there is a plugin for Chrome that downloads the date and translates the time into minutes. While the solution provided here is good and works, there are additional steps required to format the data (time in seconds, distance in meters, no Activity Date).

    There is a forum where if you search for Club there are a lot of requests for information and some frustration that it’s close but not done. The API documentation doesn’t mention the missing Date.

    There are two solutions. The first is provided by the forum and is workable with effort. Get everyone (in the club) to grant the Strava account with Strava App (ID and Secret) access to their private accounts with Read rights. The second is to install the Chrome plugin and do a daily download.

    For our use case the plugin is the shortest distance between two points and requires less coordination with the Club members, when new members join. The downside, it is limited to the number of activities that can be downloaded so care will need to be taken in how often downloads are required based upon the number of Activities; larger clubs will likely require downloads more often.

    Hope this helps those who come across this to save time and effort.

    1. Thanks for sharing, Jim!

25. Can somebody tell me how to write this in script?

    To get more than 200, you need to add in the ‘page’ parameter and set it to 2,3,4 etc. to get the remaining activities, e.g.

    var params = ‘?after=1546300800&per_page=200&page=2’

    Eventually you’ll want to do that programmatically with a while loop (keep looping while the API returns data and stop when it comes back empty handed).

    1. Replied above in your reply to my post ^

    2. Unfortunately but Strava still shows same response as it’s a 1 page. Looks like it doesn’t work within after/before parameters.

       1. Just make sure you’re updating X to the page number within each variable in that code addition. That’s really all I can do to troubleshoot based on the info I have

26. Hi Ben, thanks for the great resource.

    I am wondering if the error I’m getting is something to do with the new OAuth changes?

    After trying to authorise the App by following the link in the execution log, I get a screen with the error:

    Error: Error retrieving token: Authorization Error (line 541, file “Service”)

    Any idea what might be the problem here? Thanks!

    1. Same issue here, any fixes already?

       Thanks!

27. This was a fantastic guide Ben. Really appreciate it.

28. Hi mods, can you please remove my comment reply I accidentally just posted with my full name and I’d prefer it not be posted here

29. Hello everyone!!
    I have error when Get Data

    I get the error TypeError: Cannot read property ‘length’ of undefined

    its for line : sheet.getRange(sheet.getLastRow() + 1, 1, stravaData.length, stravaData[0].length).setValues(stravaData);

    Help me to fix this,
    Thank,

    1. Samdy, I think I had a similar error to you a and I ended up having to create an empty array first then add my data to it. Something about how GS creates arrays was throwing the length off for me:

       // combine into array with second square brackets
       var stravaData1 = [];
       stravaData1.push(stravaData);

       // paste the values into the Sheet
       sheet.getRange(sheet.getLastRow() + 1, 1, stravaData1.length, stravaData1[0].length).setValues(stravaData1);

30. Hi,

    Thanks for this amazing resource! However I’d like to import detailed activities such as calories, which are not found in the basic activities list. It appears in order to get detailed activities you’d need to modify the API call as described here: https://developers.strava.com/docs/reference/#api-Activities-getActivityById

    Any idea how to go about doing this? It looks like it wants you to input the specific identifier for that activity which would involve looping over all the activities you just imported, and then grabbing the data from that.

    1. Walter, I’ve set up a script to pull my detailed activity data, including calculated “best efforts” over various distances.

       Too long to paste, but you can see my code here:

       https://github.com/kylewwilliams/StravaActivityDetail/blob/main/code.gs

31. I am trying to set this up for a group of people. I have it working great for myself, but then when I try to set up a new google sheet, new script with new ID and SECRET, it continues to populate data from my Strava Account instead of the others. Any ideas how to correct this?

    1. same here any where to learn ?

    2. Hello, same prbm.

       Did you find a solution ?

32. I run code error: TypeError: Cannot read property ‘forEach’ of undefined (line 24, file “code”)Dismiss. (code: data.forEach(function(activity) ….) plz help me how to fix it. TKS.

33. This post is awesome, thank you for writing this!

    In case that helps, I made some modifications to the original solution to make the application do a full refresh.
    To me it is important for a few reasons. First, I find it much easier to see my latest activities on top of the sheet. Second, I have enough activities that I need to have some kind of dynamic paging mechanism in place, so I added that in there too. Third: I often will make slight changes to my activities. Maybe I’ll correct a title, mark a bike ride as a commute, correct the bike used, etc. So I want these corrections updated in my sheet too.
    Drawback: I find it wasteful to just pull everything too often. So I actually adapted the code in the first solution to also go fetch the latest activities throughout the day, and I only do a full refresh once a day. For simplicity, I’ll just post the code for the full refresh as I think that’s what most folks will use.

    Code for full refresh:

    // Get athlete activity data
    function getStravaActivityData() {

    // get the sheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(‘Sheet1’);

    // call the Strava API to retrieve data
    var data = callStravaActivityAPI();

    // empty array to hold activity data
    var stravaData = [];

    // loop over activity data and add to stravaData array for Sheet
    Logger.log(‘Starting to write.’);
    data.forEach(function(activity) {
    var arr = [];
    arr.push(
    activity.name,
    activity.distance,
    activity.moving_time,
    activity.elapsed_time,
    activity.total_elevation_gain,
    activity.type,
    activity.workout_type,
    activity.id,
    activity.start_date_local,
    activity.timezone,
    activity.location_city,
    activity.location_state,
    activity.location_country,
    activity.achievement_count,
    activity.kudos_count,
    activity.comment_count,
    activity.athlete_count,
    activity.photo_count,
    activity.trainer,
    activity.commute,
    activity.manual,
    activity.private,
    activity.visibility,
    activity.gear_id,
    activity.from_accepted_tag,
    activity.average_speed,
    activity.max_speed,
    activity.average_cadence,
    activity.average_temp,
    activity.average_watts,
    activity.device_watts,
    activity.has_heartrate,
    activity.average_heartrate,
    activity.max_heartrate,
    activity.heartrate_opt_out,
    activity.display_hide_heartrate_option,
    activity.elev_high,
    activity.elev_low,
    activity.pr_count,
    activity.total_photo_count,
    activity.has_kudoed,
    activity.suffer_score,
    activity.max_watts,
    activity.weighted_average_watts,
    activity.start_latitude,
    activity.start_longitude,
    activity.location_city,
    activity.location_state,
    activity.location_country
    );
    stravaData.push(arr);
    });

    // paste the values into the Sheet
    // The below will do a full data refresh
    // Clear all data
    sheet.getRange(2,1,sheet.getLastRow(), stravaData[0].length).clearContent();
    Logger.log(‘Clear the sheet.’);
    // Populate new data
    sheet.getRange(2,1,stravaData.length, stravaData[0].length).setValues(stravaData);

    // Past the refresh time stamp into the sheet
    // variables = ss.getSheetByName(‘Variables’);
    // Get today’s date and time to display when the data was last refreshed
    //const refresh_date = Utilities.formatDate(new Date(), “GMT”, “MM/dd/yyyy HH:mm”);
    // variables.getRange(“B3”).setValue(refresh_date);
    //variables.getRange(“E5”).setValue(“Full”);
    }

    // call the Strava Activity API
    function callStravaActivityAPI() {

    // set up the service
    var service = getStravaService();

    if (service.hasAccess()) {
    Logger.log(‘App has access.’);

    var endpoint = ‘https://www.strava.com/api/v3/athlete/activities’;
    var params = ‘?per_page=200&page=’;
    var page = 1;

    var headers = {
    Authorization: ‘Bearer ‘ + service.getAccessToken()
    };

    var options = {
    headers: headers,
    method : ‘GET’,
    muteHttpExceptions: true
    };

    // Initiate the response array
    var response = [];

    // Get current response (first page of data)
    var current_response = JSON.parse(UrlFetchApp.fetch(endpoint + params + page, options));

    Logger.log(‘Starting the loop of responses.’);
    while (current_response.length != 0) {
    page ++;
    response = response.concat(current_response);
    current_response = JSON.parse(UrlFetchApp.fetch(endpoint + params + page, options));
    }
    Logger.log(‘Array built.’);

    return response;
    }
    else {
    Logger.log(“App has no access yet.”);

    // open this url to gain authorization from github
    var authorizationUrl = service.getAuthorizationUrl();

    Logger.log(“Open the following URL and re-run the script: %s”,
    authorizationUrl);
    }
    }

    1. Great stuff! Thanks for sharing this extension.

       Cheers,
       Ben

34. Hi Ben,

    Thank you for this amazing piece of code.

    I have adjusted it to show me my starred segments but I’m having trouble getting the Start and end Longitude to post?

    I’m very new to coding and the API documentation shows the start_latlng and end_latlng are “float” objects but I have no idea how to adjust the script to accommodate this.

    Any help would be greatly appreciated

35. Hi Ben!
    This is amazing!!

    Planning on connecting this to PowerBI via the Google Sheets Connector

    I have run into one problem however: I can’t seem to get the “description” to show. I have it alongside all the others as activity.description

    No errors are returned when running it, but every single cell it returns in this column is blank – any idea why this one in particular won’t show?

36. I’ve got the error below?
    Just copied the script from this website.

    TypeError: data.forEach is not a function

Comments are closed.