“What changed since yesterday?” is a question every sheet eventually gets asked. diffObjectsByKey(before, after, key) compares two arrays of row objects by an ID field and returns { added, removed, changed }. Each changed entry lists only the fields that differ, as { col: [old, new] }. Pair it with rowsToObjects to turn sheet ranges into objects first.
When to use
- Daily “what changed” digests against a snapshot tab
- Checking an import before you overwrite a sheet
- Audit logs of edits made outside your script
How to use this snippet
/**
* Compare two arrays of objects by a key field.
*
* @param {Object[]} before
* @param {Object[]} after
* @param {string} key field that identifies a row (e.g. 'Id')
* @return {{added: Object[], removed: Object[], changed: {key: *, fields: Object}[]}}
*/
function diffObjectsByKey(before, after, key) {
function index(list) {
var m = {};
(list || []).forEach(function (o) { m[String(o[key])] = o; });
return m;
}
function norm(v) { return v instanceof Date ? v.toISOString() : String(v == null ? '' : v); }
var a = index(before), b = index(after);
var out = { added: [], removed: [], changed: [] };
Object.keys(b).forEach(function (k) {
if (!(k in a)) { out.added.push(b[k]); return; }
var fields = {};
var cols = Object.keys(a[k]).concat(Object.keys(b[k]));
cols.forEach(function (c) {
if (c in fields) return;
if (norm(a[k][c]) !== norm(b[k][c])) fields[c] = [a[k][c], b[k][c]];
});
if (Object.keys(fields).length) out.changed.push({ key: b[k][key], fields: fields });
});
Object.keys(a).forEach(function (k) { if (!(k in b)) out.removed.push(a[k]); });
return out;
}
Example
function whatChanged() {
var ss = SpreadsheetApp.getActive();
var before = rowsToObjects(ss.getSheetByName('_Snapshot').getDataRange().getValues());
var after = rowsToObjects(ss.getSheetByName('Clients').getDataRange().getValues());
var d = diffObjectsByKey(before, after, 'Id');
console.log(d.added.length + ' added, ' + d.removed.length + ' removed, ' + d.changed.length + ' changed');
d.changed.forEach(function (c) {
Object.keys(c.fields).forEach(function (f) {
console.log(c.key + ' ' + f + ': ' + c.fields[f][0] + ' → ' + c.fields[f][1]);
});
});
}
Tips:
- Values are compared as strings (dates as ISO), so
5and'5'count as equal. That's usually what you want from a sheet. - Duplicate keys: the last row wins. Run a duplicate check first if your IDs aren't trustworthy.
- Blank-key rows all collapse into one. Filter them out before diffing.
Tip: NitroGAS Co-Pilot can build the snapshot-and-diff job around this. Paste your sheet's header row and say which column is the ID.
Happy Coding!
