Skip to content

McRUN Points System

About

The McRUN Points System is a Google Apps Script codebase designed to automate, calculate, and manage member points and event logs for the McGill Students Running Club. This system integrates with Google Sheets to record and tally participation, interfaces with Strava to extract running data, and automates communications and formatting via Gmail and Google Drive.

The project's purpose is to streamline the tracking of club events, automate member points calculation, and enhance the club's engagement and reporting using cloud tools and APIs.

Files

Key Features

  • Automated points calculation and event logging for club activities.
  • Strava API integration to auto-import running data.
  • Dynamic email generation for reporting and member notifications.
  • Custom formatting of event and points ledger sheets.
  • Time-based and web triggers for scheduled automation.
  • Script property storage for secure handling of credentials.
  • Tools for extracting and formatting HTML email templates.
  • Map image generation for event routes.
  • Robust logging and error handling.

Tools Used

  • Google Apps Script (Sheets, Drive, Gmail, Calendar)
  • Strava API (OAuth2)
  • Google Cloud APIs (Maps, Storage)
  • Make.com automations
  • HTML template processing for emails

Function Docs

This section is divided by project file (in alphabetical order). Each file includes a list of all its functions and a detailed entry for each.

Note: Functions named with a trailing underscore (_) are internal but documented here with the underscore removed.


# Formatting.gs

## sortTimestampByAscending()

Sorts the event log sheet by event timestamp (ascending, skipping headers).

sortTimestampByAscending();

Pitfalls: Assumes event timestamp is in column 3 and headers are at row 1.

## formatSpecificColumns()

Applies formatting (bold, wrap, checkbox, font size) to columns in the "Head Run Attendance" sheet.

formatSpecificColumns();

Pitfalls: Sheet/range must exist; formatting is hardcoded.

## toTitleCase(inputString)

Converts a string to title case.

const result = toTitleCase("hello world");
Name Type Description
inputString String String to format

Output: String (title-cased)


# HTML-Extraction.gs

## extractTagsFromProjectFile()

Extracts all placeholder tags (e.g., <?= TAG ?>) from a named HTML file in the project.

extractTagsFromProjectFile();

Output: In Logger.

## extractPlaceholders()

Extracts all {{placeholder}} tags from the email template text.

extractPlaceholders();

Output: Logs placeholder list

Pitfalls: Requires STATS_EMAIL_OBJ.text to be defined.

## createInlineImage(fileUrl, blobKey)

Creates a blob for an image file from its Google Drive URL and assigns a content ID.

const blob = createInlineImage('https://drive.google.com/file/d/FILE_ID/view', 'mapCid');
Name Type Description
fileUrl String Drive file URL
blobKey String Blob name/content ID

Output: Blob (image for inline email attachment)

Pitfalls: File must exist and be accessible.

## saveDraftAsHtml()

Saves the body of a Gmail draft (by subject line) as an HTML file in Drive.

saveDraftAsHtml();

Output: Creates file in Drive

Pitfalls: Draft with the specified subject must exist.


# Ledger-Code.gs

## newSubmission()

Formats the columns and sorts timestamps for a new event submission.

newSubmission();

Output: None

## getLatestSubmissionTimestamp()

Returns the latest event timestamp as a Date object.

const latest = getLatestSubmissionTimestamp();

Output: Date

## getSubmissionTimestamp(row)

Returns the timestamp of a submission for a given row.

const ts = getSubmissionTimestamp(7);
Name Type Description
row Integer Row in sheet

Output: Date

## getValidLastRow(sheet)

Finds the last non-empty row in a sheet.

const lastRow = getValidLastRow(LOG_SHEET);
Name Type Description
sheet Sheet Sheet to check

Output: Integer (row index)

## getLatestLog()

Returns the data (array) from the latest log row.

const log = getLatestLog();

## getLogInRow(row)

Returns log row data as array for a specific row.

const rowData = getLogInRow(10);
Name Type Description
row Integer Row in sheet

Output: Array

## getAttendeesInLog(row)

Gets the list of attendees for a given log row.

const attendees = getAttendeesInLog(5);
Name Type Description
row Integer Row in sheet


# Ledger-Variables.gs

## getLedger()

Returns or initializes the cached ledger data.

const data = getLedger();

Output: Array/object

## getLogSheet()

Returns the Event Log sheet object.

const sheet = getLogSheet();

Output: Sheet

## getLedgerSheet()

Returns the Member Points sheet object.

