Generate a project burndown chart

Northwind runs its projects out of a single Tasks sheet — every task with a point estimate and, once it is done, a completion date. That tells you what has been finished, but it does not tell you the thing a project lead actually wants to know: are we on track, or quietly slipping?

A burndown chart answers that. It plots the work still remaining against an ideal straight line from the project’s total points down to zero on the deadline. If the “remaining” line sits above the ideal, the project is behind; if it dips below, it is ahead. This automation rebuilds the data behind that chart every night, so the picture is current the moment anyone opens the sheet.

What you’ll need

• A Tasks sheet with a header row and three columns: project, points, and completedDate (left blank until the task is finished).
• A Burndown tab — the script writes date, ideal and remaining into it. Create the tab; the script fills the columns.
• The two spreadsheet IDs dropped into the config block in place of the placeholders.
• A nightly trigger, set up once — see Trigger it.

The script

// The spreadsheet holding the Tasks sheet.
const TASKS_SHEET_ID = '1abcTasksSheetId';

// The spreadsheet holding the Burndown tab (often the same file).
const BURNDOWN_SHEET_ID = '1abcBurndownSheetId';

// The tab the chart data is written to.
const BURNDOWN_TAB_NAME = 'Burndown';

// Milliseconds in a day — used to count the project's length.
const MS_PER_DAY = 86400000;

/**
 * Rebuilds the Burndown tab for one project: an ideal burn-down line and
 * the actual points still remaining on each day of the project.
 *
 * @param {string} project The project name to chart.
 * @param {Date} startDate The project start date.
 * @param {Date} endDate The project deadline.
 * @param {number} totalPoints The total points planned for the project.
 */
function rebuildBurndown(project, startDate, endDate, totalPoints) {
  // 1. Read the Tasks sheet and keep only this project's tasks.
  const tasks = readSheet(TASKS_SHEET_ID)
    .filter((t) => t.project === project);

  // 2. Work out the project length and the ideal points burned per day.
  const days = Math.ceil((endDate - startDate) / MS_PER_DAY);
  if (days <= 0) {
    Logger.log('End date is not after start date — nothing to chart.');
    return;
  }
  const idealPerDay = totalPoints / days;

  // 3. For each day, compute the ideal remaining and the actual remaining.
  const rows = [];
  for (let i = 0; i <= days; i++) {
    const d = new Date(startDate);
    d.setDate(d.getDate() + i);

    // Points burned = sum of points for tasks completed on or before day d.
    const burned = tasks
      .filter((t) => t.completedDate && t.completedDate <= d)
      .reduce((sum, t) => sum + t.points, 0);

    rows.push([
      d,
      Math.max(0, totalPoints - i * idealPerDay), // ideal line
      totalPoints - burned,                       // actual remaining
    ]);
  }

  // 4. Clear the Burndown tab and write the fresh data.
  const sheet = SpreadsheetApp.openById(BURNDOWN_SHEET_ID)
    .getSheetByName(BURNDOWN_TAB_NAME);
  sheet.clearContents();
  sheet.getRange(1, 1, 1, 3).setValues([['date', 'ideal', 'remaining']]);
  sheet.getRange(2, 1, rows.length, 3).setValues(rows);
  Logger.log(`Wrote ${rows.length} days of burndown data for ${project}.`);
}

/**
 * Reads a sheet's first tab into an array of objects, one per row, keyed
 * by the header names.
 *
 * @param {string} id The spreadsheet ID to read.
 * @return {Object[]} One object per data row.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

How it works

1. rebuildBurndown reads the Tasks sheet through the readSheet helper and filters it down to the one project you are charting.
2. It works out the project length in days from the start and end dates, and bails out if the end date is not after the start.
3. The ideal burn rate is the total points divided by the number of days — the slope of the perfect straight line.
4. It loops over every day of the project. For each day it sums the points of every task completed on or before that day to get the work burned, and subtracts that from the total to get the actual remaining.
5. The ideal remaining for that day is the total minus idealPerDay times the day index, clamped at zero so the line never goes negative.
6. It clears the Burndown tab and writes a fresh date, ideal, remaining row for every day, ready for the chart to draw.
7. readSheet is a small helper that turns any sheet into an array of objects keyed by the header row, so the rest of the code reads by column name.

Example run

For a 10-day project worth 100 points, after a few days of work the Burndown tab might look like this:

date	ideal	remaining
01 Aug	100	100
02 Aug	90	95
03 Aug	80	88
04 Aug	70	76
05 Aug	60	65

The remaining figures sit just above the ideal line — the project is a few points behind, but tracking close. Plotted as a line chart, the gap between the two lines is the schedule risk at a glance.

Add the chart

Once the tab has data, draw the chart once and it will redraw itself on every nightly refresh:

1. Select columns A:C on the Burndown tab.
2. Choose Insert → Chart and pick Line chart.
3. Sheets plots ideal and remaining against the date — the classic burndown picture.

Trigger it

Refresh the data nightly so the chart is current each morning:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose rebuildBurndown, event source Time-driven, type Day timer, and pick the 11pm–midnight slot.
4. Save and approve the authorisation prompt.

rebuildBurndown takes arguments, so a time trigger cannot call it directly. Add a small wrapper with no arguments — for example nightlyBurndown() that calls rebuildBurndown('Apollo', new Date('2025-08-01'), new Date('2025-08-11'), 100) — and point the trigger at the wrapper instead.

Watch out for

• The chart covers one project per tab. To track several projects, give each its own Burndown tab and its own wrapper function.
• completedDate must be a real date, not text. A date typed as a string will not compare correctly and the task will never count as burned. Format the column as a date.
• The ideal line assumes a steady burn. Real projects rarely burn evenly — the ideal line is a reference, not a target to hit exactly day by day.
• totalPoints is passed in, not derived from the sheet. If the project scope changes, update the wrapper’s argument or the ideal line will be wrong.
• The script rewrites the whole tab every run, so do not add notes or extra columns to the Burndown tab — they will be cleared. Keep annotations on the chart or a separate sheet.