Auto-archive completed rows to a Done sheet

Northwind tracks work in a Projects sheet, and over time it fills up with finished tasks. The completed rows are not useless — there is value in keeping them — but they clutter the active view, make filters slower, and bury the handful of things still in flight.

This script keeps the working view clean. On a schedule it scans the Projects sheet, moves every row marked done into a Done archive tab, and rewrites Projects with only the rows still open. Nothing is deleted — the history lives on the Done tab — but the active sheet stays focused on current work.

What you’ll need

• A Projects sheet with a header row that includes a status column. Rows whose status is exactly done get archived.
• A Done sheet in the same spreadsheet, with the same columns in the same order, so the moved rows line up.
• The ID of the spreadsheet, taken from its URL.

The script

// The spreadsheet that holds both the Projects and Done tabs.
const PROJECTS_SHEET_ID = '1abcProjectsSheetId';

// Tab names and the status value that marks a row as finished.
const ACTIVE_TAB = 'Projects';
const ARCHIVE_TAB = 'Done';
const DONE_STATUS = 'done';

/**
 * Moves every completed row out of the Projects tab and into the Done
 * tab, then rewrites Projects with only the open rows.
 */
function archiveDoneRows() {
  const ss = SpreadsheetApp.openById(PROJECTS_SHEET_ID);
  const active = ss.getSheetByName(ACTIVE_TAB);
  const done = ss.getSheetByName(ARCHIVE_TAB);

  // 1. Read the Projects tab and split off the header row.
  const [header, ...rows] = active.getDataRange().getValues();
  const statusCol = header.indexOf('status');

  // 2. Bail out if the sheet has no status column — nothing to sort on.
  if (statusCol === -1) {
    Logger.log('No "status" column found — nothing to do.');
    return;
  }

  // 3. Sort each row into keep (still open) or move (finished).
  const keep = [header];
  const move = [];
  rows.forEach((r) => {
    (r[statusCol] === DONE_STATUS ? move : keep).push(r);
  });

  // 4. Nothing finished since last run — leave both tabs untouched.
  if (move.length === 0) {
    Logger.log('No completed rows to archive.');
    return;
  }

  // 5. Append the finished rows to the bottom of the Done tab.
  done.getRange(done.getLastRow() + 1, 1, move.length, header.length)
    .setValues(move);

  // 6. Rewrite the Projects tab with just the header and open rows.
  active.clear();
  active.getRange(1, 1, keep.length, header.length).setValues(keep);
  Logger.log('Archived ' + move.length + ' completed row(s).');
}

How it works

1. archiveDoneRows opens the spreadsheet and gets handles to both the Projects and Done tabs.
2. It reads the whole Projects tab, peels off the header row, and finds the index of the status column so the rest of the code can check it by number.
3. If there is no status column it logs a message and stops — without it the script has nothing to sort on.
4. It walks every data row and pushes it onto one of two arrays: move if the status is exactly done, keep otherwise. The keep array starts with the header so it is ready to write back.
5. If nothing was finished, it stops here, leaving both tabs untouched.
6. Otherwise it appends the move rows to the bottom of Done, clears Projects, and writes the header plus open rows back. The active view now holds only current work.

Example run

Before a run, the Projects tab looks like this:

Task	Owner	status
Draft Q2 proposal	Awadesh	done
Build landing page	Priya	in progress
Review analytics	Awadesh	done
Client onboarding	Priya	todo

After archiveDoneRows runs, Projects keeps only the two open rows:

Task	Owner	status
Build landing page	Priya	in progress
Client onboarding	Priya	todo

And the two done rows are appended to the bottom of the Done tab.

Trigger it

Run this once a day so finished work clears out overnight:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose archiveDoneRows, event source Time-driven, type Day timer, set the hour to a quiet time such as 2am to 3am.
4. Save, and approve the spreadsheet authorisation prompt on the first run.

Watch out for

• The status match is exact and case-sensitive. Done, DONE, or done with a trailing space will not be archived. Use a dropdown (data validation) on the status column to keep values consistent.
• Rewriting Projects clears any formulas, conditional formatting, or filters on the data rows. Keep formulas in a separate tab that references Projects, not inside it.
• clear() removes cell contents but leaves the sheet structure. If you have notes or merged cells in the data range, they will be lost.
• The Done and Projects tabs must have identical columns in the same order. Add a column to one and you must add it to the other, or the archived rows shift out of alignment.
• If two runs overlap — for example a manual run during the scheduled one — rows could be written twice. Keep to a single trigger and avoid running it by hand at the same time.