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.
Learn more about Google Apps Script in this free, beginner Introduction To Apps Script course
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 itoauth.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.
Learn more about Google Apps Script in this free, beginner Introduction To Apps Script course
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.)
Learn more about Google Apps Script in this free, beginner Introduction To Apps Script course
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!
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.
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
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
Resolved, a problem within the name…thn the same
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”)
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…
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
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…
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!
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
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.
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.
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
Great work, Jorge and Ralph! Thanks for sharing your findings and code here.
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.
Excited to try this out. I got as far as authenticating, but came back to Google Sheets and no “Stava App” menu option.
Solved the Strava App button. Not sure what happened, but I ran it and now appeared.
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.
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.
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.
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?
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!
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?
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.
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..
Thank you! I ran in the same problem [TypeError: Cannot read property ‘forEach’ of undefined] but your workaround proved to work!
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!
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?
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.
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.
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?
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!
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 ?
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]
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…
Sheet1 – name sheet
Thank you!
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?
Thank You Dan, your comment help me alot
Still getting the same issue of type error like Matiiss. Any advice?
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?
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());
Hi John, I’ve tried your fix but still get the same error as Matiss!
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 !
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.
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
}
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;
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
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])
}
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).
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
}
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.
Thanks for sharing, Jim!
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).
Replied above in your reply to my post ^
Unfortunately but Strava still shows same response as it’s a 1 page. Looks like it doesn’t work within after/before parameters.
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
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!
Same issue here, any fixes already?
Thanks!
This was a fantastic guide Ben. Really appreciate it.
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
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,
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);
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.
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
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?
same here any where to learn ?
Hello, same prbm.
Did you find a solution ?
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.
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);
}
}
Great stuff! Thanks for sharing this extension.
Cheers,
Ben
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
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?
I’ve got the error below?
Just copied the script from this website.
TypeError: data.forEach is not a function