Gantt charts in Sheets or Looker need clean start/end/duration and progress fractions.
projectGanttDataPrep writes inclusive durationDays and clamps pctComplete into 0–1.
Inclusive counting matches how many calendar days a bar should span (Mon–Mon = 1 day? here Mon–Tue = 2).
Feed the prepared Task sheet to a stacked-bar Gantt or timeline chart.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| Tasks | A task | Task name |
| Tasks | B start | Start date |
| Tasks | C end | End date |
| Tasks | D owner | Owner |
| Tasks | E durationDays | Output |
| Tasks | F pctComplete | 0–1 progress |
What this script does
Normalizes task date spans and progress for charting.
Prerequisites
Start/end as Dates; pct as fraction or editable number.
- Inclusive day policy agreed
- End >= start
- Chart uses E and F
Walkthrough
Task Jan 1–Jan 3 → durationDays 3.
Edge cases
End before start yields blank duration.
- pct clamped
- Non-dates clear duration
How to test
pct 1.5 becomes 1; -0.2 becomes 0.
Hardening for production
Skip weekends in duration; add dependency columns.
Variations
Output a separate GanttData sheet with one row per day for heatmaps.
Full code: projectGanttDataPrep()
Enter task dates and progress, run projectGanttDataPrep(), then point your Gantt chart at Tasks.
/**
* Prepare Gantt-friendly rows: start, end, durationDays from tasks.
*/
const TASKS = "Tasks"; // A task, B start, C end, D owner, E durationDays, F pctComplete
function projectGanttDataPrep() {
const sheet = SpreadsheetApp.getActive().getSheetByName(TASKS);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const start = values[i][1];
const end = values[i][2];
if (!(start instanceof Date) || !(end instanceof Date)) {
values[i][4] = "";
continue;
}
const days = Math.round((end.getTime() - start.getTime()) / 86400000) + 1; // inclusive
values[i][4] = days > 0 ? days : "";
let pct = Number(values[i][5]);
if (isNaN(pct)) pct = 0;
values[i][5] = Math.max(0, Math.min(1, pct));
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 16: Milliseconds per day.
- Line 16: Inclusive day count.
- Line 4: Clamped to 0–1.
- Line 2: Column E output.
- Line 12: Validates start/end types.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: Gantt prep
- 1Dates are Date cells
- 2Inclusive duration understood
- 3pctComplete is fraction not percent (or divide by 100)
- 4Chart data range updated
- 5Owners filled for filters
- 6Test a multi-week task
Frequently asked questions
Divide by 100 before clamp, or store 0–100 and adjust the chart.
Loop dates and skip weekends/holidays instead of simple subtraction.
Use start=end and durationDays 1, or a milestone flag column.
Store predecessor ids; schedule separately.
Use date-only values at noon local to avoid DST edge shifts.
Same columns work as dimensions/metrics after prep.
Missing Date types or end before start.