Build a Lightweight Project Tracker in Google Sheets

By Swiftools Team · Published January 14, 2026 · 8 min read

A handwritten planner page with tags and pens

Most teams have used at least three of the following: Asana, Trello, ClickUp, Jira, Monday, Linear, Notion, Basecamp. The pattern is the same. New tool gets adopted with enthusiasm. Within six months, half the team has stopped opening it, and the real status of the project is being tracked in someone's notebook, a private Slack thread, or - inevitably - a Google Sheet.

This article is an honest acknowledgment of that pattern. For a lot of teams and a lot of projects, the right project tracker is a small, well-built spreadsheet. The setup below is the version we use ourselves for projects under about 200 tasks - and it has survived where the "real" tools didn't.

The columns

Set up Sheet1 with these columns in row 1:

ID | Task | Owner | Status | Priority | Due | Blocked By | Notes | Updated

Nine columns. That's the entire schema. Resist the urge to add "estimated hours," "actual hours," "category," "subcategory," "tags." Every column you add increases the maintenance burden and decreases the chance that anyone actually keeps the sheet current.

The formulas that do the work

Auto-incrementing task IDs

In cell A2, paste this formula. It runs once and fills the whole ID column automatically as tasks are added:

=ARRAYFORMULA(IF(B2:B="", "", "T-" & TEXT(ROW(B2:B)-1, "000")))

Type a task name in column B and a tidy ID like T-001 appears in column A. Move a row, and the ID changes - but that's fine for internal tracking. If you need stable IDs across sorts, use a separate column with manually-entered values.

"Updated" timestamp on every change

The Updated column is the closest thing this setup has to revision history. Add a script (Extensions → Apps Script):

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  if (sheet.getName() !== 'Sheet1') return;
  const row = e.range.getRow();
  if (row === 1) return;
  const updatedCol = 9;  // Column I
  if (e.range.getColumn() === updatedCol) return;  // avoid loop
  sheet.getRange(row, updatedCol).setValue(new Date());
}

Every edit to any column on a row stamps the Updated column with the current timestamp. Now you can sort by Updated to see what's actually moving versus what's been sitting stale for two weeks.

Conditional formatting for status and overdue

Select the Status column → Format → Conditional formatting. Add these rules:

  • Text contains Done → green background
  • Text contains In Progress → blue background
  • Text contains Blocked → red background
  • Text contains To Do → light gray

Select the Due column → conditional formatting → Custom formula: =AND($F2<TODAY(), $D2<>"Done") → red background. Overdue tasks (any task with a due date in the past that isn't Done) light up red automatically. This single rule, more than any other piece of this setup, is what keeps the sheet honest.

A data validation list for Status and Priority

Select the Status column → Data → Data validation → "Dropdown" → list: To Do, In Progress, Blocked, Done. Same for Priority with P0, P1, P2, P3.

Locking these columns to a fixed vocabulary prevents the slow drift that ruins most ad hoc trackers - someone writes "doing it," someone else writes "wip," a third person writes "in progress," and now your filter for "what's currently being worked on" doesn't catch everything.

The Kanban view (built with formulas only)

Add a second sheet, call it "Board." In row 1, put column headers: To Do | In Progress | Blocked | Done. Then in cell A2:

=FILTER(Sheet1!B:B, Sheet1!D:D=A1, Sheet1!B:B<>"")

In B2:

=FILTER(Sheet1!B:B, Sheet1!D:D=B1, Sheet1!B:B<>"")

...and so on for C2 and D2. You now have a live Kanban-style view: each column shows the tasks currently in that status, populated from the main sheet. Change a task's status in Sheet1, and it moves between columns in the Board view automatically.

For a cleaner visual, set the column widths equal, give each header a background color matching the status colors, and freeze row 1.

The "what's mine" view per person

Add a third sheet called "My Tasks." Put this in B1:

=GOOGLEEMAIL()

(or hardcode the user's name in B1 for shared use). Then in A3:

=QUERY(Sheet1!A:I, "SELECT A, B, D, E, F WHERE C = '" & B1 & "' AND D <> 'Done' ORDER BY F ASC", 1)

This produces a personal task list for whoever's looking at the sheet: their open tasks, sorted by due date. QUERY syntax is one of the most underrated features in Sheets - it's basically SQL inside a spreadsheet.

Dependencies, lightly

The "Blocked By" column takes a comma-separated list of task IDs - T-004, T-007. To highlight tasks whose blockers aren't done yet, add this conditional formatting on the Status column:

=AND($G2<>"",
     COUNTIF(SPLIT($G2, ","), "*") >
     COUNTIFS(A:A, SPLIT($G2, ","), D:D, "Done"))

It flags any task whose blockers aren't all complete. Full Gantt-style dependency rendering is out of scope for this setup - if you need that, you've outgrown the spreadsheet approach and should use a real PM tool.

A simple weekly digest

Set up an Apps Script time-based trigger to send a Friday summary to whoever owns the project. The script:

function weeklyDigest() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  const data = sheet.getDataRange().getValues();
  const thisWeek = new Date();
  thisWeek.setDate(thisWeek.getDate() - 7);

  let completed = 0, opened = 0, overdue = 0;
  data.slice(1).forEach(row => {
    const status = row[3], due = row[5], updated = row[8];
    if (status === 'Done' && updated && new Date(updated) >= thisWeek) completed++;
    if (status !== 'Done' && updated && new Date(updated) >= thisWeek) opened++;
    if (status !== 'Done' && due && new Date(due) < new Date()) overdue++;
  });

  MailApp.sendEmail({
    to: '[email protected]',
    subject: 'Project digest: ' + completed + ' done, ' + overdue + ' overdue',
    body: 'Completed this week: ' + completed + '\n' +
          'Updated this week: ' + opened + '\n' +
          'Overdue: ' + overdue
  });
}

Schedule it to run every Friday at 4pm. Now even people who don't open the sheet get a one-line read on project health every week.

What this setup does and doesn't do

Does: Task tracking, status, ownership, due dates, basic dependencies, personal task lists, Kanban view, conditional highlighting of overdue items, weekly digest. Everything most internal projects need.

Doesn't: Time tracking, real Gantt charts with critical-path calculation, sprint planning rituals, version history at the field level, complex permissions per task. If you need those, use a real tool.

The honest threshold: this setup works well up to about 200 active tasks or 8-10 people regularly editing. Past that, the lack of fine-grained permissions, the awkwardness of "comments on a task," and the limits of formula performance start to matter. Below that, every project management tool we've tried adds more friction than it removes.

Why it survives where the "real" tools don't

Three reasons.

Sheets requires zero onboarding. Anyone can read and edit it without training, plugins, or learning a new vocabulary. The cognitive cost of opening it is approximately zero, which means people actually open it.

The data is portable, queryable, and exportable. You can paste from it, link to it, filter it, build pivot tables off it, sync it to a database (per our sync post), embed it in a doc. PM tools are usually walled gardens; Sheets data goes anywhere.

You're never paying for unused features. PM tools earn revenue by adding features, which means each year the tool is more complex than the previous. A spreadsheet stays exactly as complex as the formulas you wrote, indefinitely.

The grand design pattern: use the smallest tool that handles the actual current problem, and only graduate when you've concretely outgrown it - not when a vendor's marketing convinces you that you might.

Sources & Further Reading

Related Articles