rowsOlderThan(rows, dateField, days, opts) returns the row objects whose date field is older than N days, measured in calendar days in the spreadsheet timezone. Empty and unparseable dates are dropped.
When to use
- Flagging or archiving rows nobody has touched in a while
- Building a "stale" report from a Last Updated column
- Nightly jobs that only act on old rows
How to use this snippet
/**
* Filter row objects whose dateField is older than N calendar days
* in the spreadsheet timezone (override with opts.timezone).
*
* Boundary: by default a row matches when age > days (strictly older).
* Pass {inclusive: true} to match age >= days.
* opts.now overrides "today" (useful in tests).
*
* @param {Object[]} rows
* @param {string} dateField
* @param {number} days
* @param {{timezone?: string, now?: Date, inclusive?: boolean}} [opts]
* @return {Object[]}
*/
function rowsOlderThan(rows, dateField, days, opts) {
opts = opts || {};
var ss = SpreadsheetApp.getActiveSpreadsheet();
var tz = opts.timezone || (ss && ss.getSpreadsheetTimeZone()) || Session.getScriptTimeZone();
var now = opts.now instanceof Date ? opts.now : new Date();
var inclusive = !!opts.inclusive; // true: age >= days; false (default): age > days
var todayYmd = Utilities.formatDate(now, tz, 'yyyy-MM-dd');
var t = todayYmd.split('-');
var todayUtc = Date.UTC(+t[0], +t[1] - 1, +t[2]);
return (rows || []).filter(function (row) {
var v = row[dateField];
if (v == null || v === '') return false;
var d = v instanceof Date ? v : new Date(v);
if (isNaN(d.getTime())) return false;
var ymd = Utilities.formatDate(d, tz, 'yyyy-MM-dd');
var p = ymd.split('-');
var ageDays = Math.round((todayUtc - Date.UTC(+p[0], +p[1] - 1, +p[2])) / 86400000);
return inclusive ? ageDays >= days : ageDays > days;
});
}
Example
function flagStale() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Tasks');
var rows = rowsToObjects(sheet.getDataRange().getValues());
var stale = rowsOlderThan(rows, 'Last Touched', 30);
Logger.log(stale.length + ' rows older than 30 days');
}
Notes
- Age is counted in whole calendar days via
Utilities.formatDatein the sheet timezone, not raw millisecond differences. A row dated yesterday is 1 day old. - Default boundary is strict: on day 30 exactly, the row is not returned. Use
{inclusive: true}if "30 days or older" is what you mean. - Values must be
Dates or somethingnew Date(...)can parse. Sheets serials already arrive asDatefromgetValues().
Happy Coding!
