This report documents the automation system built for the Task Tracker Google Sheet. The system keeps tasks synchronized between two tabs — Task Tracker and Done — based on the value of the Status column, with no manual row-moving required.

Current status

The automation is live and running. It was verified working as of June 21, 2026, with a confirmed successful sync test.

What it does

Automatically moves any task marked Done, Completed, or Complete from Task Tracker to the Done tab
Automatically moves a row back from Done to Task Tracker if its status is changed to anything other than Done/Completed/Complete
Adds a timestamp (“Moved to Done On”) recording when each task was completed
Detects the Status column automatically from the header row, so column order can change without breaking anything
Runs automatically every 60 seconds in the background — no manual action or button click needed

2. System Architecture

The system consists of two sheet tabs and one Apps Script project attached to the spreadsheet.

2.1 Components

Component	Role
Task Tracker	Source sheet — active, in-progress tasks live here
Done	Destination sheet — completed tasks land here with a completion timestamp
Apps Script project	Bound script containing the syncRows() function and the time-based trigger
Time-based trigger	Calls syncRows() automatically once every minute

2.2 Data flow

Each time the script runs, it performs two passes in sequence:

Task Tracker → Done: every row whose Status cell reads done, completed, or complete (case-insensitive) is copied to the next empty row in Done, stamped with the current date/time, and removed from Task Tracker.
Done → Task Tracker: every row in Done whose Status is no longer done/completed/complete is copied back to the next empty row in Task Tracker (with the timestamp column dropped) and removed from Done.

Both passes use the header row to locate the Status column by name, so the logic does not depend on Status being in a fixed column position.

3. Automation Logic

3.1 Trigger mechanism

The automation runs on a time-based (installable) trigger rather than a simple onEdit trigger. This was a deliberate design decision after the initial onEdit-based approach repeatedly hit Google’s 6-minute execution limit and caused infinite-loop errors (deleting a row re-triggered the same edit event).

Setting	Value	Notes
Trigger type	Time-driven	Not onEdit
Function	syncRows	Runs both directions
Frequency	Every 1 minute	Configurable

3.2 Status matching rules

Recognized “done” values: Done, Completed, Complete
Matching is case-insensitive — DONE, done, Done, DoNe all match
Leading/trailing whitespace is trimmed before comparison
Completely blank rows are ignored in both directions

3.3 Safety mechanisms

Status column is located dynamically by reading the header row — never hardcoded to a column letter
Row movement is all-or-nothing per sync cycle: rows are batched, written, then cleared, avoiding partial writes
The Done sheet and its “Moved to Done On” column are created automatically if missing, so the script self-heals from an accidentally deleted tab or column
The reverse sync strips the timestamp column before returning a row to Task Tracker, keeping the two tabs’ column structures aligned

4. Script Reference

The full Apps Script source currently deployed on the spreadsheet.

4.1 syncRows() — core sync function

function syncRows() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const taskSheet = ss.getSheetByName(“Task Tracker”);

if (!taskSheet) return;

let doneSheet = ss.getSheetByName(“Done”);

if (!doneSheet) doneSheet = ss.insertSheet(“Done”);

const DONE_VALUES = [“done”, “completed”, “complete”];

// Part 1: Task Tracker -> Done (status = done)

// Part 2: Done -> Task Tracker (status != done)

// Status column detected dynamically from header row

// Timestamp column “Moved to Done On” auto-created in Done

// … (full logic moves matching rows, batches writes,

// stamps completion time, and rewrites both sheets)

}

Note: the complete, unabridged script is maintained in the Apps Script editor attached to the spreadsheet (Extensions → Apps Script). The excerpt above summarizes structure; the deployed version contains full read/write logic for both directions.

4.2 setupTimer() — one-time installer

function setupTimer() {

ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));

ScriptApp.newTrigger(“syncRows”)

.timeBased()

.everyMinutes(1)

.create();

}

