Blog

Best wishes to all of you for 2020!

This is my 5th annual review post.

I’m proud and thankful that I get to write this post every year, because it means I’m still running my own show and my business is still going.

At this time of year I like to pause. Stop doing the busy work. Step off the treadmill and look back at 2019 to acknowledge what I achieved, what went well, and what can be improved. To look forward to 2020 and make plans for the year ahead.

2019 was my most successful year as an independent, small-business owner. It was great to hit some big milestones this year and see the previous years of hard work pay off.

I’m full of optimism for 2020 and think it’ll be another fantastic year, building on the success of 2019.

Did I Meet My 2019 Goals?

My goals for 2019 were:

• Create a follow-up Apps Script course — Yes, I achieved this!
• Create two other courses — 50% success. I launched one other new course
• Attend the Google Next 19 conference — Yes, it was one of the highlights of the year
• Continue to grow the community on this site and the online school — Yes (this was a rather meaningless goal without any metrics though!)
• Hold more webinars in 2019 — This I completely failed at, although I was a guest on a number of webinars and podcasts
• Deepen my digital analytics and marketing knowledge, and also continue experimenting with data science and Google Cloud topics — I did improve my knowledge of SEO this year, but didn’t really make any progress in other topics. However, I’ve come to realize that my expertise doesn’t lie in this area and my time is best spent on my core business. I do still want to dive deeper into Google Cloud this year though.

2019 Highlights

Online Courses

I launched two new courses:

• Automation With Apps Script
• Extreme Formulas (Edit 2023: no longer available)

The Collins School of Data continues to grow with over 20,000 students now! (Thank you all!)

Website

• I published 17 new pieces of content on benlcollins.com (down on previous years and something I want to increase again in 2020). However, almost all of them are long-form tutorials.
• The traffic to benlcollins.com continues to climb, reaching around 170k users/month for around 300k pageviews/month. Overall, the site has seen 3.4 million pageviews this year and 1.8 million users. Wow!
• The traffic grew in the first quarter of the year and then largely plateaued for the rest of the year, so I have work to do to grow again.

My favorite posts of the year were:

• Google Sheets Formula Clock (the craziest spreadsheet formula I’ve ever created. This was a really satisfying exploration of formulas in Google Sheets.)
• Goal Seek in Google Sheets
• How to connect the Strava API with Google Sheets and Data Studio (I was happy to finally figure this out, although it already needs an update!)
• In Pursuit Of A Dream (fun to write about our move to Harpers Ferry, WV)
• Google Next 19 Conference – Live Blog

Google Sheets Tips Newsletter

• I sent out a Google Sheets tip every Monday of this year except two (once I was sick and once for Christmas week). I was really happy to achieve this consistency and I’ve seen great growth and engagement around this list.
• Over 30k people are now signed up to my email list
• Popular emails were the formula challenges and this long form email on the mind-blowing facts behind card shuffling

Travel & Conferences

This year I traveled three times for work:

• In April, I attended the Google Next conference in San Francisco. It’s one of the highlights of the year for me because of the energy and people I spend time with. It’s such an inspiration for the rest of the year. Like 2018, I live blogged whilst I was there, covering anything related to Google Sheets. I’ll be there again in 2020. Holler if you’ll be there!
• In August Google invited me to New York to give a presentation to the Google Sheets team about my work. This was a real honor! I enjoyed meeting the team responsible for this product that I love.
• In December, I traveled to Copenhagen, Denmark to lead a 75-person workshop for the UN, teaching advanced Google Sheets techniques to help their team transition from Excel to Google Sheets.

Becoming a Google Developer Expert

2019 started in the best possible way!

I became a Google Developer Expert (GDE) in January. It’s been an honor to receive this award and be part of the program. The relationships I’ve developed with other GDEs and Googlers have been fantastic and inspirational. The G Suite GDE family are a great bunch!

I was sad to miss the 2019 GDE summit but hopefully I’ll be there in 2020!

Non-Work Highlights

