Kition
sheetPlanners & Trackers

Class Timetable

A secondary-school week with double lessons as merged cells, merged break rows and subject colours from text rules.

Class Timetable — Sheet sample rendered by Kition

Double lessons are single cells merged down two periods, and the morning break and lunch are merged across the whole week. Subjects are coloured by "text contains" rules, one per subject, grouped by department, so retyping a lesson recolours it. The side table counts lessons per subject with COUNTIF, where a merged double counts as one lesson.

Steps

75 commands
showcase.yaml76 lines
steps:
  - { command: range.setValues, params: { range: A1, values: [["Class 9B Timetable"], ["Autumn term · room 214 unless noted · tutor Ms. Okoro"]] } }
  - { command: range.setValues, params: { range: A4, values: [["Period", "Time", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday"], ["1", "8:30 \u2013 9:20", "Mathematics", "English", "Biology", "Mathematics", "History"], ["2", "9:25 \u2013 10:15", null, null, null, "Geography", null], ["Break", "10:15 \u2013 10:35", "Morning break", null, null, null, null], ["3", "10:35 \u2013 11:25", "Physics", "Spanish", "Mathematics", "English", "Art"], ["4", "11:30 \u2013 12:20", "Spanish", "History", null, "Physics", null], ["Lunch", "12:20 \u2013 13:10", "Lunch", null, null, null, null], ["5", "13:10 \u2013 14:00", "Geography", "Physical education", "English", "Chemistry", "Music"], ["6", "14:05 \u2013 14:55", "Chemistry", null, "Spanish", null, "Mathematics"]] } }
  - { command: range.setValues, params: { range: I4, values: [["Department", "Lessons"]] } }
  - { command: range.setValues, params: { range: I5, values: [["Maths", "=COUNTIF($C$5:$G$12,\"Mathematics\")"], ["Sciences", "=SUM(COUNTIF($C$5:$G$12,{\"Physics\",\"Chemistry\",\"Biology\"}))"]] } }
  - { command: range.setValues, params: { range: I8, values: [["Languages", "=SUM(COUNTIF($C$5:$G$12,{\"English\",\"Spanish\"}))"], ["Humanities", "=SUM(COUNTIF($C$5:$G$12,{\"History\",\"Geography\"}))"]] } }
  - { command: range.setValues, params: { range: I11, values: [["Arts and PE", "=SUM(COUNTIF($C$5:$G$12,{\"Art\",\"Music\",\"Physical education\"}))"], ["All lessons", "=SUM(J5:J11)"]] } }
  - { command: home.mergeCells, params: { range: "C5:C6" } }
  - { command: home.mergeCells, params: { range: "D5:D6" } }
  - { command: home.mergeCells, params: { range: "E5:E6" } }
  - { command: home.mergeCells, params: { range: "G5:G6" } }
  - { command: home.mergeCells, params: { range: "E8:E9" } }
  - { command: home.mergeCells, params: { range: "G8:G9" } }
  - { command: home.mergeCells, params: { range: "D11:D12" } }
  - { command: home.mergeCells, params: { range: "F11:F12" } }
  - { command: home.mergeCenter, params: { range: "C7:G7" } }
  - { command: home.mergeCenter, params: { range: "C10:G10" } }
  - { command: home.mergeCells, params: { range: "A7:B7" } }
  - { command: home.mergeCells, params: { range: "A10:B10" } }
  - { command: home.conditionalFormat, params: { range: "C5:G12", rule: { type: containsText, text: "Mathematics", style: { fill: { fg: { Rgb: 14412542 }, pattern: Solid }, font: { color: { Rgb: 1982639 }, bold: true } } } } }
  - { command: home.conditionalFormat, params: { range: "C5:G12", rule: { type: containsText, text: "Physics", style: { fill: { fg: { Rgb: 14739455 }, pattern: Solid }, font: { color: { Rgb: 3616931 }, bold: true } } } } }
  - { command: home.conditionalFormat, params: { range: "C5:G12", rule: { type: containsText, text: "Chemistry", style: { fill: { fg: { Rgb: 14739455 }, pattern: Solid }, font: { color: { Rgb: 3616931 }, bold: true } } } } }
  - { command: home.conditionalFormat, params: { range: "C5:G12", rule: { type: containsText, text: "Biology", style: { fill: { fg: { Rgb: 14482663 }, pattern: Solid }, font: { color: { Rgb: 1467700 }, bold: true } } } } }
  - { command: home.conditionalFormat, params: { range: "C5:G12", rule: { type: containsText, text: "English", style: { fill: { fg: { Rgb: 16770278 }, pattern: Solid }, font: { color: { Rgb: 10424889 }, bold: true } } } } }
  - { command: home.conditionalFormat, params: { range: "C5:G12", rule: { type: containsText, text: "Spanish", style: { fill: { fg: { Rgb: 16770278 }, pattern: Solid }, font: { color: { Rgb: 10424889 }, bold: true } } } } }
  - { command: home.conditionalFormat, params: { range: "C5:G12", rule: { type: containsText, text: "History", style: { fill: { fg: { Rgb: 16708551 }, pattern: Solid }, font: { color: { Rgb: 9584654 }, bold: true } } } } }
  - { command: home.conditionalFormat, params: { range: "C5:G12", rule: { type: containsText, text: "Geography", style: { fill: { fg: { Rgb: 16708551 }, pattern: Solid }, font: { color: { Rgb: 9584654 }, bold: true } } } } }
  - { command: home.conditionalFormat, params: { range: "C5:G12", rule: { type: containsText, text: "Art", style: { fill: { fg: { Rgb: 15984895 }, pattern: Solid }, font: { color: { Rgb: 7020968 }, bold: true } } } } }
  - { command: home.conditionalFormat, params: { range: "C5:G12", rule: { type: containsText, text: "Music", style: { fill: { fg: { Rgb: 15984895 }, pattern: Solid }, font: { color: { Rgb: 7020968 }, bold: true } } } } }
  - { command: home.conditionalFormat, params: { range: "C5:G12", rule: { type: containsText, text: "Physical education", style: { fill: { fg: { Rgb: 13433841 }, pattern: Solid }, font: { color: { Rgb: 1138265 }, bold: true } } } } }
  - { command: home.fontSize, params: { range: A1, size: 20 } }
  - { command: home.bold, params: { range: A1, on: true } }
  - { command: home.fontColor, params: { range: A1, color: "#0f172a" } }
  - { command: home.italic, params: { range: A2, on: true } }
  - { command: home.fontColor, params: { range: A2, color: "#6b7280" } }
  - { command: home.fillColor, params: { range: "A4:G4", color: "#0f172a" } }
  - { command: home.fillColor, params: { range: "I4:J4", color: "#0f172a" } }
  - { command: home.fontColor, params: { range: "A4:J4", color: "#ffffff" } }
  - { command: home.bold, params: { range: "A4:J4", on: true } }
  - { command: home.alignCenter, params: { range: "A4:G12" } }
  - { command: home.alignMiddle, params: { range: "A4:J12" } }
  - { command: home.wrapText, params: { range: "C5:G12" } }
  - { command: home.bold, params: { range: "A5:A12", on: true } }
  - { command: home.fontColor, params: { range: "B5:B12", color: "#6b7280" } }
  - { command: home.fontSize, params: { range: "B5:B12", size: 9 } }
  - { command: home.fillColor, params: { range: "A7:G7", color: "#f1f5f9" } }
  - { command: home.fillColor, params: { range: "A10:G10", color: "#f1f5f9" } }
  - { command: home.italic, params: { range: "A7:G7", on: true } }
  - { command: home.italic, params: { range: "A10:G10", on: true } }
  - { command: home.fontColor, params: { range: "A7:G7", color: "#64748b" } }
  - { command: home.fontColor, params: { range: "A10:G10", color: "#64748b" } }
  - { command: home.borders, params: { range: "A4:G12", preset: all, color: "#ffffff", style: medium } }
  - { command: home.fillColor, params: { range: "I5:J5", color: "#dbeafe" } }
  - { command: home.fillColor, params: { range: "I6:J6", color: "#e0e7ff" } }
  - { command: home.fillColor, params: { range: "I8:J8", color: "#ffe4e6" } }
  - { command: home.fillColor, params: { range: "I9:J9", color: "#fef3c7" } }
  - { command: home.fillColor, params: { range: "I11:J11", color: "#f3e8ff" } }
  - { command: home.bold, params: { range: "I5:J12", on: true } }
  - { command: home.fontColor, params: { range: "I5:J12", color: "#334155" } }
  - { command: home.borders, params: { range: "I4:J12", preset: insideHorizontal, color: "#ffffff", style: medium } }
  - { command: home.borders, params: { range: "I12:J12", preset: topBottom, color: "#0f172a" } }
  - { command: home.alignCenter, params: { range: "J4:J12" } }
  - { command: home.columnWidth, params: { cols: "A:A", width: 46 } }
  - { command: home.columnWidth, params: { cols: "B:B", width: 74 } }
  - { command: home.columnWidth, params: { cols: "C:G", width: 96 } }
  - { command: home.columnWidth, params: { cols: "H:H", width: 20 } }
  - { command: home.columnWidth, params: { cols: "I:I", width: 118 } }
  - { command: home.columnWidth, params: { cols: "J:J", width: 54 } }
  - { command: home.rowHeight, params: { rows: "1:1", height: 28 } }
  - { command: home.rowHeight, params: { rows: "4:4", height: 24 } }
  - { command: home.rowHeight, params: { rows: "5:12", height: 34 } }
  - { command: home.rowHeight, params: { rows: "7:7", height: 20 } }
  - { command: home.rowHeight, params: { rows: "10:10", height: 20 } }
  - { command: sheet.read, params: { range: "J5:J12" }, as: lessons }
  - { command: cell.get, params: { cell: C5 }, as: double }
  - { command: document.inspect, params: {}, as: doc }

Checks

7 checks on every build
  • results.double.merge = "C5:C6"
  • results.double.input = "Mathematics"
  • results.lessons.values = [[4],[5],[null],[6],[4],[null],[3],[22]]
  • results.doc.sheets.0.merges = {"count":12,"contains":"C10:G10"}
  • results.doc.sheets.0.conditionalFormats = 11
  • Diagnostics: none, the kit's own check finds nothing wrong
  • The saved document opens again, and its thumbnail matches thumb.png within the render tolerance

Related

More Sheet samples
sheetPlanners & Trackers

Weekly Meal Planner

A seven-day meal grid with wrapped text, merged cells for batch-cooked meals and a costed shopping list.

1 sheet; used range A1:H18
sheetPlanners & Trackers

Monthly Habit Tracker

A November habit grid where a 1 becomes a dot through a number format, with completion rates, data bars and a daily heat row.

1 sheet; used range A1:AG13
sheetPlanners & Trackers

Swim Club Attendance

Twelve training sessions for 24 swimmers, with frozen names and dates, letter codes from a dropdown and colour-coded attendance rates.

1 sheet; used range A1:Q30
sheetPlanners & Trackers

Website Relaunch Gantt

A seven-week project plan whose Gantt bars, progress fill and status-date column are all drawn by conditional formatting.

1 sheet; used range A1:AJ18