chapter-organizer/docs/instructions/google-sheets-schedule-import.md

created, description

created	description
2026-03-30	Extract a multi-day Google Sheets schedule grid into event-occurrence import text for the web app.

Import event schedule from Google Sheets

This repo includes a console tool that reads a public Google Sheet (with grid data and cell formatting), interprets each tab as one calendar day, and writes plain text in the same format as the legacy PDF-derived files consumed by Calendar → Import Event Occurrences (/calendar/event-occurrences/import).

Prerequisites

1. Google Cloud API key with the Google Sheets API enabled.
2. The spreadsheet must be readable with that key (typically File → Share → Anyone with the link Viewer, or share explicitly as needed for your key type).
3. A mapping JSON file that lists each tab title and the calendar month/day for that tab (see sample under docs/notes/).

Expected grid layout

• Row 1: Column B onward = location names (column A may be blank or a label).
• Column A (from row 2 down): Start time for each row (e.g. 9:00 AM, 9:00 a.m.). The tool infers slot length from consecutive times and uses the next row’s time as the end of a block when cells span multiple rows.
• Data cells: Event title (required for a block) and/or non-white background (treated as part of the same block as adjacent cells with the same text + color). Merged cells are expanded so the anchor value applies to the whole merge.

Default section header

Output uses General Schedule as the section header unless you override it in mapping. That matches grids where each cell is its own schedule item (conference-style). If every line should belong to a specific competition event, set sectionHeader for that tab to something like Biotechnology - MS (must fuzzy-match an event in your database when importing).

Under General Schedule, the parser assigns occurrences to the General Schedule event type in the app (see EventDefinitionResolver / EventDefinition.GeneralSchedule).

Grouping into competition events (PDF-style sections)

If you set eventDefinitionsCsv in the mapping JSON (or pass --events-csv), the tool loads event names from the CSV Event column and, when possible, rewrites output like the state schedule text:

• Section header: {Event Name} - MS or {Event Name} - HS
• Occurrence lines under that section use activity text only (the tool drops the leading MS/HS and the repeated event name so lines read like the PDF, e.g. On-Site Preliminary Exam … not MS Cybersecurity On-Site …).
• Only rows whose cell text has a leading MS , HS , MS/, or HS/ prefix (after normalization) are grouped; the remainder is fuzzy-matched to an event (see OccurrenceEventMatcher).
• MS/HS … combined rows → General Schedule (no single section).
• Unmatched lines (opening session, meet-the-candidates, help desk, etc.) stay under a final General Schedule block.

Use --no-group-by-event to force the old “one General Schedule per sheet” layout.

Site-wide rows (e.g. CURFEW)

If the same label appears across every location column for the same time (typical for CURFEW), the tool emits one line per date and time with no location. Built-in: CURFEW (case-insensitive). Optional mapping field siteWideEventNames adds more titles (e.g. ["Fire drill"]).

Run the tool

From the repository root:

$env:GOOGLE_SHEETS_API_KEY = "<your-api-key>"

dotnet run --project tools/GoogleSheetsScheduleImport/GoogleSheetsScheduleImport.csproj -- `
  --sheet-url "https://docs.google.com/spreadsheets/d/<spreadsheetId>/edit" `
  --mapping path/to/mapping.json `
  --output path/to/event-times.txt

Optional flags:

Flag	Purpose
--year 2026	Year for date validation (also in mapping file).
--tabs "Day 1,Day 2"	Only these tab titles (exact match).
--events-csv path\to\Event Definitions.csv	Load event names for stricter parser validation (Event column).
--strict	Exit code 1 if the built-in parser reports errors or parses zero occurrences.

The tool always runs a parser round-trip on the generated text and prints errors/issues to the console.

Import into the app

1. Open Import Event Occurrences in the web app.
2. Paste the contents of the generated .txt file.
3. Parse, review results, then Save to Database as usual.

Troubleshooting

• 403 / access denied: Confirm the sheet is visible to the API key and Sheets API is enabled.
• Wrong durations: Ensure time labels in column A are consistent; the tool infers slot length from the most common delta between consecutive rows.
• Parser issues on import: Use --events-csv pointing at your chapter’s event definitions export; fix sectionHeader in mapping if items should sit under a specific Event Name - MS/HS section.