Auto-snapshot a sheet to a dated archive tab

Northwind’s Pipeline sheet is full of live formulas — running totals, lookups against this month’s deals, percentages that recalculate the moment a number changes. That is exactly what you want day to day, but it means the sheet has no memory. Ask it what the pipeline looked like at the end of March and there is no answer: the formulas have long since moved on.

This script gives the sheet a memory. On the last day of each month it copies every cell on the source tab as plain values — formulas resolved, frozen in place — into a new tab named Archive 2026-05. The archive never recalculates, so each month-end stays exactly as it was, and the tab is hidden to keep the spreadsheet tidy.

What you’ll need

• The Northwind Pipeline spreadsheet, with the data you want to freeze on its first tab.
• Edit access to that spreadsheet from the account running the script.
• Nothing else — the script creates each dated archive tab itself.

The script

// The spreadsheet whose first tab gets snapshotted each month.
const SOURCE_SHEET_ID = '1abcPipelineSheetId';

// Prefix for every archive tab — the run date's year-month is appended.
const ARCHIVE_PREFIX = 'Archive ';

/**
 * Copies the source tab's current values into a new, hidden, dated
 * archive tab. Safe to run more than once: it skips the month if an
 * archive for it already exists.
 */
function snapshotMonth() {
  const ss = SpreadsheetApp.openById(SOURCE_SHEET_ID);
  const source = ss.getSheets()[0];

  // 1. Build the tab name from the current year and month, e.g. "Archive 2026-05".
  const tabName =
    ARCHIVE_PREFIX +
    Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM');

  // 2. Bail out if this month has already been archived — no duplicates.
  if (ss.getSheetByName(tabName)) {
    Logger.log('Archive "' + tabName + '" already exists — skipping.');
    return;
  }

  // 3. Read every cell from the source tab as resolved values.
  const values = source.getDataRange().getValues();
  if (!values.length || !values[0].length) {
    Logger.log('Source tab is empty — nothing to snapshot.');
    return;
  }

  // 4. Create the archive tab and write the frozen values into it.
  const target = ss.insertSheet(tabName);
  target
    .getRange(1, 1, values.length, values[0].length)
    .setValues(values);

  // 5. Hide the archive so it does not clutter the tab bar.
  target.hideSheet();
  Logger.log('Snapshotted ' + values.length + ' rows to "' + tabName + '".');
}

How it works

1. snapshotMonth opens the source spreadsheet and takes its first tab as the sheet to freeze.
2. It builds the archive tab name from today’s date — Utilities.formatDate with the pattern yyyy-MM gives Archive 2026-05 for any day in May 2026.
3. It checks whether a tab with that name already exists. If it does, the month has been snapshotted already, so the script logs a message and stops — this makes the function safe to re-run.
4. It reads the source tab with getDataRange().getValues(). Because getValues returns the result of each cell, not the formula, the snapshot is automatically a frozen copy. A short guard skips the run if the source is empty.
5. It inserts a new tab, writes the values in one block, and calls hideSheet so the archive does not crowd the tab bar — the data is there when you need it, out of the way when you do not.

Example run

The Pipeline tab on the last day of May contains live formulas:

Deal	Stage	Value	Weighted
Acme rebrand	Proposal	12,000	=C2*0.4
Beta launch	Won	8,500	=C3*1.0

After the run, a hidden tab Archive 2026-05 holds the same grid with the formulas resolved to fixed numbers:

Deal	Stage	Value	Weighted
Acme rebrand	Proposal	12,000	4,800
Beta launch	Won	8,500	8,500

Change a deal in June and the archive does not move — it is a permanent record of where the pipeline stood at the end of May.

Trigger it

Run this automatically at month-end:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose snapshotMonth, event source Time-driven, type Month timer.
4. Set the day to the last day of month (31 — Apps Script clamps it to the real last day for shorter months) and the time to 11pm to midnight.
5. Save and approve the authorisation prompt.

Running late in the evening means the snapshot captures a full day’s activity.

Watch out for

• The archive copies values only — charts, conditional formatting, column widths and merged cells are not carried over. If you need a fully formatted copy, duplicate the tab with source.copyTo(ss) instead and then convert its formulas to values.
• A spreadsheet has a hard ceiling of around 10 million cells across all tabs. Twelve archives a year of a large sheet will add up — prune or move old archives to a separate file annually.
• The dated tab name is built from the run date. If the trigger fires a few minutes after midnight on the 1st, the archive will be named for the new month. Scheduling at 11pm on the last day keeps the label correct.
• Hidden tabs still count toward the spreadsheet’s size and can still be unhidden by anyone with edit access — this is tidy-up, not protection. Use sheet protection if the archive must not be altered.