Build a self-updating Gantt chart

Northwind tracks every client engagement in a Projects sheet — one row per project with a client, a name, a start date, and an end date. That is enough to know the schedule, but not enough to see it. Spotting overlaps, gaps, and crunch weeks means reading dates off a list and holding the picture in your head.

This script turns the list into a timeline. It reads the Projects sheet, works out the full span of weeks it covers, and rebuilds a Gantt sheet with one column per week and one row per project — drawing a bar of block characters across the weeks each project is live. Run it on a schedule and the chart redraws itself whenever a date changes.

What you’ll need

• A Projects sheet with a header row and four columns: client, project, start, and end. The start and end cells must hold real dates, not text.
• A separate spreadsheet (or tab) for the rendered chart. The script writes to the first tab and rebuilds it on every run.

The script

// The spreadsheet holding the source project list.
const PROJECTS_SHEET_ID = '1abcProjectsSheetId';

// The spreadsheet that receives the rendered Gantt chart.
const GANTT_SHEET_ID = '1abcGanttSheetId';

// Milliseconds in one week — used for all week-span arithmetic.
const WEEK_MS = 7 * 86400000;

// The character used to draw a bar cell.
const BAR = '█';

/**
 * Reads the Projects sheet and rebuilds the Gantt sheet, drawing a bar
 * across every week each project is active.
 */
function rebuildGantt() {
  // 1. Load every project row as an object.
  const projects = readSheet(PROJECTS_SHEET_ID);
  if (!projects.length) {
    Logger.log('No projects found — nothing to draw.');
    return;
  }

  // 2. Find the overall span and how many week columns it needs.
  const earliest = new Date(Math.min(...projects.map((p) => p.start.getTime())));
  const latest = new Date(Math.max(...projects.map((p) => p.end.getTime())));
  const weeks = Math.ceil((latest - earliest) / WEEK_MS) + 1;

  // 3. Build the header: a "Project" label, then one date per week.
  const header = ['Project'];
  for (let w = 0; w < weeks; w++) {
    const d = new Date(earliest);
    d.setDate(d.getDate() + w * 7);
    header.push(Utilities.formatDate(d, 'GMT', 'd MMM'));
  }

  // 4. Build one row per project, marking the weeks it overlaps.
  const rows = projects.map((p) => {
    const row = Array(weeks + 1).fill('');
    row[0] = `${p.client} — ${p.project}`;
    for (let w = 0; w < weeks; w++) {
      const weekStart = new Date(earliest);
      weekStart.setDate(weekStart.getDate() + w * 7);
      const weekEnd = new Date(weekStart);
      weekEnd.setDate(weekEnd.getDate() + 7);
      // The project touches this week if its span overlaps the week.
      if (p.end >= weekStart && p.start < weekEnd) row[w + 1] = BAR;
    }
    return row;
  });

  // 5. Clear the Gantt sheet and write the header plus every row.
  const sheet = SpreadsheetApp.openById(GANTT_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, rows.length + 1, weeks + 1).setValues([header, ...rows]);
  Logger.log('Drew ' + rows.length + ' projects across ' + weeks + ' weeks.');
}

/**
 * Reads a sheet's first tab and returns each row as an object keyed
 * by the header 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. rebuildGantt calls readSheet to load the Projects sheet, turning each row into an object keyed by the header so columns are read by name. If there are no projects, it logs and stops.
2. It finds the earliest start and latest end across all projects, then divides that span by WEEK_MS to work out how many week columns the chart needs.
3. It builds the header row — a Project label followed by one short date label per week, each one week after the last.
4. For each project it creates a blank row, sets the first cell to client — project, then walks the weeks. A week gets a █ if the project’s span overlaps it: the project ends on or after the week starts and starts before the week ends.
5. It clears the Gantt sheet and writes the header and every project row in a single setValues call.

Example run

The Projects sheet holds three projects:

client	project	start	end
Acme	Rebrand	04/08/2025	17/08/2025
Globex	Audit	11/08/2025	24/08/2025
Initech	Launch	18/08/2025	24/08/2025

After a run, the Gantt sheet renders the overlap at a glance:

Project	4 Aug	11 Aug	18 Aug
Acme — Rebrand	█	█
Globex — Audit		█	█
Initech — Launch			█

You can see immediately that Acme and Globex collide in the week of 11 Aug.

Trigger it

Redraw the chart automatically so it never goes stale:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger for rebuildGantt, time-driven, on a daily timer.
3. Save. Any date edited in Projects shows up in the chart by the next day.

Watch out for

• The start and end cells must be real dates. If someone types a date as text, .getTime() will fail and the run will throw.
• The chart spans the full range from the earliest start to the latest end. One project far in the future stretches every row with empty week columns.
• █ is a plain text character, not conditional formatting. It is readable but not styled — for coloured bars, apply conditional formatting to the chart range as a one-off so it survives the clear.
• clear wipes the whole Gantt sheet, including any manual notes. Keep the chart on its own dedicated tab.
• Wide date ranges produce many columns. A year of weekly columns is 52 — fine for Sheets, but it will scroll.