“Rows from the last 7 days” sounds trivial until a row stamped at 11:30pm in New York shows up as tomorrow because something compared against UTC midnight. filterObjectsByDateWindow(rows, field, opts) filters row objects by a date field using calendar dates in the spreadsheet timezone — either an explicit start / end or lastNDays — so the window matches what people see in the sheet.
When to use
- Weekly digests (“everything created in the last 7 days”)
- Exports for a date range someone typed into a Config tab
- Nightly jobs that only touch today’s (or yesterday’s) rows
How to use this snippet
/**
* Keep row objects whose `field` falls inside a calendar-date window,
* evaluated in the spreadsheet timezone (fallback: script timezone).
* Both ends are inclusive. Blank / invalid dates are dropped.
*
* @param {Object[]} rows
* @param {string} field e.g. 'Created'
* @param {{start?: Date|string, end?: Date|string, lastNDays?: number,
* now?: Date, timeZone?: string,
* spreadsheet?: GoogleAppsScript.Spreadsheet.Spreadsheet}=} opts
* start / end Date or 'yyyy-MM-dd' (either may be omitted = open-ended)
* lastNDays overrides start/end: 1 = today only, 7 = today + previous 6 days
* @return {Object[]}
*/
function filterObjectsByDateWindow(rows, field, opts) {
opts = opts || {};
var ss = opts.spreadsheet || SpreadsheetApp.getActive();
var tz = opts.timeZone
|| (ss && ss.getSpreadsheetTimeZone())
|| Session.getScriptTimeZone();
// A Date (or 'yyyy-MM-dd…' string) → 'yyyy-MM-dd' in the sheet's timezone.
// Comparing these strings compares calendar days, not instants.
function dayKey(v) {
if (v == null || v === '') return null;
if (typeof v === 'string' && /^\d{4}-\d{2}-\d{2}/.test(v)) return v.slice(0, 10);
var d = v instanceof Date ? v : new Date(v);
return isNaN(d.getTime()) ? null : Utilities.formatDate(d, tz, 'yyyy-MM-dd');
}
// Calendar arithmetic on a 'yyyy-MM-dd' key (UTC is fine here: no times involved).
function addDays(key, n) {
var p = key.split('-').map(Number);
var d = new Date(Date.UTC(p[0], p[1] - 1, p[2] + n));
return d.toISOString().slice(0, 10);
}
var start = dayKey(opts.start);
var end = dayKey(opts.end);
if (opts.lastNDays) {
var today = dayKey(opts.now || new Date());
start = addDays(today, -(opts.lastNDays - 1));
end = today;
}
return (rows || []).filter(function (row) {
var k = dayKey(row[field]);
if (!k) return false;
return (!start || k >= start) && (!end || k <= end);
});
}
Example
function weeklyDigestRows_() {
var rows = readSheetData_('Orders'); // [{ Created: Date, Client, Amount }, ...]
return filterObjectsByDateWindow(rows, 'Created', { lastNDays: 7 });
}
function exportRangeFromConfig_() {
var cfg = SpreadsheetApp.getActive().getSheetByName('Config');
var start = cfg.getRange('B2').getValue(); // Date cell
var end = cfg.getRange('B3').getValue(); // Date cell
return filterObjectsByDateWindow(readSheetData_('Orders'), 'Created', { start: start, end: end });
}
Tips:
- Date cells come back as
Dateobjects pinned to the spreadsheet’s timezone — formatting them in that same zone gives back the day people see. - Plain
'yyyy-MM-dd'text is used as-is (no timezone conversion). Other strings go throughnew Date(…), which is fragile; store real dates or ISO text. lastNDays: 1means “today only”;lastNDays: 7is today plus the previous six days.- Standalone script? Pass
{ spreadsheet: SpreadsheetApp.openById(id) }or{ timeZone: 'America/New_York' }. - Pairs with formatInSpreadsheetTimezone and isWithinBusinessHours.
Tip: NitroGAS Co-Pilot can swap a hand-rolled getTime() comparison for filterObjectsByDateWindow if you paste the loop and say which timezone the sheet uses.
Happy Coding!
