Most project trackers break in the same way: the list of tasks gets long, status columns go stale, and the spreadsheet becomes a place tasks go to be forgotten rather than finished.

This tutorial builds a tracker that stays useful because it surfaces what matters — what's overdue, what's blocked, who owns what — without manual sorting or filtering.

By the end, you'll have:

  • A task log with status, owner, due date, and priority
  • A summary row showing counts by status (Done, In Progress, Overdue)
  • Conditional formatting that highlights overdue tasks and blocked items automatically
  • Optional: an Apps Script that emails owners a list of their overdue tasks every Monday

---

Step 1: Set Up the Task Log

Open a new Google Sheet. Rename the first tab Tasks.

Add these headers in Row 1:

| A | B | C | D | E | F | |---|---|---|---|---|---| | Task | Owner | Due Date | Priority | Status | Notes |

Add sample rows:

| Task | Owner | Due Date | Priority | Status | Notes | |------|-------|----------|----------|--------|-------| | Write project brief | alex@example.com | 2026-04-25 | High | In Progress | | | Schedule kickoff call | sam@example.com | 2026-04-23 | Medium | Done | | | Review design mockups | alex@example.com | 2026-04-20 | High | Overdue | Waiting on feedback |

Format the Due Date column: Select column C, go to Format > Number > Date.

Add a Status dropdown: Select column E, go to Data > Data validation, choose Dropdown (from a list), and enter: Not Started, In Progress, Done, Blocked, Overdue.

Add a Priority dropdown: Same process for column D: High, Medium, Low.

---

Step 2: Add Conditional Formatting

Select A2:F (all columns, all rows from row 2 down).

Go to Format > Conditional formatting. Add these rules in order:

Rule 1 — Red: Overdue

  • Custom formula: =$E2="Overdue"
  • Fill: light red (#FFDAD6)

Rule 2 — Orange: Blocked

  • Custom formula: =$E2="Blocked"
  • Fill: light orange (#FFE0B2)

Rule 3 — Green: Done

  • Custom formula: =$E2="Done"
  • Fill: light green (#DCFCE7)

Rule 4 — Overdue by date, not marked (catches tasks missed in the Status column)

  • Custom formula: =AND($C2"", $E2<>"Done")
  • Fill: light red (#FFDAD6)

Rules 1–3 fire on the Status dropdown. Rule 4 catches tasks where the due date passed but nobody updated the status.

---

Step 3: Build the Summary Bar

Add a small status summary above the task list — in row 1 of a spare column, or in a separate area.

In cell H1:

Code
=COUNTIF(E:E, "Done")

In cell H2:

Code
=COUNTIF(E:E, "In Progress")

In cell H3:

Code
=COUNTIF(E:E, "Overdue")

In cell H4:

Code
=COUNTIFS(C:C, "<"&TODAY(), C:C, "<>", E:E, "<>Done")

Add labels in G1:G4: Done, In Progress, Overdue (status), Overdue (by date).

This gives you a live count of what's in each state. No pivot table, no filtering needed.

---

Step 4: Filter Views for Each Owner

Instead of having everyone work on the same filtered view, use Filter views — personal filters that don't affect what others see.

  1. Click Data > Filter views > Create new filter view
  2. Name it "Alex's Tasks"
  3. Click the filter icon on the Owner column
  4. Filter to show only alex@example.com
  5. Click OK

Each team member can create their own filter view. The URL changes when a filter view is active — you can share the link directly to share a specific view.

---

Step 5: Email Overdue Tasks to Owners (Optional)

This Apps Script runs every Monday morning, finds tasks that are overdue or have past their due date, groups them by owner, and sends each person their list.

Open Extensions > Apps Script, delete the placeholder, and paste:

Code
function emailOverdueTasks() {

  // -- SETTINGS --
  var sheetName = "Tasks";
  var ownerCol   = 2;  // Column B
  var dueDateCol = 3;  // Column C
  var statusCol  = 5;  // Column E
  var taskCol    = 1;  // Column A

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data  = sheet.getDataRange().getValues();
  var today = new Date();
  today.setHours(0, 0, 0, 0);

  // Group overdue tasks by owner email
  var byOwner = {};

  for (var i = 1; i < data.length; i++) {
var task    = data[i][taskCol - 1];
var owner   = data[i][ownerCol - 1];
var dueDate = data[i][dueDateCol - 1];
var status  = data[i][statusCol - 1];

if (!task || !owner || status === "Done") continue;
if (!(dueDate instanceof Date)) continue;

dueDate.setHours(0, 0, 0, 0);
if (dueDate >= today) continue;  // Not yet overdue

if (!byOwner[owner]) byOwner[owner] = [];
byOwner[owner].push({
  task: task,
  due: Utilities.formatDate(dueDate, Session.getScriptTimeZone(), "MMM d, yyyy"),
});
  }

  // Send one email per owner
  for (var email in byOwner) {
var tasks = byOwner[email];
var body = "Hi,\n\nYou have " + tasks.length + " overdue task" + (tasks.length > 1 ? "s" : "") + ":\n\n";
tasks.forEach(function(t) {
  body += "• " + t.task + " (was due " + t.due + ")\n";
});
body += "\nView the full tracker: " + SpreadsheetApp.getActiveSpreadsheet().getUrl();
body += "\n\nSent automatically from Google Sheets.";

GmailApp.sendEmail(email, "Overdue tasks — " + Utilities.formatDate(today, Session.getScriptTimeZone(), "MMM d"), body);
Logger.log("Sent to: " + email);
  }
}

To schedule this for every Monday morning:

  1. Click the clock icon (Triggers) in the left sidebar
  2. Click + Add Trigger
  3. Function: emailOverdueTasks, event source: Time-driven, type: Week timer, day: Monday, time: 7am–8am
  4. Click Save and authorize

Each owner only gets their own overdue tasks. Non-email strings in the Owner column are skipped (so you can use names in some rows without breaking the script).

---

Common Problems

Conditional formatting doesn't apply to new rows

Your range is set to a fixed range like A2:F50. Change the "Apply to range" to A2:F to cover all future rows.

Overdue tasks aren't turning red automatically

Check that the due dates are stored as real dates (right-aligned in the cell, not left-aligned text). Go to Format > Number > Date to verify.

The script sends to wrong addresses

The script reads the Owner column as-is. Make sure owner entries are valid email addresses. Test with Logger.log() before enabling the trigger.

---

What You Have Now

  • A task tracker that highlights overdue and blocked items automatically
  • A live status summary that updates as tasks change
  • Per-owner filter views for focused work
  • Optional weekly overdue emails to keep owners accountable

---

What to Build on Top of This

Don't want to set this up yourself? Describe your project structure and it'll be configured for your sheet. Get it installed