The orders sheet has 40,000 rows and every script that reads it is slow. archiveRowsBeforeDate(sheet, dateHeader, cutoff, opts) moves every row dated before the cutoff into monthly Archive_YYYY-MM tabs (by that row's month, in the spreadsheet's timezone), creating tabs with the same header as needed. It's batch-safe: it reads once, writes each month in one setValues, rewrites the kept rows in one go, and only clears the leftover rows after every archive write has succeeded.
When to use
- Monthly clean-ups of order, log or event sheets
- Before a big sheet starts hitting the 10-million-cell limit
- Keeping “current” tabs fast while old data stays searchable
How to use this snippet
/**
* Move rows dated before `cutoff` into Archive_YYYY-MM tabs.
*
* @param {Sheet} sheet
* @param {string} dateHeader header of the date column
* @param {Date} cutoff rows with a date < cutoff are archived
* @param {Object=} opts { prefix: 'Archive_', dryRun: false }
* @return {{moved: number, kept: number, tabs: Object<string, number>}}
*/
function archiveRowsBeforeDate(sheet, dateHeader, cutoff, opts) {
opts = opts || {};
var prefix = opts.prefix || 'Archive_';
var ss = sheet.getParent();
var tz = ss.getSpreadsheetTimeZone();
var lock = LockService.getDocumentLock();
if (!lock.tryLock(30000)) throw new Error('archiveRowsBeforeDate: sheet is busy, try again');
try {
var data = sheet.getDataRange().getValues();
var header = data.shift();
var col = header.indexOf(dateHeader);
if (col === -1) throw new Error('archiveRowsBeforeDate: no column "' + dateHeader + '"');
var keep = [], byMonth = {};
data.forEach(function (r) {
var d = r[col];
if (d instanceof Date && d < cutoff) {
var tab = prefix + Utilities.formatDate(d, tz, 'yyyy-MM');
(byMonth[tab] = byMonth[tab] || []).push(r);
} else {
keep.push(r);
}
});
var tabs = {};
Object.keys(byMonth).forEach(function (t) { tabs[t] = byMonth[t].length; });
var result = { moved: data.length - keep.length, kept: keep.length, tabs: tabs };
if (opts.dryRun || !result.moved) return result;
// 1. Append to archive tabs first
Object.keys(byMonth).sort().forEach(function (t) {
var rows = byMonth[t];
var arch = ss.getSheetByName(t);
if (!arch) {
arch = ss.insertSheet(t);
arch.getRange(1, 1, 1, header.length).setValues([header]);
}
arch.getRange(arch.getLastRow() + 1, 1, rows.length, header.length).setValues(rows);
});
SpreadsheetApp.flush();
// 2. Then rewrite the source: kept rows up top, clear the leftovers
if (keep.length) sheet.getRange(2, 1, keep.length, header.length).setValues(keep);
sheet.getRange(2 + keep.length, 1, result.moved, header.length).clearContent();
return result;
} finally {
lock.releaseLock();
}
}
Example
function monthlyArchive() {
var sh = SpreadsheetApp.getActive().getSheetByName('Orders');
var cutoff = new Date();
cutoff.setMonth(cutoff.getMonth() - 3, 1); // keep the current month plus the 3 before it
cutoff.setHours(0, 0, 0, 0);
var res = archiveRowsBeforeDate(sh, 'Order Date', cutoff, { dryRun: true });
console.log(JSON.stringify(res)); // check first, then run with dryRun: false
}
Tips:
- Run with
dryRun: truefirst and check the counts per tab. - Rows with a blank or text date are kept, not archived. Fix them, then run again.
- Source rows are rewritten as values, so formulas in the source tab are replaced by their results. Keep formulas in a separate column range or on another tab.
- More than a few hundred thousand cells? Archive one month at a time so the run stays under 6 minutes.
Tip: NitroGAS Co-Pilot can wrap this in a monthly trigger with a dry-run switch. Paste your sheet's header row and say which column holds the date.
Happy Coding!
