Swim Club Attendance
Twelve training sessions for 24 swimmers, with frozen names and dates, letter codes from a dropdown and colour-coded attendance rates.
Each session takes one letter from a dropdown: P present, L late, A absent, E excused. The names and the two header rows are frozen, so the list scrolls under them. Text rules colour the letters, COUNTIF tallies each swimmer and each session, and the rate leaves excused sessions out of the count. Rates under 75% turn red; the bottom row shows turnout per session under a colour scale.
Steps
43 commandssteps:
- { command: range.setValues, params: { range: A1, values: [["Swim Club Attendance"], ["Autumn block · Tuesday and Thursday evening training"], ["Key: P present · L late · A absent · E excused"]] } }
- { command: range.setValues, params: { range: A4, values: [["Member", "Group", 46301, 46303, 46308, 46310, 46315, 46317, 46322, 46324, 46329, 46331, 46336, 46338, "Attended", "Absent", "Rate"], [null, null, "=C4", "=D4", "=E4", "=F4", "=G4", "=H4", "=I4", "=J4", "=K4", "=L4", "=M4", "=N4", null, null, null], ["Abbott, Lily", "Juniors", "P", "A", "A", "E", "P", "A", "L", "P", "A", "L", "P", "P", "=COUNTIF(C6:N6,\"P\")+COUNTIF(C6:N6,\"L\")", "=COUNTIF(C6:N6,\"A\")", "=O6/(COUNTA(C6:N6)-COUNTIF(C6:N6,\"E\"))"], ["Baptiste, Noah", "Juniors", "P", "P", "L", "A", "P", "A", "A", "P", "P", "P", "P", "A", "=COUNTIF(C7:N7,\"P\")+COUNTIF(C7:N7,\"L\")", "=COUNTIF(C7:N7,\"A\")", "=O7/(COUNTA(C7:N7)-COUNTIF(C7:N7,\"E\"))"], ["Chen, Ava", "Juniors", "P", "A", "P", "P", "P", "P", "P", "P", "P", "A", "A", "P", "=COUNTIF(C8:N8,\"P\")+COUNTIF(C8:N8,\"L\")", "=COUNTIF(C8:N8,\"A\")", "=O8/(COUNTA(C8:N8)-COUNTIF(C8:N8,\"E\"))"], ["Dimitrov, Leo", "Juniors", "A", "P", "L", "P", "P", "A", "A", "P", "P", "P", "A", "A", "=COUNTIF(C9:N9,\"P\")+COUNTIF(C9:N9,\"L\")", "=COUNTIF(C9:N9,\"A\")", "=O9/(COUNTA(C9:N9)-COUNTIF(C9:N9,\"E\"))"], ["Eze, Grace", "Juniors", "P", "P", "P", "P", "P", "P", "P", "P", "L", "P", "P", "P", "=COUNTIF(C10:N10,\"P\")+COUNTIF(C10:N10,\"L\")", "=COUNTIF(C10:N10,\"A\")", "=O10/(COUNTA(C10:N10)-COUNTIF(C10:N10,\"E\"))"], ["Fontaine, Hugo", "Juniors", "L", "L", "P", "P", "P", "L", "P", "P", "P", "P", "P", "P", "=COUNTIF(C11:N11,\"P\")+COUNTIF(C11:N11,\"L\")", "=COUNTIF(C11:N11,\"A\")", "=O11/(COUNTA(C11:N11)-COUNTIF(C11:N11,\"E\"))"], ["Garcia, Mia", "Juniors", "P", "P", "P", "A", "E", "P", "P", "L", "P", "L", "P", "P", "=COUNTIF(C12:N12,\"P\")+COUNTIF(C12:N12,\"L\")", "=COUNTIF(C12:N12,\"A\")", "=O12/(COUNTA(C12:N12)-COUNTIF(C12:N12,\"E\"))"], ["Holm, Oscar", "Juniors", "P", "P", "P", "P", "P", "P", "P", "P", "P", "P", "E", "P", "=COUNTIF(C13:N13,\"P\")+COUNTIF(C13:N13,\"L\")", "=COUNTIF(C13:N13,\"A\")", "=O13/(COUNTA(C13:N13)-COUNTIF(C13:N13,\"E\"))"], ["Iqbal, Zara", "Intermediate", "P", "P", "A", "P", "P", "P", "P", "P", "P", "L", "P", "P", "=COUNTIF(C14:N14,\"P\")+COUNTIF(C14:N14,\"L\")", "=COUNTIF(C14:N14,\"A\")", "=O14/(COUNTA(C14:N14)-COUNTIF(C14:N14,\"E\"))"], ["Jensen, Theo", "Intermediate", "P", "E", "E", "P", "P", "P", "P", "L", "P", "P", "P", "P", "=COUNTIF(C15:N15,\"P\")+COUNTIF(C15:N15,\"L\")", "=COUNTIF(C15:N15,\"A\")", "=O15/(COUNTA(C15:N15)-COUNTIF(C15:N15,\"E\"))"], ["Kowalski, Ella", "Intermediate", "L", "L", "P", "P", "A", "P", "P", "P", "P", "A", "P", "A", "=COUNTIF(C16:N16,\"P\")+COUNTIF(C16:N16,\"L\")", "=COUNTIF(C16:N16,\"A\")", "=O16/(COUNTA(C16:N16)-COUNTIF(C16:N16,\"E\"))"], ["Lindqvist, Max", "Intermediate", "P", "P", "P", "P", "E", "P", "P", "P", "P", "P", "P", "P", "=COUNTIF(C17:N17,\"P\")+COUNTIF(C17:N17,\"L\")", "=COUNTIF(C17:N17,\"A\")", "=O17/(COUNTA(C17:N17)-COUNTIF(C17:N17,\"E\"))"], ["Mensah, Ruby", "Intermediate", "P", "P", "P", "L", "P", "E", "P", "P", "A", "P", "P", "P", "=COUNTIF(C18:N18,\"P\")+COUNTIF(C18:N18,\"L\")", "=COUNTIF(C18:N18,\"A\")", "=O18/(COUNTA(C18:N18)-COUNTIF(C18:N18,\"E\"))"], ["Novak, Finn", "Intermediate", "P", "P", "P", "P", "P", "L", "P", "P", "P", "P", "P", "P", "=COUNTIF(C19:N19,\"P\")+COUNTIF(C19:N19,\"L\")", "=COUNTIF(C19:N19,\"A\")", "=O19/(COUNTA(C19:N19)-COUNTIF(C19:N19,\"E\"))"], ["Ortega, Isla", "Intermediate", "P", "P", "P", "L", "E", "P", "P", "P", "A", "A", "P", "L", "=COUNTIF(C20:N20,\"P\")+COUNTIF(C20:N20,\"L\")", "=COUNTIF(C20:N20,\"A\")", "=O20/(COUNTA(C20:N20)-COUNTIF(C20:N20,\"E\"))"], ["Patel, Arjun", "Intermediate", "P", "P", "P", "E", "E", "L", "P", "P", "P", "P", "P", "P", "=COUNTIF(C21:N21,\"P\")+COUNTIF(C21:N21,\"L\")", "=COUNTIF(C21:N21,\"A\")", "=O21/(COUNTA(C21:N21)-COUNTIF(C21:N21,\"E\"))"], ["Quist, Freya", "Squad", "P", "E", "A", "P", "P", "L", "P", "P", "E", "A", "P", "P", "=COUNTIF(C22:N22,\"P\")+COUNTIF(C22:N22,\"L\")", "=COUNTIF(C22:N22,\"A\")", "=O22/(COUNTA(C22:N22)-COUNTIF(C22:N22,\"E\"))"], ["Rossi, Luca", "Squad", "A", "P", "P", "P", "P", "P", "P", "P", "P", "P", "P", "E", "=COUNTIF(C23:N23,\"P\")+COUNTIF(C23:N23,\"L\")", "=COUNTIF(C23:N23,\"A\")", "=O23/(COUNTA(C23:N23)-COUNTIF(C23:N23,\"E\"))"], ["Sato, Hana", "Squad", "A", "P", "P", "A", "A", "P", "L", "A", "P", "P", "A", "P", "=COUNTIF(C24:N24,\"P\")+COUNTIF(C24:N24,\"L\")", "=COUNTIF(C24:N24,\"A\")", "=O24/(COUNTA(C24:N24)-COUNTIF(C24:N24,\"E\"))"], ["Tembo, Kofi", "Squad", "P", "P", "P", "P", "P", "P", "L", "P", "P", "P", "P", "P", "=COUNTIF(C25:N25,\"P\")+COUNTIF(C25:N25,\"L\")", "=COUNTIF(C25:N25,\"A\")", "=O25/(COUNTA(C25:N25)-COUNTIF(C25:N25,\"E\"))"], ["Ueda, Rin", "Squad", "P", "P", "P", "A", "P", "P", "E", "L", "A", "P", "L", "P", "=COUNTIF(C26:N26,\"P\")+COUNTIF(C26:N26,\"L\")", "=COUNTIF(C26:N26,\"A\")", "=O26/(COUNTA(C26:N26)-COUNTIF(C26:N26,\"E\"))"], ["Vargas, Sofia", "Squad", "P", "P", "P", "L", "L", "P", "P", "P", "P", "P", "P", "P", "=COUNTIF(C27:N27,\"P\")+COUNTIF(C27:N27,\"L\")", "=COUNTIF(C27:N27,\"A\")", "=O27/(COUNTA(C27:N27)-COUNTIF(C27:N27,\"E\"))"], ["Walsh, Jack", "Squad", "L", "P", "P", "A", "P", "L", "A", "P", "P", "P", "P", "P", "=COUNTIF(C28:N28,\"P\")+COUNTIF(C28:N28,\"L\")", "=COUNTIF(C28:N28,\"A\")", "=O28/(COUNTA(C28:N28)-COUNTIF(C28:N28,\"E\"))"], ["Yilmaz, Elif", "Squad", "P", "P", "P", "P", "L", "E", "L", "P", "P", "P", "P", "P", "=COUNTIF(C29:N29,\"P\")+COUNTIF(C29:N29,\"L\")", "=COUNTIF(C29:N29,\"A\")", "=O29/(COUNTA(C29:N29)-COUNTIF(C29:N29,\"E\"))"], ["Swimmers present", null, "=COUNTIF(C6:C29,\"P\")+COUNTIF(C6:C29,\"L\")", "=COUNTIF(D6:D29,\"P\")+COUNTIF(D6:D29,\"L\")", "=COUNTIF(E6:E29,\"P\")+COUNTIF(E6:E29,\"L\")", "=COUNTIF(F6:F29,\"P\")+COUNTIF(F6:F29,\"L\")", "=COUNTIF(G6:G29,\"P\")+COUNTIF(G6:G29,\"L\")", "=COUNTIF(H6:H29,\"P\")+COUNTIF(H6:H29,\"L\")", "=COUNTIF(I6:I29,\"P\")+COUNTIF(I6:I29,\"L\")", "=COUNTIF(J6:J29,\"P\")+COUNTIF(J6:J29,\"L\")", "=COUNTIF(K6:K29,\"P\")+COUNTIF(K6:K29,\"L\")", "=COUNTIF(L6:L29,\"P\")+COUNTIF(L6:L29,\"L\")", "=COUNTIF(M6:M29,\"P\")+COUNTIF(M6:M29,\"L\")", "=COUNTIF(N6:N29,\"P\")+COUNTIF(N6:N29,\"L\")", "=SUM(O6:O29)", "=SUM(P6:P29)", "=AVERAGE(Q6:Q29)"]] } }
- { command: data.validation, params: { range: "C6:N29", type: list, formula1: "P,L,A,E", errorStyle: stop, errorMessage: "Use P, L, A or E." } }
- { command: home.conditionalFormat, params: { range: "C6:N29", rule: { type: expression, formula: "=C6=\"P\"", style: { fill: { fg: { Rgb: 14482663 }, pattern: Solid }, font: { color: { Rgb: 1467700 } } } } } }
- { command: home.conditionalFormat, params: { range: "C6:N29", rule: { type: expression, formula: "=C6=\"L\"", style: { fill: { fg: { Rgb: 16708551 }, pattern: Solid }, font: { color: { Rgb: 9584654 } } } } } }
- { command: home.conditionalFormat, params: { range: "C6:N29", rule: { type: expression, formula: "=C6=\"A\"", style: { fill: { fg: { Rgb: 16704226 }, pattern: Solid }, font: { color: { Rgb: 12131356 }, bold: true } } } } }
- { command: home.conditionalFormat, params: { range: "C6:N29", rule: { type: expression, formula: "=C6=\"E\"", style: { fill: { fg: { Rgb: 15857145 }, pattern: Solid }, font: { color: { Rgb: 6583435 } } } } } }
- { command: home.conditionalFormat, params: { range: "Q6:Q29", rule: { type: expression, formula: "=Q6<0.75", style: { fill: { fg: { Rgb: 12131356 }, pattern: Solid }, font: { color: { Rgb: 16777215 }, bold: true } } } } }
- { command: home.conditionalFormat, params: { range: "C30:N30", rule: { type: colorScale, colors: ["#fef9c3", "#7dd3fc"] } } }
- { command: home.fontSize, params: { range: A1, size: 20 } }
- { command: home.bold, params: { range: A1, on: true } }
- { command: home.fontColor, params: { range: A1, color: "#075985" } }
- { command: home.italic, params: { range: "A2:A3", on: true } }
- { command: home.fontColor, params: { range: "A2:A3", color: "#6b7280" } }
- { command: home.fillColor, params: { range: "A4:Q5", color: "#075985" } }
- { command: home.fontColor, params: { range: "A4:Q5", color: "#ffffff" } }
- { command: home.bold, params: { range: "A4:Q4", on: true } }
- { command: home.numberFormat, params: { range: "C4:N4", format: "ddd" } }
- { command: home.numberFormat, params: { range: "C5:N5", format: "d mmm" } }
- { command: home.fontSize, params: { range: "C5:N5", size: 9 } }
- { command: home.numberFormat, params: { range: "Q6:Q30", format: "0%" } }
- { command: home.alignCenter, params: { range: "C4:Q30" } }
- { command: home.fontColor, params: { range: "B6:B29", color: "#6b7280" } }
- { command: home.borders, params: { range: "C6:N29", preset: all, color: "#ffffff" } }
- { command: home.borders, params: { range: "A6:B29", preset: insideHorizontal, color: "#eef2f7" } }
- { command: home.borders, params: { range: "O6:Q29", preset: insideHorizontal, color: "#eef2f7" } }
- { command: home.borders, params: { range: "A13:Q13", preset: bottom, color: "#94a3b8" } }
- { command: home.borders, params: { range: "A21:Q21", preset: bottom, color: "#94a3b8" } }
- { command: home.bold, params: { range: "A30:Q30", on: true } }
- { command: home.bold, params: { range: "O6:O29", on: true } }
- { command: home.borders, params: { range: "A30:Q30", preset: top, color: "#075985", style: medium } }
- { command: home.columnWidth, params: { cols: "A:A", width: 108 } }
- { command: home.columnWidth, params: { cols: "B:B", width: 78 } }
- { command: home.columnWidth, params: { cols: "C:N", width: 38 } }
- { command: home.columnWidth, params: { cols: "O:P", width: 58 } }
- { command: home.columnWidth, params: { cols: "Q:Q", width: 46 } }
- { command: home.rowHeight, params: { rows: "1:1", height: 28 } }
- { command: view.freezePanes, params: { cell: C6 } }
- { command: data.validate, params: { cell: C6, input: X }, as: badCode }
- { command: sheet.read, params: { range: "O6:Q9" }, as: firstRows }
- { command: sheet.read, params: { range: "O30:Q30" }, as: totals }
- { command: formulas.evaluate, params: { formula: "=COUNTIF(Q6:Q29,\"<0.75\")" }, as: below }
- { command: document.inspect, params: {}, as: doc }Checks
9 checks on every buildresults.doc.sheets.0.freeze= [5,2]results.doc.sheets.0.validations= 1results.badCode.ok= falseresults.firstRows.values.0= [7,4,{"approx":0.636364,"tol":0.0001}]results.firstRows.values.3= [7,5,{"approx":0.583333,"tol":0.0001}]results.totals.values.0= [237,36,{"approx":0.870139,"tol":0.0001}]results.below= 4- Diagnostics: none, the kit's own check finds nothing wrong
- The saved document opens again, and its thumbnail matches
thumb.pngwithin the render tolerance
Related
More Sheet samplesMonthly 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.
Class Timetable
A secondary-school week with double lessons as merged cells, merged break rows and subject colours from text rules.
Website Relaunch Gantt
A seven-week project plan whose Gantt bars, progress fill and status-date column are all drawn by conditional formatting.
Weekly Meal Planner
A seven-day meal grid with wrapped text, merged cells for batch-cooked meals and a costed shopping list.