Formulas only ever show today's number. The MRR cell said 4,200 last month and 4,650 now, but nobody wrote 4,200 down. snapshotRangeToHistory(sourceRange, historySheetName) appends one row to a history tab: today's date plus the range's display values, flattened left to right. Run it from a nightly trigger. If it already ran today, the second run writes nothing, so a retry or a double trigger can't create duplicate days.
When to use
- KPI cells (MRR, open tickets, pipeline value) you want a trend line for
- Formula totals that change as data changes, where the history matters
- Anything a client will one day ask “what was it on the 3rd?” about
How to use this snippet
/**
* Append today's date + the range's display values as one row on a history tab.
* Runs at most once per day (by date in the spreadsheet's timezone).
*
* @param {Range} sourceRange e.g. ss.getRange('Dashboard!B2:E2')
* @param {string} historySheetName tab to append to (created if missing)
* @return {boolean} true if a row was written, false if today was already recorded
*/
function snapshotRangeToHistory(sourceRange, historySheetName) {
var ss = sourceRange.getSheet().getParent();
var tz = ss.getSpreadsheetTimeZone();
var today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
var hist = ss.getSheetByName(historySheetName) || ss.insertSheet(historySheetName);
var last = hist.getLastRow();
if (last > 0) {
var lastDate = hist.getRange(last, 1).getDisplayValue();
if (lastDate === today) return false; // already snapshotted today
}
var values = sourceRange.getDisplayValues();
var flat = [].concat.apply([], values);
hist.getRange(last + 1, 1).setNumberFormat('@'); // plain text, so Sheets doesn't turn it into a date
hist.getRange(last + 1, 1, 1, flat.length + 1).setValues([[today].concat(flat)]);
return true;
}
Example
function nightlySnapshot() {
var ss = SpreadsheetApp.getActive();
snapshotRangeToHistory(ss.getRange('Dashboard!B2:E2'), 'History');
}
// History tab:
// 2026-12-28 | $4,200.00 | 37 | 12 | 91%
// 2026-12-29 | $4,650.00 | 35 | 14 | 93%
Tips:
- Add a header row to the history tab once (Date, MRR, Open, ...) so charts pick up labels.
- Display values keep currency and percent formatting. If you'd rather chart raw numbers, swap
getDisplayValues()forgetValues(). - The date is stored as plain text
yyyy-MM-ddin the spreadsheet's timezone, which is what the once-a-day check compares against. - Run the trigger after the data that feeds the formulas has finished importing.
Tip: NitroGAS Co-Pilot can add the nightly trigger and the header row. Paste the range address and the names of the numbers.
Happy Coding!
