Category: Spreadsheets

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

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

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!

Behold the Google Sheets Formula Clock, a working analog clock built with a single Google Sheets formula:

It’s a working analog clock built with a single Google Sheets formula.

That’s right, just a single formula. No Apps Script code. No widgets. No hidden add-ons.

Just a plain ol’ formula in Google Sheets!

Google Sheets Formula Clock Template

Click here to open the Google Sheets Formula Clock Template

(Click to open the template. Feel free to create your own copy through the File menu: File > Make a copy...)

It might take a moment to update to the current time.

Part 1: Build your own Google Sheets Formula Clock

Step 1

Open a blank Google Sheet or create a new Google Sheet

(Pro-tip: type sheet.new into your browser address bar to do this instantly)

Step 2

Copy the Google Sheets Formula Clock formula below and paste it into the formula bar for cell A1 of your new Sheet:

Initially it will look like this:

Step 3

Make row 1 wider by hovering between rows 1 and 2 and using the grab hand to drag the row boundary down. Make the cell wide enough to create a circle:

Step 4

This is the step that makes the clock tick!

Under File > Spreadsheet settings set the spreadsheet calculation settings to be “On change and every minute”, like so:

This ensures that the NOW function is refreshed every minute, so our clock hands move around the circle. That’s it!

You should see the hands of your clock moving around the face.

Tick-tock! Tick-tock!

Part 2: How Does It Work?

So there are a few things going on here.

We need a way to get the current hour and minute values and have them update automatically.

Then somehow we need to draw a clock face with hands using…formulas?

Let’s run through the building blocks…

Create A Circle With The Sparkline Function

The SPARKLINE function is used to create miniature charts inside a single cell. That’s its modus operandi.

However, we can also supply it with a range of x- and y-coordinates to create 2-d shapes, like a circle for example.

Use the following five steps to create a circle with a sparkline:

1) Start with this function in cell A1:

The SEQUENCE function creates 37 rows in a single column, starting from 0 and increasing in increments of 10 each.

I.e. it outputs a column of numbers representing every 10 degrees of a circle, up to 360 degrees.

2) In column B, we add this Array Formula in cell B1:

3) And in column C, this one in cell C1:

Columns B and C now give you the coordinates of a circle.

4) Let’s plug them into the SPARKLINE function in cell D1 with this function:

5) Lastly, make row 1 wider to show the circle.

Boom!

The SPARKLINE function draws a circle for us:

Then, we need to create a time that automatically updates every minute. Thankfully that’s relatively easy to do with the NOW function:

NOW Function + Spreadsheet Settings

(Feel free to type these formulas in to the side of your sparkline workings in column B, C and D.)

The NOW Function in Google Sheets outputs a timestamp with a time to the nearest second. It’s a volatile function, which means it recalculates every time a change is made to the Sheet. In other words, it gives a new timestamp.

Per the Step 4 in Part 1 above, we can set the Sheet to update every minute, so the NOW function updates every minute.

Get The MINUTE And HOUR From NOW

It’s relatively easy to extract the minute and hour from the timestamp, with these two functions:

and

We need to convert these to degrees on a circle to show how far round the hands have gone.

The formulas become:

and

respectively.

Later we’ll need to convert these to RADIANS and then into coordinates for the sparkline function.

That’s the mechanics of the clock-tick-tock part, but we still need to add them to our sparkline clock.

Add The Clock Hands

The middle of our circle is represented by the coordinates (0,0).

Currently, our sparkline has positioned us at the 12 o’clock position, represented by (0,1).

To add the minute hand, we need to draw another arc around the circle to travel around the edge of the circle to the current minute value, e.g. if it’s half past the hour then we need to draw another half circle to position ourselves at the bottom of the circle.

Then we can simply draw a line back to the center of the circle, and that’s our minute hand!

So, add this function to cell B38:

And add this one to cell C38:

Essentially, what these two formulas are doing is working out how many degrees around the circle we need to go, and calculating the coordinates.

Finally, let’s return to the center of our circle, thereby drawing the minute hand.

In cell B398 put a 0.

In cell C398 put a 0.

They need to be on row 398 to give the array formula for the minutes enough space to expand (max 360 rows).

The “clock” now looks like this, and if you’ve set your spreadsheet to update every minute (see Step 4 in Part 1 above) then you’ll see this hand move around the clock.

