Archive a project folder when it's marked done

Northwind tracks its projects in a Sheet and keeps each project’s files in its own Drive folder. When a project wraps, the folder stays exactly where it was — sitting in the active workspace next to the live work. After a year the workspace is mostly finished projects, and the in-flight ones are hard to find.

This script ties the cleanup to the status the team already maintains. When a project’s row is marked done, it moves that project’s folder into an archive and writes the date back into the Sheet. The active workspace stays focused on current work, and nobody has to remember to file anything away.

What you’ll need

• A Projects sheet with these columns: name, folderId (the Drive folder ID for that project), status (set to done when a project finishes), and archivedAt (left blank — the script fills it in).
• An archive folder to move completed project folders into, and its folder ID.
• Edit access to both the projects workspace and the archive folder.

The script

// The folder completed projects are moved into.
const ARCHIVE = '1abcArchivedProjectsId';

// The sheet that tracks every project and its status.
const PROJECTS = '1abcProjectsId';

/**
 * Scans the Projects sheet for rows marked "done" that have not yet
 * been archived, moves each project's folder into the archive, and
 * stamps the row with the archive date.
 */
function archiveCompletedProjects() {
  // 1. Read the whole Projects tab.
  const sheet = SpreadsheetApp.openById(PROJECTS).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  const [h, ...rows] = values;

  if (!rows.length) {
    Logger.log('No project rows — nothing to do.');
    return;
  }

  // 2. Map header names to column indexes so we read by name.
  const col = Object.fromEntries(h.map((k, i) => [k, i]));
  const archiveFolder = DriveApp.getFolderById(ARCHIVE);
  let moved = 0;

  // 3. Check each project row.
  rows.forEach((r, i) => {
    // Skip rows that are not done, or that are already archived.
    if (r[col.status] !== 'done' || r[col.archivedAt]) return;

    // 4. Move the project's folder into the archive.
    const folder = DriveApp.getFolderById(r[col.folderId]);
    folder.moveTo(archiveFolder);

    // 5. Stamp the archive date back into the values array.
    //    i is 0-based over data rows, so the sheet row is i + 1.
    values[i + 1][col.archivedAt] = new Date();
    moved++;
  });

  // 6. Write the updated values (with new dates) back in one call.
  sheet.getDataRange().setValues(values);
  Logger.log(`Archived ${moved} project folder(s).`);
}

How it works

1. archiveCompletedProjects opens the projects spreadsheet and reads the whole first tab into a values array, keeping the header separate.
2. If there are no data rows, it stops.
3. It builds a col map of header name to index, so the rest of the script reads r[col.status] rather than guessing column order.
4. It walks every project row, skipping any that is not done or that already has an archivedAt date — those have been handled before.
5. For a qualifying row it opens the project folder by ID and calls moveTo to relocate it into the archive folder.
6. It writes the current date into the row’s archivedAt cell within the values array, then writes the whole array back to the sheet in one setValues call so the new dates are saved.

Example run

Say the Projects sheet holds:

name	folderId	status	archivedAt
Castle rebrand	1castle…	done
Brightline site	1bright…	active
Acme report	1acme…	done	2026-04-02

After a run:

• Castle rebrand — its folder moves into the archive; archivedAt is set to today.
• Brightline site — still active, untouched.
• Acme report — already has an archivedAt, so it is skipped even though its status is done.

Trigger it

Run this on a daily timer so a project is shelved soon after it is marked done:

1. In the Apps Script editor open Triggers and click Add Trigger.
2. Choose archiveCompletedProjects, set the event source to Time-driven, and pick a Day timer for overnight.

You can also run it by hand from the editor whenever you finish a batch of projects.

Watch out for

• moveTo relocates the real folder — it does not copy. Anyone with a link or shortcut to that folder still reaches it in its new home, but it is no longer in the active workspace.
• The archivedAt stamp is what stops a folder being processed twice. If you clear that cell, the next run will try to move the folder again — harmless if it is already in the archive, but it re-stamps the date.
• A wrong folderId makes getFolderById throw and halts the run before later rows are reached. Keep the IDs accurate.
• getDataRange().setValues rewrites the whole tab. If someone is editing the sheet during the run, their unsaved change to another cell could be overwritten. Run it outside working hours.
• Shared folders need edit access on both the source and the archive for the move to succeed.