Move finished rows to an Archive tab

1 · Prepare

Put these pieces in place

• Make a copy of your spreadsheet to test with. Choose File, then Make a copy.
• In the copy, create a tab named Tasks with headers in row 1 and the status in column C.
• Mark one or two test rows as Done. You do not need to create the Archive tab yourself.

2 · Review before running

What the script can access and change

3 · Copy

Paste this into the Apps Script editor

Create or open a script file, replace the example values marked above, then save the project.

const SOURCE_TAB = 'Tasks'; // This must match the tab that rows are moved out of.
const ARCHIVE_TAB = 'Archive'; // The script creates this tab with the same header row when it does not exist yet.
const STATUS_WORD = 'Done'; // Only rows whose status cell holds exactly this word are moved. The check is case sensitive.
const STATUS_COLUMN_INDEX = 2; // Column C is index 2. Counting starts at 0, so column A is index 0 and column B is index 1.

function archiveCompletedRows() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const source = spreadsheet.getSheetByName(SOURCE_TAB);
  if (!source) throw new Error('Add a Sheet tab named ' + SOURCE_TAB + ' before running this function.');

  const firstDataRow = 2;
  const lastRow = source.getLastRow();
  const lastColumn = source.getLastColumn();
  if (lastRow < firstDataRow) return;

  const rows = source.getRange(firstDataRow, 1, lastRow - firstDataRow + 1, lastColumn).getValues();
  /** @type {unknown[][]} */
  const rowsToMove = [];
  /** @type {number[]} */
  const rowNumbersToDelete = [];

  rows.forEach((row, index) => {
    if (String(row[STATUS_COLUMN_INDEX]).trim() !== STATUS_WORD) return;
    rowsToMove.push(row);
    rowNumbersToDelete.push(firstDataRow + index);
  });

  if (rowsToMove.length === 0) {
    Logger.log('No row had the status ' + STATUS_WORD + '. Nothing was moved.');
    return;
  }

  const archive = spreadsheet.getSheetByName(ARCHIVE_TAB) || spreadsheet.insertSheet(ARCHIVE_TAB);
  if (archive.getLastRow() === 0) {
    archive.getRange(1, 1, 1, lastColumn).setValues(source.getRange(1, 1, 1, lastColumn).getValues());
  }

  archive.getRange(archive.getLastRow() + 1, 1, rowsToMove.length, lastColumn).setValues(rowsToMove);
  SpreadsheetApp.flush();

  rowNumbersToDelete.reverse().forEach((rowNumber) => source.deleteRow(rowNumber));
  Logger.log('Rows moved to ' + ARCHIVE_TAB + ': ' + rowsToMove.length + '.');
}

4 · Run safely

Follow these steps in order

1. Work on a copy of the spreadsheet

   This script deletes rows. Choose File, then Make a copy, and do your first run in the copy. Move to the real file only when you are happy with the result.

2. Set up the test data

   In the copy, name the tab Tasks, keep the headers in row 1, and mark two rows as Done in column C. Write down how many rows the tab has so you can check the count afterwards.

3. Paste the function and save

   Open Extensions, choose Apps Script, paste the function, and save it.

4. Run it and compare both tabs

   Choose archiveCompletedRows and run it. Approve access when Google asks. The Done rows should sit at the bottom of the new Archive tab and be gone from Tasks, and the row counts should add up. The log line reads "Rows moved to Archive: 2."

5. Use it on the real file

   Once the copy behaves, paste the same function into the real spreadsheet and run it there. Running it again later moves only the rows marked Done since the last run.

5 · Confirm

How to tell it worked

The Archive tab ends with the moved rows below a matching header, the Tasks tab no longer lists them, and the execution log shows a line such as "Rows moved to Archive: 2."

If the result is different

Troubleshooting

A row that says Done stayed on the Tasks tab.

The check trims spaces but is case sensitive, so done, DONE, and Done! are not a match. Retype the word Done in column C, or change the word in the code to the one you use.

The wrong rows disappeared from Tasks.

This happens when rows are deleted from the top down, because every delete shifts the rows below it up by one. Keep the reverse() call so the deleting starts at the bottom. To recover, press Ctrl+Z in the spreadsheet, or use File, then Version history, then See version history, and restore the version from before the run.

The script says "Add a Sheet tab named Tasks before running this function."

Rename your tab to Tasks, or change the name in the code and in the editable values.

The Archive tab has data but no header.

The header is copied only when the Archive tab is completely empty. Insert a new first row in Archive, then copy the header from Tasks into it. This leaves the archived rows in place below the header.