📊 How We Built a Dynamic Gantt Chart in Google Sheets

Fully Automated, Dynamically rendered Gantt Chart in Google Sheets!

🎯 Objective:

Create a visual, dynamic Gantt chart in Google Sheets that:

• Auto-updates when data changes
• Shows tasks and sub-tasks, indented
• Displays status icons, assignee avatars, and late warnings
• Includes alternating colored Gantt bars
• Has a menu trigger and onEdit automation
• Includes a legend/reminder so users understand the visuals

---

🛠 Step-by-Step Breakdown

1. Base Setup – Dynamic Gantt Chart

• Started with a TODO sheet containing columns like Task Name, Type, Start Date, Due Date, Status, etc.
• Script that:
  • Pulled tasks dynamically
  • Ignored “Completed” items
  • Generated date columns from the earliest Start Date to latest Due Date
  • Filled cells with colors to create Gantt bars

---

2. Dynamic Headers & Filtering

• Used dynamic column lookup based on header names
• Filtered rows:
  • Type = Task
  • Valid Start Date and Due Date
  • Status ≠ Completed
• Resilient to column changes in the sheet

---

3. Sub-Task Support

• Mapped sub-tasks to their ParentTask
• Indented sub-tasks visually with a bullet •
• Placed sub-tasks below their parent in the Gantt

---

4. Visual Flare

• Alternating parent task colors for better visual separation
• Sub-tasks had a lighter shade of the parent color
• Status icons:
  • 🟢 Not Started
  • 🟡 In Progress
  • 🔴 Blocked
  • ✅ Completed
• Late Warnings:
  • 🚨 for tasks due today or overdue
• Assignee Avatars:
  • 👤 Initials from the “Assigned To” column

---

5. Auto-Resizing Columns + Formatting

• Columns auto-resized based on content
• Frozen header row
• Task and info columns sized for readability

---

6. Adding a Legend/Reminder

• Legend added below the Gantt chart: 📌 Legend:
  • 🟢 Not Started
  • 🟡 In Progress
  • 🔴 Blocked
  • ✅ Completed
  • 🚨 Due Today or Overdue 👤 Initials represent the assigned person (e.g., 👤DI = Devi Innerarity)

---

7. Manual & Automatic Triggers

• onOpen() created a custom menu:
  • 🗺️ Roadmap → Build Roadmap Now
• testBuildRoadmap() for manual refresh
• onEdit() auto-runs the Gantt update when TODO changes
• Installed installable trigger for onEdit:
• Script Editor → Triggers → Add Trigger → onEdit → From Spreadsheet → On Edit

---

🚀 How to Use It:

1. Edit any task in TODO → Roadmap auto-refreshes
2. Or click 🗺️ Roadmap → Build Roadmap Now for manual refresh
3. Scroll to see:

• Colorful task bars
• Status icons, avatars, 🚨 warnings
• Legend at the bottom

---

💡 What You Can Add Next:

• Filtering by assignee or category
• Adding progress % bars
• Exporting the Gantt to PDF
• Interactive sidebars or buttons

---

🧑‍💻 Tech Stack:

• Google Apps Script
• Google Sheets dynamic functions
• Emoji + color-based UX styling

---

🎉 End Result:

You now have a fully automated, highly visual, executive-grade Gantt Chart system inside Google Sheets — no expensive software needed.

---

Code:

 function onOpen() {
   const ui = SpreadsheetApp.getUi();
   ui.createMenu("🗺️ Roadmap")
     .addItem("Build Roadmap Now", "testBuildRoadmap")
     .addToUi();
 }

function onEdit(e) {
  try {
    if (!e || !e.range || !e.source) return;
    const sheet = e.range.getSheet();
    if (sheet.getName() === 'TODO') {
      buildRoadmapGrid(true); // logs enabled
    }
  } catch (err) {
    logToSheet('onEdit error', err.message);
  }
}

function testBuildRoadmap() {
  buildRoadmapGrid(true); // logs enabled
}

