Skip to content

McRUN Membership List

About

McRUN Membership List is a Google Apps Script codebase for managing the membership roster of the McGill Students Running Club.

This project automates the collection, verification, and maintenance of member data, integrates with Google Sheets, Gmail, and automates registration, fee-tracking, and communication workflows. It is designed for efficient, accurate, and scalable club membership management.

Purpose

  • Centralize and automate member registration (including form and manual entry).
  • Verify membership fee payments via email (Zeffy, Stripe, Interac).
  • Synchronize semester and master sheets, ensuring consistent member history.
  • Automate communications and reporting for new members.

Files

Key Features

  • Automated import from form and manual member registration.
  • Fee verification and follow-up via Gmail/Inbox search.
  • Custom Google Sheets menu for common admin workflows.
  • Synchronization between semester and master membership lists.
  • Time-based and event-based triggers for processing new members and payments.
  • Communication automation for onboarding new members.
  • Robust error handling and logging.

Tools Used

  • Google Apps Script (JavaScript)
  • Google Sheets (multiple sheets: Main, Master, Import, Internal Fee Collection, etc.)
  • Gmail API via Apps Script
  • Apps Script Triggers (onChange, time-based, onOpen)
  • Google Drive (waivers, attachments)

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

## trimWhitespace(lastRow)

Trims whitespace from key columns in the last row of MAIN_SHEET.

trimWhitespace(23);
Name Type Description
lastRow Integer Row number to trim (default: last row)

Output: None (in-place formatting)

Pitfalls: Assumes columns 3-9 are name/referral fields in MAIN_SHEET.

## removeDiacritics(str)

Removes diacritics (accents) from a string, returning ASCII-only output.

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

Output: String (normalized, accents removed)

## sortMainByName()

Sorts the MAIN_SHEET by first name and then last name (columns 3, 4).

sortMainByName();

Output: None


# Member Fee.gs

## getPaymentItem(colIndex)

Retrieves the fee payment item from a specific cell in "Internal Fee Collection".

const item = getPaymentItem('A3');
Name Type Description
colIndex String Cell reference

Output: String (cell value)

## getGmailLabel(labelName)

Retrieves a Gmail label object by its name.

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

Output: GmailLabel

## checkAndSetPaymentRef(row)

Verifies whether a member's payment has been found in the inbox or waived; schedules a follow-up if not.

checkAndSetPaymentRef(22);
Name Type Description
row Integer Row in MAIN_SHEET (default: last submission)

Output: None

Pitfalls: Creates trigger for follow-up if payment not found.


# Membership Collected.gs

## onFormSubmit(newRow)

Processes a new member's registration: trims, formats, verifies payment, adds to master, and sends communications.

onFormSubmit(23);
Name Type Description
newRow Int Row number (default: last submission)

Output: None

## sendNewMemberCommunications(row)

Packages and transfers new member info to "NewMemberComms" sheet.

sendNewMemberCommunications(23);
Name Type Description
row Int Row in MAIN_SHEET

Output: None

## getLastSubmissionInMain()

Returns index (1-based) of the last filled row in MAIN_SHEET.

const idx = getLastSubmissionInMain();

Output: Int (row index)


# Triggers.gs

## createNewFeeTrigger(row, feeDetails)

Creates a scheduled time-based trigger to check a member's payment status.

createNewFeeTrigger(22, {memberName: "Elise Dubois", email: "elise@ex.com"});
Name Type Description
row Int Row number in sheet
feeDetails Object Details for fee checking

Output: None

## runFeeChecker()

Processes all fee check triggers: finds payments, updates sheet, or schedules further action.

runFeeChecker();

Output: None


# Transfer Scripts.gs

## onChange(e)

Event handler for spreadsheet changes: processes new Import entries and master updates.

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

Output: None

## transferLastImport()

Transfers the last Import sheet row to the main sheet and processes as new member.

transferLastImport();

Output: None

## transferThisRow(row)

Transfers a specific Import row to main sheet and processes as new member.

transferThisRow(14);
Name Type Description
row Int Row number in Import

Output: None


# User Menu.gs

## onOpen()

Adds the custom McRUN menu to the sheet UI.

onOpen();

Output: None

## logMenuAttempt(email)

Logs an attempt to use the menu by a user.

logMenuAttempt("admin@mcgill.ca");
Name Type Description
email String User email (default: current user)

Output: None

## changeSheetView(sheetName)

Activates the specified sheet in the current spreadsheet.

changeSheetView("Winter 2025");
Name Type Description
sheetName String Name of the sheet

Output: None

Triggers

Types of Triggers

  • onChange:
  • Handles new registration import, master updates, and triggers member processing.
  • Time-based triggers:
  • For periodic fee/payment checking; created as needed for follow-up.
  • onOpen:
  • Adds the custom admin menu for member management.

Purpose:
- Ensures all new members are processed, formatted, verified, and onboarded automatically. - Follows up on outstanding fee payments.

Troubleshooting & FAQ

Issue/Error Likely Cause Solution
"Missing required fields" Registration data not validated Ensure all required fields are present in import
"Unauthorized" Wrong or missing API key Ensure correct API key when using web endpoints
"Failed to find payment" Payment email not found Wait for payment notification or check search terms
"Label does not exist" Gmail label missing Create Gmail label manually
"Script error during onFormSubmit" Data/range not found or sheet structure changed Check sheet structure, update code if needed

See Also

Last updated: 2025-06-12