Build a habit tracker with streak counting

Northwind runs a few small team rituals — a morning standup, a weekly retro, a Friday demo — and the studio likes to know they actually happen. A simple grid in a Sheet does the logging: one row per habit, one column per day, a tick in the cell when the ritual happened. What the grid does not give you is the number people actually care about: the streak.

This script reads that grid, walks each habit’s tick marks from the most recent day backwards, and counts how many days in a row it has been kept. It writes the result into a streak column so the current run is visible at a glance — the small bit of friction that keeps a ritual alive.

What you’ll need

• A Google Sheet where the first sheet holds the habit grid: column A is the habit name, and one column per day with a real Date value in the header row (not text — the script relies on the headers being Date objects).
• A column with the header streak — anywhere in the sheet. The script writes the computed streak there.
• Ticked days marked with a ✓ character or a checkbox (TRUE). Anything else in a day cell counts as a miss.

The script

// The spreadsheet that holds the habit grid.
const HABITS_SHEET_ID = '1abcHabitsSheetId';

// What counts as "kept" for a given day.
const TICK = '✓';

/**
 * Reads the habit grid, counts each habit's current run of consecutive
 * kept days, and writes the result into the "streak" column.
 */
function computeStreaks() {
  const sheet = SpreadsheetApp.openById(HABITS_SHEET_ID).getSheets()[0];
  const [header, ...rows] = sheet.getDataRange().getValues();

  // Bail out early if there are no habits to score.
  if (!rows.length) {
    Logger.log('No habit rows found — nothing to do.');
    return;
  }

  // 1. Find every column whose header is a real Date — these are the days.
  const dateCols = header
    .map((h, i) => (h instanceof Date ? i : -1))
    .filter((i) => i >= 0);

  // 2. Sort day columns newest-first so we can count backwards from today.
  const sortedDateCols = dateCols.sort((a, b) => header[b] - header[a]);

  // 3. Locate the column the streak is written into.
  const streakCol = header.indexOf('streak');
  if (streakCol === -1) {
    Logger.log('No "streak" column found — add one and re-run.');
    return;
  }

  // 4. For each habit, walk days newest-first and stop at the first miss.
  const updates = rows.map((r) => {
    let streak = 0;
    for (const idx of sortedDateCols) {
      if (r[idx] === TICK || r[idx] === true) streak++;
      else break;
    }
    return [streak];
  });

  // 5. Write the streak counts back in one batched call.
  sheet.getRange(2, streakCol + 1, updates.length, 1).setValues(updates);
  Logger.log('Updated streaks for ' + updates.length + ' habits.');
}

How it works

1. computeStreaks opens the habits spreadsheet and splits the grid into a header row and the habit rows. If there are no habit rows it logs and stops.
2. It scans the header for cells that are real Date objects — those are the day columns. Text-formatted dates are ignored, which is the one gotcha to watch for.
3. It sorts the day columns newest-first by comparing the header dates, so the count always starts from the most recent day.
4. It finds the streak column by header name and stops if it is missing.
5. For each habit it walks the day columns from newest to oldest, adding one for every tick and breaking the loop the moment it hits a miss — that break is what makes it a current streak rather than a total.
6. It writes all the streak values back in a single setValues call, which is far faster than updating cells one at a time.

Example run

Say the grid looks like this, with today being 22 Nov:

Habit	20 Nov	21 Nov	22 Nov	streak
Standup	✓	✓	✓
Retro		✓	✓
Demo	✓		✓

After a run, the streak column is filled in:

Habit	20 Nov	21 Nov	22 Nov	streak
Standup	✓	✓	✓	3
Retro		✓	✓	2
Demo	✓		✓	1

Demo was kept today and yesterday-but-one, but the gap on 21 Nov resets the count — its current streak is 1, not 2.

Trigger it

Run this once a day, after the day’s cells have been filled in:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add Trigger → choose computeStreaks, event source Time-driven, a Day timer set to late evening.
3. Save and approve the authorisation prompt.

To update streaks the instant a tick is added, swap the time trigger for an onEdit installable trigger on the habits spreadsheet instead.

Watch out for

• The day headers must be real Date values. If a header reads as text the column is skipped and that day silently never counts. Format the header row as dates to be safe.
• A streak is broken by anything that is not a ✓ or a TRUE checkbox — an empty cell, an x, a typo. Decide on one tick convention and stick to it.
• Days in the future, if you pre-fill the grid, sort to the front and will be counted. Only tick cells once the day has actually happened.
• The streak value is a snapshot from the last run. It does not “decay” overnight on its own — the count is only correct as of the most recent run, so keep the daily trigger in place.
• Reordering or inserting day columns is fine; the script keys off header dates, not column positions. Renaming the streak column is not — update the constant to match.