function buildRoadmapGrid(debug = false) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const src = ss.getSheetByName('TODO');
  const dstName = 'Roadmap';
  let dst = ss.getSheetByName(dstName);

  if (dst) ss.deleteSheet(dst);
  dst = ss.insertSheet(dstName);

  const lastRow = src.getLastRow();
  const lastCol = src.getLastColumn();
  if (lastRow < 2) {
    if (debug) logToSheet('Exit', 'No data in TODO');
    return;
  }

  const headers = src.getRange(1, 1, 1, lastCol).getValues()[0];
  const colIndex = headerName =>
    headers.findIndex(h => h.trim().toLowerCase() === headerName.trim().toLowerCase());
  const getCol = name => {
    const idx = colIndex(name);
    if (idx === -1) throw new Error(`Missing column: "${name}"`);
    return idx;
  };

  const TASK_NAME = getCol("Task Name");
  const TYPE = getCol("Type");
  const PARENT = getCol("ParentTask");
  const START = getCol("Start Date");
  const DUE = getCol("Due Date");
  const STATUS = getCol("Status");
  const ASSIGNED = getCol("Assigned To");

  const raw = src.getRange(2, 1, lastRow - 1, lastCol).getValues();
  const today = new Date();
  today.setHours(0, 0, 0, 0);

  const taskMap = new Map();
  raw.forEach(row => {
    const taskName = String(row[TASK_NAME]).trim();
    taskMap.set(taskName, row);
  });

  const groupedTasks = [];

  taskMap.forEach((row, name) => {
    const type = String(row[TYPE]).trim().toLowerCase();
    const status = String(row[STATUS]).trim().toLowerCase();
    const validDates = row[START] instanceof Date && row[DUE] instanceof Date;

    if (type === 'task' && status !== 'completed' && validDates) {
      groupedTasks.push({ label: name, row, isSub: false });
      raw.forEach(subRow => {
        const subType = String(subRow[TYPE]).trim().toLowerCase();
        const subStatus = String(subRow[STATUS]).trim().toLowerCase();
        const subParent = String(subRow[PARENT]).trim();
        const valid = subRow[START] instanceof Date && subRow[DUE] instanceof Date;
        const isSub = subType === 'sub-task' && subParent === name;
        if (isSub && subStatus !== 'completed' && valid) {
          groupedTasks.push({
            label: '• ' + String(subRow[TASK_NAME]).trim(),
            row: subRow,
            isSub: true
          });
        }
      });
    }
  });

  if (debug) logToSheet('Grouped Tasks', groupedTasks.length);
  if (groupedTasks.length === 0) {
    if (debug) logToSheet('Exit', 'No matching tasks with valid dates');
    return;
  }

  const allDates = groupedTasks.flatMap(t => [t.row[START], t.row[DUE]])
    .filter(d => d instanceof Date);
  const minStart = new Date(Math.min(...allDates.map(d => d.getTime())));
  const maxDue = new Date(Math.max(...allDates.map(d => d.getTime())));

  const dates = [];
  for (let d = new Date(minStart); d <= maxDue; d.setDate(d.getDate() + 1)) {
    dates.push(new Date(d));
  }

  // === HEADER ROW ===
  dst.getRange(1, 1).setValue("Task Name");
  dst.getRange(1, 2).setValue("Info");
  dst.getRange(1, 3, 1, dates.length).setValues([dates]);
  dst.getRange(1, 3, 1, dates.length)
    .setFontWeight('bold')
    .setHorizontalAlignment('center')
    .setNumberFormat('MM/dd');
  dst.getRange(1, dates.length + 4).setValue(`🛠️ Updated on ${new Date().toLocaleString()}`);

  // === ROW DATA ===
  const taskNames = groupedTasks.map(t => [t.label]);
  const info = groupedTasks.map(t => {
    const status = String(t.row[STATUS]).toLowerCase();
    const due = t.row[DUE];
    const assignee = String(t.row[ASSIGNED] || "").trim();
    const initials = assignee.split(' ').map(w => w[0]).join('').toUpperCase();

    const avatar = initials ? `👤${initials}` : '';
    const icon = getStatusIcon(status);
    const late = due < today && status !== 'completed' ? ' 🚨' : '';

    return [`${icon}${late} ${avatar}`.trim()];
  });

  dst.getRange(2, 1, taskNames.length, 1).setValues(taskNames);
  dst.getRange(2, 2, info.length, 1).setValues(info);

  // === COLOR "BARS" ACROSS DATES ===
  const parentColors = [
    ['#cfe2f3', '#e9f1f9'],
    ['#d9ead3', '#eef7eb'],
    ['#fce5cd', '#fdf2e9'],
    ['#ead1dc', '#f7ecf4']
  ];

  let colorIndex = -1;
  const bg = [];
  let currentParentColor = parentColors[0];

  for (let i = 0; i < groupedTasks.length; i++) {
    const task = groupedTasks[i];
    if (!task.isSub) {
      colorIndex = (colorIndex + 1) % parentColors.length;
      currentParentColor = parentColors[colorIndex];
    }
    const start = task.row[START].getTime();
    const end = task.row[DUE].getTime();
    const fill = task.isSub ? currentParentColor[1] : currentParentColor[0];
    bg.push(dates.map(d => {
      const t = d.getTime();
      return t >= start && t <= end ? fill : null;
    }));
  }

  dst.getRange(2, 3, bg.length, dates.length).setBackgrounds(bg);

  // === FORMATTING ===
  dst.setFrozenRows(1);
  dst.setColumnWidth(1, 240);
  dst.setColumnWidth(2, 120);
  for (let i = 3; i <= dates.length + 2; i++) {
    dst.autoResizeColumn(i);
  }

  // === LEGEND / REMINDER SECTION ===
  const legendStartRow = groupedTasks.length + 4;
  dst.getRange(legendStartRow, 1, 1, 1).setValue("📌 Legend:");
  dst.getRange(legendStartRow + 1, 1).setValue("🟢 Not Started");
  dst.getRange(legendStartRow + 1, 2).setValue("🟡 In Progress");
  dst.getRange(legendStartRow + 1, 3).setValue("🔴 Blocked");
  dst.getRange(legendStartRow + 1, 4).setValue("✅ Completed");
  dst.getRange(legendStartRow + 1, 5).setValue("🚨 Due Today or Overdue");
  dst.getRange(legendStartRow + 2, 1, 1, 5).mergeAcross().setValue("👤 Initials represent the assigned person (e.g., 👤DI = Devi Innerarity)");

  // Style the legend
  dst.getRange(legendStartRow, 1, 3, 5).setFontStyle("italic").setFontSize(10);

  if (debug) logToSheet('Success', `Wrote ${groupedTasks.length} items`);
}

// === STATUS ICON LOOKUP ===
function getStatusIcon(status) {
  const map = {
    'not started': '🟢',
    'in progress': '🟡',
    'blocked': '🔴',
    'completed': '✅'
  };
  return map[status] || '❓';
}

// === LOGGING ===
function logToSheet(label, value) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetName = 'Debug';
  let sheet = ss.getSheetByName(sheetName);
  if (!sheet) sheet = ss.insertSheet(sheetName);
  sheet.appendRow([new Date().toLocaleString(), label, value]);
}