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

Editor’s Note: Strava updated their OAuth workflow (see here), which may break the code shown below.

This post looks at how to connect the Strava API with Google Sheets and create a visualization in Google Data Studio.

Strava api with google sheets and data studio

Strava is an insanely good app for athletes to track their workouts. I use it to track my running, biking and hiking, and look at the data over time.

This whole project was born out of a frustration with the Strava app.

Whilst it’s great for collecting data, it has some limitations in how it shows that data back to me. The training log shows all of my runs and bike rides, but nothing else. However, I do a lot of hiking too (especially when I’m nursing a running injury) and to me, it’s all one and the same. I want to see it all my activities on the same training log.

So that’s why I built this activity dashboard in Google Data Studio. It shows all of my activities, regardless of type, in a single view.

Connecting the Strava API with Google Sheets

To connect to the Strava API with Google Sheets, follow these steps:

Setup your Google Sheet

  1. Open a new Google Sheet (pro-tip: type sheet.new into your browser window!)
  2. Type a header row in your Google Sheet: “ID”, “Name”, “Type”, “Distance (m)” into cells A1, B1, C1 and D1 respectively
  3. Open the Script Editor (Tools > Script editor)
  4. Give the script project a name e.g. Strava Sheets Integration
  5. Create a second script file (File > New > Script file) and call it oauth.gs
  6. Add the OAuth 2.0 Apps Script library to your project (Resources > Libraries...)
  7. Enter this ID code in the “Add a library” box: 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF
  8. Select the most recent version of the library from the drop-down (version 34 currently — September 2019) and hit “Save”

Add the code

If you’re new to API and Apps Script, check out my API Tutorial For Beginners With Google Sheets & Apps Script.

In your oauth.gs file, add this code:

var CLIENT_ID = '';
var CLIENT_SECRET = '';

// configure the service
function getStravaService() {
  return OAuth2.createService('Strava')
    .setAuthorizationBaseUrl('https://www.strava.com/oauth/authorize')
    .setTokenUrl('https://www.strava.com/oauth/token')
    .setClientId(CLIENT_ID)
    .setClientSecret(CLIENT_SECRET)
    .setCallbackFunction('authCallback')
    .setPropertyStore(PropertiesService.getUserProperties())
    .setScope('activity:read_all');
}

// handle the callback
function authCallback(request) {
  var stravaService = getStravaService();
  var isAuthorized = stravaService.handleCallback(request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Denied. You can close this tab');
  }
}

Also available in this GitHub oauth.js repo.

In your code.gs file, add this code:

// custom menu
function onOpen() {
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Strava App')
    .addItem('Get data', 'getStravaActivityData')
    .addToUi();
}

// 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 = callStravaAPI();
  
  // empty array to hold activity data
  var stravaData = [];
    
  // loop over activity data and add to stravaData array for Sheet
  data.forEach(function(activity) {
    var arr = [];
    arr.push(
      activity.id,
      activity.name,
      activity.type,
      activity.distance
    );
    stravaData.push(arr);
  });
  
  // paste the values into the Sheet
  sheet.getRange(sheet.getLastRow() + 1, 1, stravaData.length, stravaData[0].length).setValues(stravaData);
}

// call the Strava API
function callStravaAPI() {
  
  // 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 = '?after=1546300800&per_page=200';

    var headers = {
      Authorization: 'Bearer ' + service.getAccessToken()
    };
    
    var options = {
      headers: headers,
      method : 'GET',
      muteHttpExceptions: true
    };
    
    var response = JSON.parse(UrlFetchApp.fetch(endpoint + params, options));
    
    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);
  }
}

Also available in this GitHub code.js repo.

Note about the params variable

Have a look at the params variable:

var params = '?after=1546300800&per_page=200'

The ‘after’ parameter means my code will only return Strava activities after the date I give. The format of the date is epoch time and the date I’ve used here is 1/1/2019 i.e. I’m only returning activities from this year.

(Here’s a handy calculator to convert human dates to epoch timestamps.)

The other part of the params variable is the ‘per_page’ variable, which I’ve set to 200. This is the maximum number of records the API will return in one batch.

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).

Note about parsing the data

The script above parses the response from the API and adds 4 values to the array that goes into the Google Sheet, namely: ID, Name, Type, and Distance.

You can easily add more fields, however.

Look at the Strava documentation to see what fields are returned and select the ones you want. For example, you add total elevation gain like this:

activity.total_elevation_gain

If you add extra fields to the array, don’t forget to change the size of the range you’re pasting the data into in your Google Sheet.

The array and range dimensions must match.

Setup your Strava API application

You need to create your app on the Strava platform so that your Google Sheet can connect to it.

Login to Strava and go to Settings > My API Application or type in https://www.strava.com/settings/api

This will take you to the API application settings page.

Give your application a name, and enter a website and description. You can put anything you want here, as it’s just for display.

The key to unlocking the Strava API, which took me a lot of struggle to find, is to set the “Authorization Callback Domain” as

