Render a heatmap calendar from daily values

Northwind logs every client touch-point — a call, a meeting, a delivered piece of work — against the day it happened. Over a year that is a long, flat column of dates and counts that tells you almost nothing at a glance. You cannot see the busy stretches, the quiet weeks, or whether things are trending up.

This script turns that column into a GitHub-style heatmap: a 7-row by 53-column grid where each cell is one day, coloured from pale grey for nothing to deep green for a busy day. A year of activity becomes one block you can read in seconds. The script writes the grid and applies a colour gradient with conditional formatting, so the heatmap re-paints itself every time it runs.

What you’ll need

• An Activity sheet with two columns and a header row: date and count. One row per day; days with no activity can simply be absent.
• A Heatmap tab where the grid is drawn. The script clears and rewrites it on every run, so it does not matter what is in it.
• The spreadsheet IDs for the activity data and the dashboard. They are pulled into named constants at the top of the script.

The script

// The spreadsheet holding the raw "Activity" log (date, count).
const ACTIVITY_SHEET_ID = '1abcActivitySheetId';

// The spreadsheet whose "Heatmap" tab the grid is drawn on.
const DASHBOARD_SHEET_ID = '1abcDashboardSheetId';

// Name of the tab that receives the grid.
const HEATMAP_TAB = 'Heatmap';

// Grid shape: 7 days down, 53 weeks across — enough to cover a full year
// plus the partial weeks at each end.
const ROWS = 7;
const COLS = 53;

// The colour gradient. Empty days are grey; the midpoint count and above
// climb from light to dark green.
const COLOUR_LOW = '#ebedf0';
const COLOUR_MID = '#40c463';
const COLOUR_HIGH = '#216e39';
const MID_COUNT = '5';

/**
 * Reads the daily activity log and paints a year-long heatmap grid on
 * the Heatmap tab, coloured by a conditional-formatting gradient.
 */
function buildHeatmap() {
  // 1. Load the activity counts, keyed by ISO date string.
  const data = readActivity();
  if (!data.size) {
    Logger.log('No activity rows found — nothing to draw.');
    return;
  }

  // 2. Work out the grid's start: 364 days back, then rewound to the
  //    Sunday of that week so every column is a clean Sun-Sat block.
  const today = new Date();
  today.setHours(0, 0, 0, 0);
  const startSunday = new Date(today);
  startSunday.setDate(today.getDate() - 364);
  startSunday.setDate(startSunday.getDate() - startSunday.getDay());

  // 3. Fill the grid column by column (week) and row by row (weekday).
  const grid = Array.from({ length: ROWS }, () => Array(COLS).fill(''));
  for (let col = 0; col < COLS; col++) {
    for (let row = 0; row < ROWS; row++) {
      const day = new Date(startSunday);
      day.setDate(day.getDate() + col * 7 + row);

      // Leave future days blank — the grid stops at today.
      if (day > today) continue;

      const iso = day.toISOString().slice(0, 10);
      grid[row][col] = data.get(iso) || 0;
    }
  }

  // 4. Clear the Heatmap tab and write the grid in one block.
  const heat = SpreadsheetApp.openById(DASHBOARD_SHEET_ID)
    .getSheetByName(HEATMAP_TAB);
  heat.clear();
  const range = heat.getRange(1, 1, ROWS, COLS);
  range.setValues(grid);

  // 5. Apply a three-point colour gradient over the whole grid.
  const rule = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMinpoint(COLOUR_LOW)
    .setGradientMidpoint(
      COLOUR_MID,
      SpreadsheetApp.InterpolationType.NUMBER,
      MID_COUNT
    )
    .setGradientMaxpoint(COLOUR_HIGH)
    .setRanges([range])
    .build();
  heat.setConditionalFormatRules([rule]);

  Logger.log('Heatmap repainted for the year ending ' +
    today.toISOString().slice(0, 10));
}

/**
 * Reads the Activity sheet and returns a Map of ISO date string -> count,
 * so the grid builder can look up any day in constant time.
 */
function readActivity() {
  const [header, ...rows] = SpreadsheetApp.openById(ACTIVITY_SHEET_ID)
    .getSheets()[0]
    .getDataRange()
    .getValues();

  return new Map(
    rows.map((row) => {
      // Dates may come back as Date objects or as strings — normalise
      // both to the YYYY-MM-DD form used as the grid key.
      const key = row[0] instanceof Date
        ? row[0].toISOString().slice(0, 10)
        : String(row[0]);
      return [key, row[1]];
    })
  );
}

How it works

1. buildHeatmap calls readActivity, which loads the log and returns a Map of ISO date string to count. If the log is empty it logs a message and stops.
2. It finds the grid’s start date: 364 days before today, then rewound to that week’s Sunday. That guarantees every column is a clean Sunday-to-Saturday week, the same as a GitHub contribution graph.
3. It walks the grid column by column. Each column is a week, each row a weekday. For every cell it computes the actual date, looks it up in the map, and writes the count — or 0 if there was no activity. Days after today are left blank.
4. It clears the Heatmap tab and writes the whole 7×53 grid in one setValues call, which is far faster than touching cells one at a time.
5. It builds a conditional-formatting rule with a three-point gradient — grey at the low end, green at the MID_COUNT midpoint, dark green at the top — and applies it over the entire grid. The colours recompute automatically as the numbers change.

Example run

Say the Activity sheet contains rows like these:

date	count
2025-08-04	3
2025-08-05	8
2025-08-06	1
2025-08-08	12

After a run, the Heatmap tab holds a 7×53 grid of numbers. Each cell is one day, and the conditional-formatting gradient paints them:

• Days with 0 show pale grey (#ebedf0).
• A day with 3 shows a light green, below the midpoint.
• A day with 8 or 12 shows deep green (#216e39).

Read top to bottom for the days of a week, left to right for the weeks of the year. Busy stretches stand out as dark bands; quiet spells stay grey.

Trigger it

This is a scheduled job — the heatmap should reflect yesterday’s activity each morning:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose buildHeatmap, event source Time-driven, a Day timer, and the 2am to 3am slot.
4. Save. The grid now repaints itself overnight.

Watch out for

• The grid is fixed at 7×53. A leap year, or the partial weeks at each end, can push a year just past 53 columns — a day or two may fall off the left edge. For an exact fit, compute the column count from the real start and end dates.
• toISOString returns a UTC date. If your spreadsheet’s timezone is far from UTC, a count logged late in the evening could land on the wrong day in the grid. For most uses the drift is harmless, but be aware of it near midnight.
• The Heatmap tab is cleared on every run. Do not keep notes, labels, or other formulas on it — put month labels or a legend on a separate tab.
• The midpoint count (MID_COUNT) is fixed at 5. If a typical busy day at Northwind is 20 touch-points, the whole grid will look dark green. Tune the midpoint to your real data so the gradient stays meaningful.
• Days entirely absent from the log are drawn as 0. The grid cannot tell “no activity” apart from “not recorded” — make sure the log is complete before you read quiet weeks as genuinely quiet.