Strava Activities (Google Apps Script)

README.md

Strava Activities (Google Apps Script)

This gist contains the Google Apps Script I run to autopopulate many of the fields in my Google Sheet of the same name.

Setup

I currently have a the onChange function running on the "On change" event. This triggers the other routines when any data in the spreadsheet has changed, such as when IFTTT adds a row of new Strava data.

Code.gs

/**
 * Parses strings in CreatedAt column into MM/DD/YYYY format.
 *
 * @param {string} dtstr The date string in April 1, 2019 at 12:00PM format
 * @return {string} A date string in MM/DD/YYYY format.
 * @customfunction
 */
function PARSE_CREATEDAT(dtstr) {
  if (Array.isArray(dtstr)) {
    return dtstr.map(PARSE_CREATEDAT);
  } else if (dtstr.length === 0) {
    return '';
  } else {
    var rexp = /^(\w+)\s(\d{1,2}),\s(\d{4})/;
    var match = rexp.exec(dtstr);
    var months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'];
    var month = months.indexOf(match[1]) + 1;
    return month + '/' + match[2] + '/' + match[3];
  }
}

/**
 * Get the date of the start of the week.
 *
 * @param {string} dtstr A date string in the MM/DD/YYYY format
 * @return {string} A date string in MM/DD/YYYY format of the beginning of the week.
 * @customfunction
 */
function GET_WEEKSTART(dtstr) {
  if (Array.isArray(dtstr)) {
    return dtstr.map(GET_WEEKSTART);
  } else if (dtstr.length === 0) {
    return '';
  } else {
    dtstr = dtstr.split('/');
    var dt = new Date(dtstr[2], Number(dtstr[0]) - 1, dtstr[1]);
    while (dt.getDay() !== 1) {  // 1 means Monday
      dt = new Date(dt - 1000 * 60 * 60 * 24);
    }
    return (dt.getMonth() + 1) + '/' + dt.getDate() + '/' + dt.getFullYear();
  }
}

/**
 * Convert seconds into HH:MM:SS format
 *
 * @param {Number} an integer representing the number of seconds
 * @return {string} A HH:MM:SS formatted string
 * @customfunction
 */
function GET_DURATION(dur) {
  var hrs = Math.floor(dur / (60 * 60));  
  var mins = Math.floor(((dur / (60 * 60)) - hrs) * 60);
  var secs = Math.floor(dur - (hrs * 60 * 60) - (mins * 60));
  
  return Utilities.formatString("%d:%02d:%02d", hrs, mins, secs);
}

Routines.gs

/**
 * Routines.gs
 * -----------------------------------------------------------------------
 *
 * A script for function that don't return anything, they just perform
 * operations on the active spreadsheet.
 */

var SHEET_ID = '189X1mZ3IxzOMxr6Y7WMnyRk9JaPFA7KO8coeGV5joHc';
var TAB_NAME = 'Data'

/**
 * A convience routine for executing multiple operations that occur when a 
 * change happens to the spreadsheet, which is defined in Triggers.
 *
 * Return {null}
 */
function onChange(x) {
  Logger.log(x);
  var curr = SpreadsheetApp.openById(SHEET_ID).getSheetByName(TAB_NAME);
  var values = curr.getDataRange().getValues();
  
  populateWeekStart(curr, values);
  convertLinkToID(curr, values);
  convertTimeElapsedToDuration(curr, values);
  
  return null;
}

/**
 * Automatically populate the WeekStart column based on values 
 * in the CreatedAt column
 *
 * Params:
 *   - sheet {Spreadsheet} An instance of the Spreadsheet class
 *   - data {Array} A 2D array of values currently in the sheet.
 *
 * Return {null}
 *
 */
function populateWeekStart(sheet, data) {
  var idx = 6;  // zero-index of the WeekStart column
  var dtstr, weekstart;
  
  for (var row = 1; row < data.length; row++) {  // Start at 1 because 0 is spreadsheet header
    if (data[row][idx].length === 0) {
      dtstr = PARSE_CREATEDAT(data[row][0]);
      weekstart = GET_WEEKSTART(dtstr);
      sheet.getRange(row + 1, idx + 1).setValue(weekstart);  // getRange indices are one-indexed
    }
  }
  
  return null;
}

/**
 * Extract activity ID from the link to the activity
 *
 * Params:
 *   - sheet {Spreadsheet} An instance of the Spreadsheet class
 *   - data {Array} A 2D array of values currently in the sheet.
 *
 * Return {null}
 *   
 */
function convertLinkToID(sheet, data) {
  var idx = 5;  // zero-index of the ActivityID column
  var id;
  var regx = /activities\/(\d+)$/;
  
  for (var row = 1; row < data.length; row++) {
    var regxObj = regx.exec(data[row][idx]);
    if (regxObj !== null) {
      sheet.getRange(row + 1, idx + 1).setValue(regxObj[1]);   
    }
  }
  
  return null;
}

/**
 * Map the number of seconds in ElapsedTimeInSeconds column into
 * a new column reformatting the value in HH:MM:SS format
 *
 * Params:
 *   - sheet {Spreadsheet} An instance of the Spreadsheet class
 *   - data {Array} A 2D array of values currently in the sheet.
 *
 * Return {null}
 *
 */
function convertTimeElapsedToDuration(sheet, data) {
  var idx = 4;  // zero-index of the Duration column
  var duration, seconds;
  
  for (var row = 1; row < data.length; row++) {
    if (data[row][idx].length === 0) {
      seconds = data[row][3];
      duration = GET_DURATION(seconds);
      sheet.getRange(row + 1, idx + 1).setValue(duration);
    }
  }
  
  return null;
}