script.google.com

(Hat tip to this article from Elif T. KuÅŸ, which was the only place I found this.)

Strava API application setup

Next, grab your client ID and paste it into the CLIENT_ID variable on line 1 of your Apps Script code in the oauth.gs file.

Similarly, grab your client secret and paste it into the CLIENT_SECRET variable on line 2 of your Apps Script code in the oauth.gs file.

Copy these two values:

Strava API application

And paste them into your code here:

Oauth client id and secret in Apps Script

Authorize your app

Run the onOpen function from the script editor and authorize the scopes the app needs (external service and spreadsheet app):

Apps Script authorization

If your process doesn’t look like this, and you see a Warning sign, then don’t worry. Click the Advanced option and authorize there instead (see how in this Google Apps Script: A Beginner’s Guide).

Return to your Google Sheet and you’ll see a new custom menu option “Strava App”.

Click on it and select the “Get data” drop-down.

Nothing will happen in your Google Sheet the first time it runs.

Return to the script editor and open the logs (View > Logs). You’ll see the authorization URL you need to copy and paste into a new tab of your browser.

This prompts you to authorize the Strava app:

Strava API with Google Sheets authorization workflow

Boom! Now you’ve authenticated your application.

For another OAuth example, have a look at the GitHub to Apps Script integration which shows these steps for another application.

Retrieve your Strava data!

Now, the pièce de résistance!

Run the “Get data” function again and this time, something beautiful will happen.

Rows and rows of Strava data will appear in your Google Sheet!

Connect Strava To Google Sheets

The code connects to the athlete/activities endpoint to retrieve data about all your activities.

In its present setup, shown in the GIF above, the code parses the data returned by the API and pastes 4 values into your Google Sheet: ID, Name, Type, and Distance.

(The distance is measured in meters.)

Of course, you can extract any or all of the fields returned by the API.

In the data studio dashboard, I’ve used some of the time data to determine what day of the week and what week of the year the activity occurred. I also looked at fields measuring how long the activity took.

Setting a trigger to call the API automatically

Once you’ve established the basic connection above, you’ll probably want to set up a trigger to call the API once a day to get fresh data.

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.

Building a dashboard in Google Data Studio

Google Data Studio is an amazing tool for creating visually stunning dashboards.

I was motivated to build my own training log that had all of my activities showing, regardless of type.

First, I created some calculated fields in Apps Script to work out the day of the week and the week number. I added these four fields to my code.gs file:

(new Date(activity.start_date_local)).getDay(), // sunday - saturday: 0 - 6
parseInt(Utilities.formatDate(new Date(activity.start_date_local), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "w")), // week number
(new Date(activity.start_date_local)).getMonth() + 1, // add 1 to make months 1 - 12
(new Date(activity.start_date_local)).getYear() // get year

And I converted the distance in metres into a distance in miles with this modification in my Apps Script code.gs file:

(activity.distance * 0.000621371).toFixed(2), // distance in miles

From there, you simply create a new dashboard in Data Studio and connect it to your Google Sheet.

The activity log chart is a bubble chart with day of the week on the x-axis and week number as the y-axis, both set to average (so each appears separately). The bubble size is the Distance and the dimension is set to Name.

Bubble chart in Data Studio

Next, I added a Year filter so that I can view each year separately (I’ve got data going back to 2014 in the dataset).

To complete the dashboard, I added a Strava logo and an orange theme.

Strava api with google sheets and data studio

(Note: There’s also an open source Strava API connector for Data Studio, so you could use that to create Strava visualizations and not have to write the code yourself.)

Next steps for the Strava API with Google Sheets

This whole project was conceived as a way to explore the Strava API with Google Sheets. I’m happy to get it working and share it here.

However, I’ll be the first to admit that this project is still a little rough around the edges.

But I am excited to have my Strava data in a Google Sheet now. There are TONS of other interesting stories/trends that I want to explore when I have the time.

There is definitely room for improvement with the Apps Script code. In addition to those mentioned above, and with a little more time, I would bake the OAuth connection into the UI of the Google Sheet front end (using a sidebar), instead of needing to grab the URL from the Logs in your script editor.

And the Data Studio dashboard was rather hastily thrown together to solve the issue of not seeing all my activity data in one place. Again, there’s a lot more work you could do here to improve it.

Time for a run though!

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

    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”)

  3. 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

  4. 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.

  5. 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.

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

    1. 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.

  7. 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.

  8. 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?

  9. 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!

  10. 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?

  11. 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.

  12. 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?

  13. 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!

  14. 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]

  15. 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…

  16. 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?

  17. 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());

  18. 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 !

  19. 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.

  20. 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
    }

  21. 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
      }

  22. 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.

  23. 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. 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

  24. 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!

  25. 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

  26. 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);

  27. 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.

  28. 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?

  29. 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.

  30. 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);
    }
    }

  31. 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

  32. 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?

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

    TypeError: data.forEach is not a function

Leave a Reply

Your email address will not be published. Required fields are marked *