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.
Mark a day by typing 1: the format `[=1]"●";;` turns it into a dot and a formula rule fills the cell green, while days after the logged-through date are greyed out by a second rule. COUNT gives each habit's tally, the rate divides it by the days so far, and a data bar shows it. The bottom row counts habits done each day under a colour scale, and weekends get a darker header.
Steps
41 commandssteps:
- { command: range.setValues, params: { range: A1, values: [["November Habit Tracker"], ["Type 1 in a day when the habit is done"]] } }
- { command: range.setValues, params: { range: A3, values: [["Logged through", 20]] } }
- { command: home.mergeCells, params: { range: "B3:E3" } }
- { command: range.setValues, params: { range: A4, values: [["Habit", 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, "Done", "Rate"], [null, "S", "M", "T", "W", "T", "F", "S", "S", "M", "T", "W", "T", "F", "S", "S", "M", "T", "W", "T", "F", "S", "S", "M", "T", "W", "T", "F", "S", "S", "M", null, null], ["Walk 8,000 steps", 1, 1, null, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, null, 1, 1, null, null, 1, 1, null, null, null, null, null, null, null, null, null, null, "=COUNT(B6:AE6)", "=AF6/$B$3"], ["Read 20 pages", 1, 1, 1, 1, 1, 1, 1, 1, 1, null, 1, 1, 1, null, 1, 1, null, null, null, null, null, null, null, null, null, null, null, null, null, null, "=COUNT(B7:AE7)", "=AF7/$B$3"], ["No phone after 10 pm", 1, 1, 1, 1, null, 1, null, 1, null, null, 1, 1, null, 1, null, 1, 1, null, null, 1, null, null, null, null, null, null, null, null, null, null, "=COUNT(B8:AE8)", "=AF8/$B$3"], ["Drink 2 L of water", 1, 1, null, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, null, null, null, null, null, null, null, null, null, null, "=COUNT(B9:AE9)", "=AF9/$B$3"], ["Practice Spanish", null, null, 1, null, 1, 1, null, null, 1, null, 1, 1, null, 1, 1, 1, 1, 1, 1, null, null, null, null, null, null, null, null, null, null, null, "=COUNT(B10:AE10)", "=AF10/$B$3"], ["Stretch for 10 min", 1, 1, null, 1, 1, null, 1, 1, null, null, 1, 1, null, null, 1, null, null, 1, 1, 1, null, null, null, null, null, null, null, null, null, null, "=COUNT(B11:AE11)", "=AF11/$B$3"], ["Journal", 1, null, 1, null, 1, null, null, 1, 1, null, 1, 1, null, null, null, 1, null, 1, 1, 1, null, null, null, null, null, null, null, null, null, null, "=COUNT(B12:AE12)", "=AF12/$B$3"], ["Done that day", "=IF(B$4<=$B$3,SUM(B6:B12),\"\")", "=IF(C$4<=$B$3,SUM(C6:C12),\"\")", "=IF(D$4<=$B$3,SUM(D6:D12),\"\")", "=IF(E$4<=$B$3,SUM(E6:E12),\"\")", "=IF(F$4<=$B$3,SUM(F6:F12),\"\")", "=IF(G$4<=$B$3,SUM(G6:G12),\"\")", "=IF(H$4<=$B$3,SUM(H6:H12),\"\")", "=IF(I$4<=$B$3,SUM(I6:I12),\"\")", "=IF(J$4<=$B$3,SUM(J6:J12),\"\")", "=IF(K$4<=$B$3,SUM(K6:K12),\"\")", "=IF(L$4<=$B$3,SUM(L6:L12),\"\")", "=IF(M$4<=$B$3,SUM(M6:M12),\"\")", "=IF(N$4<=$B$3,SUM(N6:N12),\"\")", "=IF(O$4<=$B$3,SUM(O6:O12),\"\")", "=IF(P$4<=$B$3,SUM(P6:P12),\"\")", "=IF(Q$4<=$B$3,SUM(Q6:Q12),\"\")", "=IF(R$4<=$B$3,SUM(R6:R12),\"\")", "=IF(S$4<=$B$3,SUM(S6:S12),\"\")", "=IF(T$4<=$B$3,SUM(T6:T12),\"\")", "=IF(U$4<=$B$3,SUM(U6:U12),\"\")", "=IF(V$4<=$B$3,SUM(V6:V12),\"\")", "=IF(W$4<=$B$3,SUM(W6:W12),\"\")", "=IF(X$4<=$B$3,SUM(X6:X12),\"\")", "=IF(Y$4<=$B$3,SUM(Y6:Y12),\"\")", "=IF(Z$4<=$B$3,SUM(Z6:Z12),\"\")", "=IF(AA$4<=$B$3,SUM(AA6:AA12),\"\")", "=IF(AB$4<=$B$3,SUM(AB6:AB12),\"\")", "=IF(AC$4<=$B$3,SUM(AC6:AC12),\"\")", "=IF(AD$4<=$B$3,SUM(AD6:AD12),\"\")", "=IF(AE$4<=$B$3,SUM(AE6:AE12),\"\")", "=SUM(AF6:AF12)", "=AF13/($B$3*7)"]] } }
- { command: home.numberFormat, params: { range: "B3", format: "\"day \"0\" of 30\"" } }
- { command: home.numberFormat, params: { range: "B6:AE12", format: "[=1]\"●\";;" } }
- { command: home.numberFormat, params: { range: "AG6:AG13", format: "0%" } }
- { command: home.conditionalFormat, params: { range: "B6:AE12", rule: { type: expression, formula: "=B$4>$B$3", style: { fill: { fg: { Rgb: 16250871 }, pattern: Solid } } } } }
- { command: home.conditionalFormat, params: { range: "B6:AE12", rule: { type: expression, formula: "=B6=1", style: { fill: { fg: { Rgb: 12968900 }, pattern: Solid }, font: { color: { Rgb: 1475653 } } } } } }
- { command: home.conditionalFormat, params: { range: "B13:AE13", rule: { type: colorScale, colors: ["#fef3c7", "#86efac", "#15803d"] } } }
- { command: home.conditionalFormat, params: { range: "AG6:AG12", rule: { type: dataBar, color: "#4ade80", min: { type: num, value: 0 }, max: { type: num, value: 1 } } } }
- { command: home.conditionalFormat, params: { range: "B4:AE5", rule: { type: expression, formula: "=OR(B$5=\"S\")", style: { fill: { fg: { Rgb: 1324600 }, pattern: Solid } } } } }
- { command: home.fontSize, params: { range: A1, size: 20 } }
- { command: home.bold, params: { range: A1, on: true } }
- { command: home.fontColor, params: { range: A1, color: "#14532d" } }
- { command: home.italic, params: { range: A2, on: true } }
- { command: home.fontColor, params: { range: A2, color: "#6b7280" } }
- { command: home.fontColor, params: { range: A3, color: "#6b7280" } }
- { command: home.bold, params: { range: B3, on: true } }
- { command: home.fillColor, params: { range: "A4:AG5", color: "#166534" } }
- { command: home.fontColor, params: { range: "A4:AG5", color: "#ffffff" } }
- { command: home.bold, params: { range: "A4:AG4", on: true } }
- { command: home.fontSize, params: { range: "B4:AE5", size: 9 } }
- { command: home.fontSize, params: { range: "B13:AE13", size: 9 } }
- { command: home.alignCenter, params: { range: "B4:AG13" } }
- { command: home.fontName, params: { range: "B6:AE12", name: JetBrains Mono } }
- { command: home.borders, params: { range: "B6:AE12", preset: all, color: "#ffffff" } }
- { command: home.borders, params: { range: "A6:A12", preset: insideHorizontal, color: "#e5e7eb" } }
- { command: home.bold, params: { range: "A13:AG13", on: true } }
- { command: home.borders, params: { range: "A13:AG13", preset: top, color: "#166534", style: medium } }
- { command: home.bold, params: { range: "AF6:AF12", on: true } }
- { command: home.columnWidth, params: { cols: "A:A", width: 132 } }
- { command: home.columnWidth, params: { cols: "B:AE", width: 19 } }
- { command: home.columnWidth, params: { cols: "AF:AF", width: 40 } }
- { command: home.columnWidth, params: { cols: "AG:AG", width: 64 } }
- { command: home.rowHeight, params: { rows: "1:1", height: 28 } }
- { command: home.rowHeight, params: { rows: "6:13", height: 21 } }
- { command: home.alignMiddle, params: { range: "A4:AG13" } }
- { command: view.freezePanes, params: { cell: B6 } }
- { command: sheet.read, params: { range: "AF6:AG13" }, as: tally }
- { command: sheet.read, params: { range: "B6:E6", formatted: true }, as: dots }Checks
7 checks on every buildresults.tally.values= {"count":8}results.dots.values.0= ["●","●","","●"]results.tally.values.0= [16,{"approx":0.8,"tol":0.0001}]results.tally.values.3= [19,{"approx":0.95,"tol":0.0001}]results.tally.values.7= [96,{"approx":0.685714,"tol":0.0001}]- 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 samplesSwim Club Attendance
Twelve training sessions for 24 swimmers, with frozen names and dates, letter codes from a dropdown and colour-coded attendance rates.
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.