• Moving to a mountain town. I LOVE, LOVE, LOVE having the woods and mountains easily accessible. It’s made a huge difference to our lifestyle. (I wrote more about our move from a work perspective and an outdoors perspective.)
• A huge benefit of living in the mountains are the weekly hikes (like this one up Loudon Heights on the A.T.)
• A wonderful trip home to the UK to visit family. My brother came over from Australia with his family and it was a joy to see all the cousins play together. My eldest son Dominic, upon meeting his 1 year-old cousin Henry for the first time, exclaimed, “He’s a little bit fat isn’t he?”. It made us all laugh.
• During this trip, my brother and I had a sublime day hiking in Snowdonia National Park.
• On the three work trips I did, I explored each city on foot (San Francisco, New York City and Copenhagen).
• Getting fit again with the weekly hiking, running and biking. It hasn’t all been plain sailing though with injuries and illnesses still impacting my year, but I’m in much better shape than I was in Florida last year.
• The weekly brainstorming hike with my wife. This has rapidly become one of the highlights of my week.
• Watching my boys grow. Teaching them about the world. Taking them on hikes. Laughing at their silly questions and funny actions. It’s both the hardest and most rewarding thing I’ve ever done.
• I read 24 books this year, beating last year’s tally (my long term goal is still 52 books, so some way to go!). My highlights this year were:
  • Company of One – Paul Jarvis
  • Atomic Habits – James Clear
  • The Paris Architect: A Novel – Charles Belfoure
  • Kochland – Christopher Leonard
  • Shadow Divers – Robert Kurson
  • Seveneves – Neal Stephenson
  • The Goldfinch – Donna Tart
• Other things I enjoyed in 2019:
  • Following the Bloodhound Land Speed Record team
  • Following Borge Ousland and Mike Horn cross the North Pole in winter (unfathomably hard)
  • Supporting Liverpool football club and watching them evolve into arguably the best team in the world through sheer drive and hard work

Challenges In 2019

In recent years I’ve experienced a number of health challenges, and this year was no exception. A nasty chest infection in March turned into pneumonia and landed me in hospital again. I recovered completely but work and life was impacted (another course launch postponed, sigh).

I was pretty healthy through the summer with lots of time outdoors. The trip to the UK was tiring and took some time to recover from. Right at the end of 2019, in the week before Christmas, another chest infection completely knocked me off my feet and necessitated a trip to the ER. A dose of meds straightened me out but it was a rough week.

After years of slow decline in my fitness levels, I reversed the trend this year and am now fitter than I was at the start of the year. I can run and hike much stronger than my Florida self could.

The transition from flat road-running in Florida to mountain running in West Virginia came at a price though. I suffered shin splints for the first time in my life over the Summer and am still managing my running now. I’m confident I’ll be back to normal on that front soon though.

By far the biggest challenge of my life continues to be the balance of being a good husband and father whilst running my own business and keeping fit. It’s hard to not feel like you’re failing on all these fronts, despite going full tilt all the time. All one can do is keep trying!

Looking Forwards To 2020

New Initiatives

• In January, I’ll be launching a new website, Excel to Sheets, to help organizations migrate from Microsoft Excel to Google Sheets. This is one of the big project areas I want to work on in 2020. Expect to see some blog posts tackling objections that Excel users have to Google Sheets.
• In March, I’ll be running an online event for all things Google Sheets. It’s going to be huge and very exciting! More details coming soon!

2020 Work Goals

• Publish more high-quality tutorials than in 2019 (target > 17)
• Hit 50k newsletter subscribers and send out a tip every Monday
• Update my existing Google Sheets courses
• Create one new Google Sheets course
• Run 10 in-person workshops
• Re-brand my digital assets
• Find a VA to help with the business
• Live-blog Google Next 2020 again
• Work through this book from last year that I haven’t started yet Data Science on the Google Cloud Platform

Other 2020 Goals

• My overall number 1 goal for 2020 is to be healthy
• Fitness goals: be active 5 times/week (a mix of spin classes, runs and at least 1 run/hike up the mountain)
• Keep up the weekly brainstorming hike with my wife
• Read 30 books

Thank You

Thank you for giving me this opportunity to share my experiences and knowledge, and carve out this teaching niche.

My mission remains to create a world-class resource for learning Google Sheets and data analysis. The work continues!

Finally, best wishes to all of you for 2020!

Cheers,
Ben

Previous years

• 2018 in review and a look forward to 2019
• 2017 in review and a look forward to 2018
• 2016 in review and a look forward to 2017
• 2015 in review and a look forward to 2016

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

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.

💡 Learn more
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
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!