Skip to content

McRUN Race Code

About

McRUN Race Code is a Google Apps Script codebase that manages McGill Students Running Club (McRUN) race registrations, automates payment verification (online/Interac), and maintains robust data flows between Google Sheets and Gmail.
It streamlines the process of receiving, validating, and formatting race registrations, integrating with email and payment platforms for automation and oversight.

Purpose:

  • Automate import, formatting, and verification of race registrations.
  • Integrate payment confirmation (Zeffy, Stripe, Interac) with Gmail.
  • Enable custom menu actions, triggers, and streamlined workflows for admins.

Files

Key Features

  • Custom Google Sheets menu for admin workflows.
  • Import and process race registrations (manual or triggered).
  • Automated payment verification via Gmail (Zeffy, Stripe, Interac).
  • Time-based and event-based triggers for payment and registration processing.
  • Advanced formatting (checkboxes, banding, phone/date formats).
  • Robust logging and error handling for all critical operations.

Tools Used

  • Google Apps Script (JavaScript)
  • Google Sheets
  • Gmail API (via Apps Script)
  • Apps Script Triggers (time-based, onOpen, onChange)
  • Zeffy/Stripe/Interac payment integration via email search

Function Docs

This section is divided by project file (alphabetical order).
Each file lists its functions and provides a detailed reference for each.

Note: Only a selection of functions may be shown below due to search result limits.
See all code/functions in GitHub


# Formatting.gs

## removeDiacritics(str)

Removes diacritics (accents) from the given string.

const normalized = removeDiacritics("Élise");
Name Type Description
str String Input string to format

Output: String (normalized, accents removed)

## formatSpecificColumns()

Applies formatting to the registration sheet: freezes panes, wraps text, sets alignment, phone/date formats, checkboxes, and banding.

formatSpecificColumns();

Output: None (side effect: formats the sheet)

Pitfalls: Relies on sheet structure and column names.


# Import.gs

## appendToImport(reg)

Appends a registration object (stringified) to the import sheet.

const rowNum = appendToImport(JSON.stringify(regObj));
Name Type Description
reg String Registration data string

Output: Integer (row number where appended)

Pitfalls: Import sheet must be accessible and not locked.

## processLastImport()

Processes the last imported registration (parsing, registering, formatting, payment check).

processLastImport();

Output: None

Pitfalls: Throws if the target row is invalid or missing.

## onChange(e)

Triggered when a change occurs in the spreadsheet; processes new imports when rows are inserted.

function onChange(e) {
  // Triggered automatically
}
Name Type Description
e Object Sheets event object

Output: None

Pitfalls: Only processes 'INSERT_ROW' changes on the import sheet.


# Inbox.gs

## getGmailLabel(labelName)

Retrieves a Gmail label object by its name.

const label = getGmailLabel("Fee Payments/Online Emails");
Name Type Description
labelName String Name of the Gmail label

Output: GmailLabel

Pitfalls: Throws if label does not exist.

## getGmailSearchString(sender, offset)

Builds a Gmail search string for a sender and minimum date.

const search = getGmailSearchString("zeffy.com", 7 * 24 * 60 * 60 * 1000);
Name Type Description
sender String Sender email address
offset Integer Time offset in ms to calculate minimum search date

Output: String (Gmail search query)

## cleanUpMatchedThread(thread, label)

Marks a Gmail thread as read, archives it, and adds the provided label.

cleanUpMatchedThread(thread, label);
Name Type Description
thread GmailThread Gmail thread object
label GmailLabel Gmail label object

Output: None

Pitfalls: Throws if thread or label is invalid.


## onOpen()

Creates a custom menu in the sheet UI for McRace admin actions.

onOpen();

Output: None

## helpUI()

Displays a help dialog with info on menu actions and contact.

helpUI();

Output: None

## confirmAndRunUserChoice(functionName, additionalMsg, funcArg)

Displays a confirmation dialog and runs the selected function with an optional argument.

confirmAndRunUserChoice("formatSpecificColumns", "Format sheet?", "");
Name Type Description
functionName String Name of function to execute
additionalMsg String Custom message for confirmation (default: "")
funcArg String Optional argument to pass (default: "")

Output: Return value of the executed function


# Payment.gs

## checkPayment(member)

Checks payment status for a member depending on their method.

const paid = checkPayment(memberObj);
Name Type Description
member Object Member info (name, email, paymentMethod)

Output: Boolean (true if paid)

Pitfalls: Method must be known ("CC" or "Interac").

## checkOnlinePayment(member)

Checks for online payment (Zeffy, Stripe) via Gmail.

const hasPaid = checkOnlinePayment(memberObj);
Name Type Description
member Object Member info

Output: Boolean (true if payment found)

## checkInteracPayment(member)

Checks for Interac payment via Gmail.

const hasPaid = checkInteracPayment(memberObj);
Name Type Description
member Object Member info

Output: Boolean (true if payment found)


# Registration.gs

## getLastRowInReg()

Returns the last non-empty row in the registration sheet.

const lastRow = getLastRowInReg();

Output: Integer

## onNewRegistration({newRow, member})

Processes a new registration (extract payment, verify, format sheet).

onNewRegistration({ newRow: 25, member: memberArr });
Name Type Description
newRow Int Row where new data added
member Array Formatted member data

Output: None

## addNewRegistration(registrationObj)

Formats and adds a new registration to the sheet. Returns the new row and member data.

const { newRow, member } = addNewRegistration(regObj);
Name Type Description
registrationObj Object Registration data

Output: Object { newRow, member }


# Triggers.gs

## createNewFeeTrigger(row, feeDetails)

Creates a time-based trigger to check for a member’s fee payment.

createNewFeeTrigger(23, { fullName: "John Doe", email: "john@example.com", paymentMethod: "CC" });
Name Type Description
row Int Registration row
feeDetails Object Member's fee/payment info

Output: None

Pitfalls: Only works if Script Properties are writable.

## runFeeChecker()

Processes all fee check triggers, verifying payments and sending notifications as needed.

runFeeChecker();

Output: None


# Variables.gs

## getCurrentUserEmail()

Returns the email address of the current user executing the script.

const email = getCurrentUserEmail();

Output: String (email address)

Triggers

Types of Triggers

  • onOpen: Adds the custom admin menu for registrations and formatting.
  • onChange: Processes new imports when rows are inserted (Import.gs).
  • Time-based triggers:
  • For fee/payment checking (Triggers.gs), periodically checking if payment has been received for a registration.
  • Configured via createNewFeeTrigger(row, feeDetails).

Purpose:
- Automates admin workflows, payment verification, and keeps the registration sheet up-to-date with minimal manual intervention.

Troubleshooting & FAQ

Issue/Error Likely Cause Solution
"Failed to retrieve Gmail label." Label does not exist Create label in Gmail
"Failed to append registration to import sheet." Sheet locked, invalid, or missing Check permissions and sheet names
"Payment not found" Email not received or not matched Wait and retry; check sender and search terms
"Error cleaning up Gmail thread." Gmail API error Verify permissions, thread existence
Formatting is off Sheet structure changed Update column indices and formatting logic

See Also

Last updated: 2025-06-12