const sheet = getLedgerSheet();

Output: Sheet


# Map-Generation.gs

## createAndAppendMap(timestamp, activity)

Creates and stores a PNG map from Strava activity, appending the URL to the activity.

const updatedActivity = createAndAppendMap(new Date(), activityObj);
Name Type Description
timestamp Date Event timestamp
activity Object Strava activity data

Output: Object (activity with mapUrl property)

## createStravaMap(activity, name)

Creates a PNG map image from a Strava activity and returns the blob.

const blob = createStravaMap(activityObj, 'run-map.png');
Name Type Description
activity Object Strava activity
name String Map file name

Output: Blob

## saveMapForRun(polyline, name)

Saves a polyline as a map image using Google Maps API and automation.

const resp = saveMapForRun('encoded_polyline', 'run-map.png');
Name Type Description
polyline String Encoded polyline
name String Map file name

Output: API response


# Send-Email.gs

## logStatus(messageArr, logSheet, thisRow)

Logs the email sending/update status for a given row.

logStatus(['Sent', 'Success'], LOG_SHEET, 5);
Name Type Description
messageArr String[] Array of status messages
logSheet Sheet Log sheet object (default: LOG_SHEET)
thisRow Integer Row number (default: last row)

Output: None (status appended in sheet)


# Strava-Code.gs

## findAndStoreStravaActivity(row)

Finds Strava activity for a row (from log or API), stores it, and returns the activity.

const activity = findAndStoreStravaActivity(10);
Name Type Description
row Integer Row to process (default: last log)

Output: Object (Strava activity)

Pitfalls: User must be logged in as club; Strava API may rate limit.

## prettyLog(...msg)

Logs multiple lines for better readability.

prettyLog('line1', 'line2', 'line3');
Name Type Description
msg String[] Messages to log


# Strava-Service.gs

## reset()

Resets the Strava OAuth2 authorization state.

reset();

Output: None

## safeReset()

Resets Strava OAuth2 authorization if allowed in script properties.

safeReset();

Output: None

## getStravaActivity(fromTimestamp, toTimestamp)

Fetches Strava activities in the given time range.

const activities = getStravaActivity(unixStart, unixEnd);
Name Type Description
fromTimestamp Integer Start Unix time
toTimestamp Integer End Unix time

Output: Array of Strava activities

## getStravaService()

Configures and returns the OAuth2 service for Strava.

const service = getStravaService();

Output: OAuth2 service object


# Triggers.gs

## doGet(e)

Handles GET requests to set up Strava triggers via web endpoint.

doGet(e);
Name Type Description
e Object Event object from web request

Output: ContentService TextOutput

Pitfalls: Requires correct API key in e.parameter.key.

## createNewStravaTrigger(row)

Creates a new time-based trigger for checking Strava for a row.

createNewStravaTrigger(5);
Name Type Description
row Integer Row to associate with the trigger

Output: None (side effect: creates trigger and script property)

## runStravaChecker()

Checks all active Strava triggers, verifies activities, and cleans up when found.

runStravaChecker();

Output: None

Triggers

The project uses several types of triggers:

  • Time-based triggers: Scheduled (e.g., every 30 minutes) for checking Strava activities, sending emails, and formatting sheets.
  • Web app triggers: The doGet(e) function can be deployed as a web app endpoint for remote automation.
  • Manual triggers: Functions like newSubmission() or safeReset() may be called manually for setup or maintenance.

Purpose:
Triggers automate the periodic checking for new Strava runs, update points, send notifications, and keep the points ledger up to date without human intervention.

Troubleshooting & FAQ

Issue/Error Cause Solution
"Unauthorized! Please verify key." Wrong API key Set correct key in script properties and request
"No permission" Missing OAuth scopes Ensure all required Apps Script scopes are granted
"Cannot read property 'getRange' of null" Missing or renamed sheet/range Double-check all sheet names and constants
"Rate limit exceeded" Strava API throttling Wait and retry, ensure efficient API use
"OAuth error" Strava authorization failed Use reset() or safeReset() to reauthorize

FAQ

  • How do I update Strava credentials?
    Update CLIENT_ID and CLIENT_SECRET in Apps Script > Project Properties.

  • How do I add a new event type?
    Update constants in Ledger-Variables.gs and related logic in points calculation.

  • How do I test a function?
    Use the Apps Script IDE's “Run” feature; check the logs/output in Execution Log.

See Also

Last updated: 2025-06-12