Skip to content

McRUN Master Attendance

About

The McRUN Master Attendance project manages and automates the process of tracking head run attendance for the McGill Students Running Club.

It leverages Google Apps Script with Google Sheets to provide a seamless, automated workflow for importing, formatting, and exporting attendance data between the master sheet and semester sheets.

Files

Purpose

  • Ensure all head run attendance is reliably recorded, formatted, and transferred.
  • Automate data cleaning and formatting for clarity and reporting.
  • Integrate with semester attendance sheets for historical and analytics purposes.

Key Features

  • Automated transfer of new submissions to semester sheets.
  • Data formatting for names, timestamps, and event details.
  • Consistent column formatting (e.g., font, size, alignment, checkboxes).
  • Trigger-based automation on sheet edits.
  • Error handling/logging for safe operation.
  • Timezone correctness for all date operations.

Tools Used

  • Google Apps Script (JavaScript)
  • Google Sheets
  • Google Sheets Triggers (onChange)
  • Custom formatting and transfer functions

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

# Data-Formatting.gs

## getLastSubmission()

Finds the row index of the last non-empty submission (by timestamp) in the master attendance sheet.

const idx = getLastSubmission();
Name Type Description
— — No parameters

Output: Number (1-based index of last non-empty row)

Pitfalls: If all rows are empty, may return 0 or error.

## formatNamesInRow(targetCols, startRow, numRow)

Formats headrunner or attendee names in the specified columns for a given row or range, normalizing apostrophes and splitting by commas/newline.

formatNamesInRow([2, 7], 7, 1);
Name Type Description
targetCols Array Columns to format
startRow Integer Row to start formatting (default: last row)
numRow Integer Number of rows to format (default: 1)

Output: None (in-place formatting in sheet)

Pitfalls: Out-of-range columns/rows may cause errors.


# Transfer.gs

## onChange(e)

Handles all sheet onChange events. Transfers new submissions to the semester sheet and triggers formatting.

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

Output: None (side effects: transfers data, runs formatting)

Pitfalls: Only processes EDIT events and correct sheet ID; errors logged.

## transferToSemesterSheet(row)

Transfers the latest submission (or specified row) to the semester attendance sheet, marking it as exported.

transferToSemesterSheet(5);
Name Type Description
row Integer Row to transfer (default: last submission)

Output: None

Pitfalls: Requires valid sheet IDs and permissions; falls back to direct access if library fails.


# Variables.gs

## getUserTimeZone()

Returns the timezone for the script as a geographical location string.

const tz = getUserTimeZone();
Name Type Description
— — No parameters

Output: String (timezone, e.g., 'America/Montreal')

Pitfalls: None


# View-Formatting.gs

## sortAttendanceForm()

Sorts all rows (except the header) by the Timestamp column in ascending order.

sortAttendanceForm();
Name Type Description
— — No parameters

Output: None (sorts in-place)

Pitfalls: Assumes Timestamp is in COLUMN_MAP.TIMESTAMP.

## prettifySheet()

Applies master formatting to the sheet for better readability.

prettifySheet();

Output: None

## formatSpecificColumns()

Applies font, size, bold, italics, number format, alignment, and checkboxes to specific columns.

formatSpecificColumns();

Output: None

Pitfalls: Hardcoded ranges; will fail if columns/names change.

Triggers

onChange Trigger

  • Type: onChange
  • Function: onChange(e)
  • Purpose: Runs automatically on any edit/change to the master attendance sheet.
    • Transfers new submissions to the semester sheet.
    • Runs formatting and maintenance functions.
    • Ensures all data is up-to-date and formatted after every change.

Manual/Custom Triggers

Troubleshooting & FAQ

Issue/Error Likely Cause Solution
"Early exit due to invalid e.changeType" Triggered on wrong event type Only EDIT events are processed
"thisSource is not defined" Event object is missing source Check Apps Script trigger setup
"Cannot get property 'getRange' of null" Wrong sheet ID or sheet deleted Check values in Variables.gs
"No rows transferred" No new submissions or all rows empty Verify data is submitted and not empty
Formatting is off Sheet structure has changed Update column indices and formatting ranges

See Also

Last updated: 2025-06-12