Owlie - Buddy Matching System

Owlie is a Google Apps Script project designed for the volunteering organization ESN Ostravská to support Erasmus students in Ostrava. Its primary purpose is to semi-automatically match Erasmus students with local "Buddies" based on dynamic rules and grouping criteria.

This system operates directly within Google Spreadsheets, leveraging custom menus and SQL-like queries (via AlaSQL) to process student and buddy data.

Features

1. Erasmus: Guess Groups

Automatically assigns Erasmus students into groups based on shared characteristics (e.g., arrival date, faculty, country).

• How it works:
  • Sorts the student list based on configured criteria.
  • Iterates through the sorted list and assigns a unique group name (using space-themed names like Aurora, Nebula, etc.) to sequential blocks of students who share the same criteria.
• Configuration: controlled by the Erasmus: Guess groups sheet.

2. Erasmus: Match Buddies

The core feature of the system. It matches Erasmus students (or groups of students) with the most suitable local Buddy.

• How it works:
  • Iterates through unassigned Erasmus students.
  • For each student, it simulates a match with every available Buddy.
  • Calculates a "suitability score" for each pair based on dynamic rules defined in the spreadsheet (e.g., matching languages, hobbies, gender preference).
  • Assigns the Buddy with the highest score.
  • Group Matching: If the student is part of a group (assigned by "Guess Groups"), the selected Buddy is automatically assigned to the entire group.
• Configuration: Rules are defined in the Erasmus: Match buddies sheet using SQL fragments.

3. Tool: Send bulk e-mails

Generates a list of e-mails to be sent to Erasmus students and Buddies. The e-mails are saved as drafts in the user's Gmail account or sent directly (based on user's configuration).

Setup & Usage

Prerequisites

• Google Spreadsheet with the required sheets:
  • Erasmus: List of Erasmus students.
  • Buddy: List of local buddies.
  • Erasmus: Guess groups: Configuration for grouping logic.
  • Erasmus: Match buddies: Rules for buddy matching.
  • Tool: Send bulk e-mails: Configuration for sending e-mails.

Menu Access

Upon opening the spreadsheet, a custom menu Owlie appears in the toolbar:

• Erasmus: Guess groups - Runs the grouping algorithm.
• Erasmus: Match buddies - Runs the matching algorithm.
• Tool: Send bulk e-mails - Generates a list of e-mails to be sent to Erasmus students and Buddies. The e-mails are saved as drafts in the user's Gmail account or sent directly (based on user's configuration).
• Owlie: Hello / About - Info.

Technical Details

• Language: Google Apps Script (JavaScript).
• Data Querying: Uses AlaSQL library to perform SQL queries directly on the spreadsheet data models (model.js).
• Data Model: Custom Model object handles fetching data from sheets, transforming it into objects, and saving changes back efficiently (updates only changed cells).

License

MIT License.

Author: Michal Mikolas (nanuqcz@gmail.com) for ESN Ostravská (www.esn-ostravska.cz).