To add the hour hand, it’s a case of drawing a line from the centre coordinate (0,0) — where we are now — back out to the edge, again, going as far around the circle as needed to represent the current hour.

Add this formula to cell B399:

And this formula to cell C399:

This adds the hour hand.

The 0.75 multiplier at the front of the formula shortens the hour hand a little to distinguish it from the second hand.

Boom!

Now you have a working clock:

Click here to view the template of this intermediary step.

Fix The Hour Issue

Unfortunately, in it’s current state, the formula breaks down at the top of the hour:

This is easily solved by wrapping the minute hand calculation with an IF statement to set it to zero at the top of the hour. This IF statement tests to see if the minute component of NOW is equal to zero and sets the value to 0 if it is, otherwise we just proceed with the full SEQUENCE function.

Change the formula in cell B38 to

and the formula in cell C38 to

It won’t look any different but you’ll avoid that error when the minute hand goes past the hour mark.

This formula is demonstrated in tab 2 of the intermediary template.

The clock will now look something like this:

So what’s left?

Improvements

You might consider the following improvements, but I’ll leave these as a challenge for you:

• Smoothing the hour hand, so it doesn’t jump in discrete steps from hour to hour but instead moves smoothly between the hours in proportion to the number of minutes passed. (See the GIF image at the start of this post.)
• Adding tick marks at each of the 12 hour marks around the clock face.
• Combining all the separate formulas into a single array formula. Hint: you need to make use of array literals with the constituent formulas.
• Add comments to explain the parts of the formula (see adding comments using the N function)

Implementing all of these is a little tricky, not the least because the formula gets rather long!

The best approach is to build in steps, employing the Onion Method technique to avoid frustrating errors.

Hickory, dickory, dock.
The mouse ran up the sparkline clock.
The sparkline clock struck one,
The mouse ran down,
Hickory, dickory, dock. 🐁 ⏱️

US Timezone Map

Combining many of these analog sparkline clocks onto a tile map, you can create this timezone map of the US:

Digital Clock?

See also this impressive digital clock built in Google Sheets by Robin Lord.

What Else Can You Draw With Sparklines?

Your imagination is the only limit with the sparkline function.

How about an Etch-A-Sketch clone built using a sparkline formula?

Etch A Sheet in Google Sheets

Or how about an outline of the Saturn V rocket?

Or a pie chart built with a single sparkline array formula?

This pie chart actually inspired the analog clock…you can probably see why!

This is a transcript of a conversation between two famous spreadsheet applications, Google Sheets and Microsoft Excel, who sat down together at a well-known beach bar, The Pivot & Chart Tavern, for a catch-up after a long WORKDAY.

For the DURATION of their meeting, SMALL and LARGE FISHERmen came and went, smelling of POISSON from the Sea.

It was a DAY to remember.

Google Sheets: “Excel! Dude! GAUSS who, yo? It’s been DAYS, MONTHs even, since we caught up. You made it. You crash so often I wasn’t sure you’d get here.”

XL: “Ah Google Sheets, you again. Rude, impetuous, cheeky. I see you’re still as mature as toddler in a COT. Still on a formula-only diet are you? Do you know FACT from fiction yet? Is this establishment to your satisfaction? I do hope it’s NOT out of your PRICE range.”

Sheets: “Woah, so aggressive. Nope, I’m all grown up now. IMREAL deal! I’m TRIM, check out my ABS. Still TRENDy and UNIQUE of course. I have so much going on right NOW, so many cool and COMPLEX features, and an ever growing, engaged, passionate community.

What about you, Excel, still hanging on? Haha.”

XL: “Hanging on? Totally FALSE! You should respect your elders.

NOW listen to me young man, I was doing advanced financial modeling whilst you were still popping zits on your funny little (inter)face. I may be over 30 YEARs old but I’m in the best health I’ve ever been. I continue to enjoy consistent product GROWTH.

Contrary to some of the marketing materials new-fangled upstarts put out, I’m very much alive and kicking, and still dominating the office data analytics scene, thank you very much. It seems you’re in rude health too Sheets, your voice is LOWER NOW, full of CONFIDENCE. Let me buy you a drink.”

Sheets: Sure, a beer please.

XL: So unsophisticated.

