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

Follow these steps in order

1. Work on a copy of the spreadsheet

   Do your first run in the copy you made. 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

   In the copy, choose Extensions, then Apps Script. Paste the function over the starter code and save.

4. Run it and compare both tabs

   Choose archiveCompletedRows in the function dropdown above the code, then click Run. Approve access when Google asks. The Done rows are now at the bottom of the new Archive tab and gone from Tasks, and the row counts add up. The Execution log reads "Rows moved to Archive: 2."

5. Use it on the real file

   When the copy looks right, paste the same function into the real spreadsheet and run it there. Each later run 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.

Retype the word Done in column C, or change STATUS_WORD in the code to the word you use. The check ignores spaces around the word but is case sensitive, so done, DONE, and Done! do not match.

The wrong rows disappeared from Tasks.

Restore the rows first: press Ctrl+Z in the spreadsheet, or choose File, then Version history, then See version history, and restore the version from before the run. Then check that the reverse() call is still in the code. Deleting from the top down removes the wrong rows, because every delete shifts the rows below it up by one.

The run stops with "Add a Sheet tab named Tasks before running this function."

Rename your tab to Tasks, or change SOURCE_TAB in the code to the name of your tab.

The Archive tab has data but no header.

Insert a new row 1 in Archive and copy the header from Tasks into it. The archived rows stay in place below it. The script copies the header only when the Archive tab is completely empty.