Run once to (re)install a clean trigger. It first deletes any existing triggers to prevent duplicates, then creates exactly one minute-based trigger.

5. Development History & Issues Resolved

The automation went through several iterations before reaching its current stable form. This history is kept for future troubleshooting reference.

Issue	Cause	Resolution
Row never moved	Destination sheet name mismatch (script looked for “Done”, tab was renamed “Completed”)	Sheet name reverted to “Done” to match script
Duplicate/incorrect trigger	Helper function setupTrigger was itself accidentally registered as an onEdit trigger	All triggers wiped and a single correct trigger reinstalled
LockService error	Used LockService.getSpreadsheetLock(), which is not a valid method	Corrected to LockService.getScriptLock() (later removed entirely in the timer-based redesign)
Exceeded maximum execution time	onEdit-based version deleted the row it just edited, which re-fired the same onEdit trigger, causing a loop until Google’s 6-minute timeout	Redesigned around a time-based trigger that scans and batch-processes rows on a fixed schedule instead of reacting to individual edits
Slow / timing out copyTo calls	Multiple sequential copyTo() format/value/validation calls per row are expensive	Switched to bulk getValues() / setValues() array operations, which run in a fraction of the time

5.1 Trade-offs of the current design

Moving from an instant onEdit trigger to a 60-second polling trigger was a deliberate trade-off:

Gain: eliminates infinite-loop/timeout failures entirely; script runs in well under a second per cycle
Trade-off: a status change is not reflected in the other tab instantly — there can be up to a 60-second delay
This delay is acceptable for a task tracker, where near-real-time (not millisecond) sync is sufficient

6. Operating Instructions

6.1 Marking a task complete

Open the Task Tracker tab
Set the Status cell for that task to Done, Completed, or Complete (any letter case)
Within 60 seconds the row disappears from Task Tracker and appears in Done with a completion timestamp

6.2 Reopening a completed task

Open the Done tab
Change that row’s Status to anything other than Done/Completed/Complete (for example, In Progress)
Within 60 seconds the row moves back to Task Tracker, with the timestamp column dropped

6.3 Adding new “done” keywords

To recognize additional completion words (for example Closed or Resolved), edit the DONE_VALUES list in the script:

const DONE_VALUES = [“done”, “completed”, “complete”, “closed”, “resolved”];

7. Troubleshooting Guide

Symptom	Check / Fix
Rows not moving at all	Apps Script → clock icon → confirm exactly one syncRows trigger exists, type “Time-driven”, every 1 minute. If missing, run setupTimer() once.
“Exceeded maximum execution time”	Indicates the old onEdit-based version is still active somewhere. Confirm only the time-based syncRows trigger exists and the onEdit-based function has been fully removed.
Status column not detected	Header row in Task Tracker/Done must contain a cell with the exact text “Status” (case-insensitive, but spelling must match).
Row moved but no timestamp	Check that the “Moved to Done On” header exists in Done row 1; the script recreates it automatically if missing, but a manually renamed column will not be recognized.
Need to see what happened on the last run	Apps Script editor → Executions (left sidebar) → select the latest syncRows run to view its log output and any errors.

8. Permissions & Dependencies

Google account authorization to view and manage the spreadsheet (granted once during trigger setup)
No external services or APIs are used — the script runs entirely within Google Apps Script
No add-ons or third-party libraries are required

9. Recommendations for Future Improvement

Consider reducing the polling interval (for example to every 30 seconds via triggers, or to onEdit with a redesigned guard) if near-instant sync becomes a requirement
Add a status-change log sheet if an audit trail of every transition (not just the final completion timestamp) is needed
Add data validation (dropdown) on the Status column in both tabs to reduce free-text typos that fall outside the recognized done values
Consider archiving older rows out of Done periodically if the sheet grows large, to keep read/write cycles fast

TASK TRACKER

Google Sheets – Task Tracker

Table of Contents

1. Executive Summary