/**
 * Make a request to the Strava API's athlete activity list endpoint and append each
 * of those activities as rows in the spreadsheet. The following variables are meant
 * to be modified:
 * 
 * -resutlsPerPage {Number}: The number of resutls per page (keep this high to help
 *  with running up against the rate limit.
 *
 * -startDate {String}: A date string in MM/DD/YYYY format.
 *
 */
function populateSheetFromStrava() {
  var sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(TAB_NAME)
  var resultsPerPage = 100;
  var startDate = '12/1/2018';
  var i = 0;
  var code;
  do {
    i++;
    code = getAthleteActivities({
      after: dateToEpoch_(startDate),
      page: i,
      per_page: resultsPerPage,
    }, handleActivityList);
  } while (code === 200.0);
  
  return null;
  
  function handleActivityList(res) {
    var data = JSON.parse(res.getContentText())
      .map(function(activity) {
        var CreatedAt = Utilities.formatDate(new Date(activity.start_date_local), 'GMT', "MMMMM dd, yyyy 'at' hh:mma");
        var ActivityType = activity.type;
        var DistanceMeters = Number(activity.distance);
        var ElapsedTimeInSeconds = Number(activity.elapsed_time);
        var Duration = GET_DURATION(ElapsedTimeInSeconds);
        var ActivityID = activity.id;
        var WeekStart = GET_WEEKSTART(PARSE_CREATEDAT(CreatedAt));
    
        return [CreatedAt, ActivityType, DistanceMeters, ElapsedTimeInSeconds, Duration, ActivityID, WeekStart];  
    });

    // Add "new" data to the appropriate sheet
    data.forEach(function(row) {
      sheet.appendRow(row);
    });
    
  }  // handleActivityList
  
}

server.gs

/**
 * server.gs
 * --------------------------------------------------------------
 * A script full of function for interacting with the Strava API.
 * Sensitive variables such as CLIENT_SCECRET are not included in
 * files under source control
 *
 */

var BASE = 'https://www.strava.com/api/v3/';

/**
 * Authorizes and makes a request to the Strava API.
 */
function authenticate() {
  var service = getService_();
  if (service.hasAccess()) {
    var url = 'https://www.strava.com/api/v3/activities';
    var response = UrlFetchApp.fetch(url, {
      headers: {
        Authorization: 'Bearer ' + service.getAccessToken()
      }
    });
    var result = JSON.parse(response.getContentText());
    Logger.log(JSON.stringify(result, null, 2));
  } else {
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL and re-run the script: %s',
        authorizationUrl);
  }
}

/**
 * Reset the authorization state, so that it can be re-tested.
 */
function reset() {
  var service = getService_();
  service.reset();
}

/**
 * Configures the service.
 * Three required and optional parameters are not specified
 * because the library creates the authorization URL with them
 * automatically: `redirect_url`, `response_type`, and
 * `state`.
 */
function getService_() {
  return OAuth2.createService('Strava')
      // Set the endpoint URLs.
      .setAuthorizationBaseUrl('https://www.strava.com/oauth/authorize')
      .setTokenUrl('https://www.strava.com/oauth/token')

      // Set the client ID and secret.
      .setClientId(CLIENT_ID)
      .setClientSecret(CLIENT_SECRET)
      
//      .setScope('https://www.strava.com/oauth/authorize')
      .setParam('scope', 'activity:read_all')

      // Set the name of the callback function that should be invoked to
      // complete the OAuth flow.
      .setCallbackFunction('authCallback_')

      // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(PropertiesService.getUserProperties());
}

/**
 * Handles the OAuth callback.
 */
function authCallback_(request) {
  var service = getService_();
  var authorized = service.handleCallback(request);
  if (authorized) {
    return HtmlService.createHtmlOutput('Success!');
  } else {
    return HtmlService.createHtmlOutput('Denied.');
  }
}

/**
 * Logs the redict URI to register.
 */
function logRedirectUri() {
  Logger.log(OAuth2.getRedirectUri());
}

/**
 * Retrieve all Strava activities from an authenticated athlete
 * https://developers.strava.com/docs/reference/#api-Activities-getLoggedInAthleteActivities
 * 
 * Params:
 *   - opts {object literal}: A dictionary of all Strava endpoint parameters listed at URL
 *     listed above.
 *
 *   - callback {function}: A callback function that takes the HTTP response as an argument.
 *
 * Return: null
 *
 */
function getAthleteActivities(opts, callback) {
  var validParams = ['per_page', 'after', 'before', 'page'];
  var service = getService_();
  var code;
  
  var url = BASE + 'athlete/activities?';
  
  url += validParams.filter(function(p) {
    return opts.hasOwnProperty(p);
  }).map(function(p) {
    return p + '=' + opts[p];
  }).join('&');
  
  var res = UrlFetchApp.fetch(url, {
    headers: {
      Authorization: 'Bearer ' + service.getAccessToken()
    },
    muteHttpExceptions: false
  });
  
  code = res.getResponseCode();
  Logger.log(url + ' (' + code + ')');
  
  callback(res);
  
  return code;
    
}

/**
 * Convert a date string written in MM/DD/YYYY format to Unix 
 * Epoch time, the number of seconds since January 1, 1970. This
 * is a private, helper function within this script.
 *
 * Params
 *    - dtstr {String} a string in MM/DD/YYYY format, e.g. 5/19/2019
 *
 * Return {Number} Unix Epoch time stamp for that date.
 * 
 */
 function dateToEpoch_(dtstr) {
  var dtstr = dtstr.split('/').map(function(d) { return Number(d); });
  var d = new Date(dtstr[2], dtstr[0] - 1, dtstr[1]);
  return d.getTime() / 1000;
}