/transpose-course Skill draft is ready for your review in the side panel — apply it and you can invoke it as `/transpose-course` next time. --- --- name: transpose-course description: Parse raw single-column course schedule data (web-scraped, pasted into a column) into a structured multi-column table with split Rubric/Title and Room/Day/Time fields. --- ## When to use User pastes raw course schedule data into a single column (typically column A or B) and asks to format/transpose it. Each course is a vertical block of labeled fields that needs to become one horizontal row. ## Output Columns A: Row#, B: Rubric, C: Title, D: Status, E: Campus, F: Room, G: Day, H: Time, I: Instructors, J: Instructional Format, K: Delivery Mode, L: Enrolled, M: Capacity ## Source format Each course is a block separated by blank lines. The block contains: 1. **Course header line** matching `/^[A-Z]{3,4}\s+\d{4}-[\w]+\s+-\s+/` (e.g., `ARTV 1303-20001 - Basic Animation`) 2. **Summary line**: `Title | Status` or `Title | Status | Instructor` 3. **"Section Details"** label, then a location/schedule line: `Campus Room | Day | Time` (e.g., `Frisco IT104 | Monday | 9:00 AM - 1:50 PM`). May appear twice (duplicate). Web courses have no location line. 4. Labeled fields (label on its own line, value 1–2 rows later, may have blank rows between): - `Campus` → e.g., `Frisco Campus` (or empty for web) - `Instructors` → e.g., `Russell Smith` - `Instructional Format` → usually `Combination` - `Delivery Mode` → `Hybrid`, `Face-to-Face`, `Web` - `Enrolled/Capacity` → either `"X/Y"` string OR a 5-digit Excel date serial (Excel auto-converted `8/18` to `46252`) ## Parsing approach Use `execute_office_js` to read the source column via `getUsedRange()`. Stash to `blobs.setJSON("raw", values)` if large. Then parse: 1. Find all course-header row indices via the regex above. 2. For each course block (from one header to the next), find labels by exact match (`Campus`, `Instructors`, `Instructional Format`, `Delivery Mode`, `Enrolled/Capacity`). Value = next non-empty cell after the label. 3. Location/schedule = first non-empty cell after the first `Section Details` label (skip duplicate `Section Details` lines). 4. Treat `(empty)` strings as blank. ## Field splits - **Course → Rubric + Title**: split on first ` - `. Left = Rubric (`ARTV 1303-20001`), right = Title (`Basic Animation`). - **Location → Campus prefix + Room + Day + Time**: split on `|` into 3 parts. Part 0 = `"Frisco IT104"` — Room is the last whitespace token (`IT104`). Part 1 = Day. Part 2 = Time. - **Summary line**: split on `|`, take part[1] as Status. ## Date serial fix for Enrolled/Capacity If the value is a number (Excel date serial), reverse-convert: ```js const d = new Date(Date.UTC(1899, 11, 30) + serial * 86400000); enrolled = d.getUTCMonth() + 1; capacity = d.getUTCDate(); ``` If it's a string like `"8/18"`, split on `/`. ## Steps 1. Read the source column with `getUsedRange()`. 2. Parse all course blocks into an array of records. 3. Spot-check: find any records with missing Campus/Location and confirm they are Web delivery (expected). Report any anomalies. 4. Clear the sheet (`getUsedRange().clear(Excel.ClearApplyTo.all)`). 5. Write headers + data rows to A1:M(n+1). 6. Bold A1:M1 headers. 7. Set numberFormat `"0"` on Enrolled and Capacity columns (L and M) — must be a 2D array matching the range dimensions. 8. `autofitColumns()` on the used range. Done when: row count matches the number of course-header lines found in the source, and Web courses have blank Room/Day/Time/Campus while in-person courses have all four populated. --- Please format this information about the Fall 2026 course schedule according to the headings in the data. Transpose the data from vertical to horizontal rows. Some courses are web courses and may not have campus, day, time, or room information. Break up the Enrolled/Capacity number into separate Enrolled and Capacity numbers. It has been misformatted as a date, where the months are the Enrolled number and the day is the Capacity. in the 12/7 format. ---