Turning to the barman…

XL: A beer, and I’ll have your finest aged red wine please. Put it all on the same TAB, thank you.

Barman: That’ll be 0.00091 Bitcoin please.

XL: Oh come on! Can you convert that TO_DOLLARS please?

Barman: As you CHOOSE, let me SWITCH the payment….that’ll be 10 Dollars EVEN at TODAY‘s price…

Excel hands over a 20 Dollar bill.

Barman: Is that DMIN bill you’ve got?

XL: Yes, I’m sorry for the trouble.

Barman: Ok, DMAX change I have is in 1 Dollar bills…

XL: That is no problem.

After a short SEARCH for an AREA to sit, and a brief interruption when they were INTERCEPTed by an errant ROMAN soldier, they took their seats at one of the PIVOT TABLES near the bar, to continue their rather KURT conversation…

Sheets: Do you think ISODD Excel? I MEAN, here we are in rude health, still the pre-eminent way the majority of knowledge workers manage and analyze their data.

XL: Yes, it’s TRUE! We have some sticking POWER that’s for sure. I take it as a good SIGN that our respective platforms continue to evolve and maintain their critical usefulness.

Sheets: Ok, let’s get down to business then. I want to share my theory of why we’re still the pre-eminent solution for many people…

XL: Ok, Sheets, the FLOOR is yours:

Sheets: First off, we’re ubiquitous. We’re everywhere. You’re in every office and I’m in every browser. So there’s that.

Second, we can solve most problems. Yes, there is ultra-specific software that will do certain tasks better, but nobody beats us for overall utility.

Third, we’re easy to use. Beginners can just dive right in, but we’re complex enough that even the most advanced users will never run out of things to discover.

XL: RIGHT, All TRUE, all good points. We’re definitely on the same FREQUENCY here.

Sheets: AND, we’re super flexible, so we can easily adapt to new tasks or new use cases.

XL: Yes, yes, indeed. Plus, almost all SaaS platforms have a button that exports data to Excel or Sheets. I suspect a lot of people use this, but of course that’s not a good metric for a SaaS company to divulge.

XL AND Sheets both have a little chuckle at this…

The conversation rambled on for several more HOURs. The EFFECT of the drinks made the conversation take an ODD turn:

XL:Have you ever BIN2OCT-oberfest, Sheets? You know the one I mean, the beer festival in Bavaria in the fall?

Sheets: Yeah, yeah I know the one, but no, I haven’t. Have you ever BIN2HEXham, XL?

XL: You mean the market town in the UK, right? Only once. And the airline lost my TRUNC on that trip! What a palava that was!

Sheets: TRUNC! Bwah! Now you’re showing your age. Haha. And definitely no chance of a TAN at that TIME of YEAR.

At a lull in the conversation, they both look down at their phones.

Sheets: Check this out, old man.

Sheets holds up his phone, with an app open called INDEX MATCH.

Sheets: It’s a dating service for spreadsheets. You right click on Sheets you like, left click on ones you don’t. It uses their IMPORTRANGE algorithm to MATCH you with other Sheets. Super cool.

XL: Bah, sounds like it’s just for HLOOKUPs to me. The more discerning spreadsheets look for love through a service called EDATE, all based around your star SIGN.

Sheets: Sounds like hokum to me…

You hungry Excel? Shall we get a PI?

Excel: You mean like a pizza PI? Could do, as long as we ADD spinach and ricotta, MINUS the mushrooms. Though I’d rather have CHAR-grilled steak.

Later, replete after dinner, it was time for the two friends to bid farewell…

XL: Right then Sheets, before you SLOPE off, let me tell you, it was good to catch up. TEXT me whenever you want to have a drink together again.

Sheets: ISEMAIL ok?

XL: As you wish. I’ll ask Numbers, LibreOffice, Airtable and maybe a few others to JOIN us next time, ok? They’re PROBably feeling LEFT out.

Sheets: Yep, I’ll be there. Catch up soon!

It certainly was a DAY to remember.

Resources

The full list of 400+ functions in Google Sheets

Your biggest competitor is a spreadsheet

My rather silly story was inspired by a similar, although much funnier, function-themed story from Mr Spreadsheet himself, John Walkenbach. Sadly I can’t find it online anymore, but if anyone can share the link, I’ll add it here.