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 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
- Open a new Google Sheet (pro-tip: type sheet.new into your browser window!)
- Type a header row in your Google Sheet: “ID”, “Name”, “Type”, “Distance (m)” into cells A1, B1, C1 and D1 respectively
- Open the Script Editor (
Tools > Script editor)
- Give the script project a name e.g.
Strava Sheets Integration
- Create a second script file (
File > New > Script file) and call it oauth.gs
- Add the OAuth 2.0 Apps Script library to your project (
Resources > Libraries...)
- Enter this ID code in the “Add a library” box:
1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF
- 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.)
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:
And paste them into your code here:
Authorize your app
Run the onOpen function from the script editor and authorize the scopes the app needs (external service and spreadsheet app):
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:
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!
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.
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.
(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!
