Google App Script: Send hourly email with spreadsheet changes, if any

I got a request to come up with a script that sends an email whenever changes are done to a specific Google spreadsheet. Since repeated edits can be done, emails should only be sent once an hour, and should contain the changes.

Use case: Team A maintains a spreadsheet, such as a product list. Team B would like to get notified if team A made changes to the spreadsheet.

This here will send an email every time a change is done:

function onEdit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = e.source.getActiveSheet();
  const cellChange = {
    name:  ss.getName(),
    url:   ss.getUrl(),
    sheet: sheet.getName(),
    cell:  e.range.getA1Notation(),
    from:  e.oldValue || '(multiple changes)',
    to:    e.value || '(multiple changes)'
  };
  const subject = `Spreadsheet ${cellChange.name} has been updated`;
  const body = `- Sheet: ${cellChange.sheet}
- Cell: ${cellChange.cell}
- From: ${cellChange.from}
- To:   ${cellChange.to}
- URL:  ${cellChange.url}
`;
  const email = {
    to:      'jimmy.neutron@example.com',
    subject: subject,
    body:    body
  }
  console.log('send email:', email);
  MailApp.sendEmail(email);
}

This is too noisy. I'll post an answer with the solution I came up with.

The onEdit() event fires off on each cell change, so we need to first collect and store recent changes, then periodically send an email with the changes.

It is possible to store changes using script properties (getScriptProperties()) or cache service (CacheService.getScriptCache()). The latter is better suited for this use case because it expires automatically, and because it has a larger quota size.

Here is the Google Apps Script code I came up with. It can be configured:

• exclude: exclude sheets from notifications
• idle: idle time in minutes before sending email
• email: email message template, see https://developers.google.com/apps-script/reference/mail/mail-app#sendemailmessage

/* Send email notification of changes setup:
* 1. Duplicate below code to the script of any spreadsheet
* 2. In Editor, tweak notifyChangesConf below as desired
* 3. In Editor on top next to Debug, select function notifyChangesSendEmail, and run it once
*    - Accept the permission request to run the script
* 4. In Triggers, install a time-driven trigger:
*    - Function:         notifyChangesSendEmail
*    - Event source:     Time-driven
*    - Type:             Minutes timer
*    - Minute interval:  Every 5 minutes
* 5. In Triggers, install an on-edit trigger:
*    - Function:         notifyChangesOnEdit
*    - Event source:     From spreadsheet
*    - Event type:       On edit
*/
const notifyChangesConf = {
exclude:    [ 'Scratch', 'Sheet8' ], // exclude sheets from notifications
idle:       60, // idle time in minutes before sending email
email: {    // see https://developers.google.com/apps-script/reference/mail/mail-app#sendemailmessage
to:       'jimmy.neutron@example.com',  // comma list
subject:  'Spreadsheet Update: %NAME% > %SHEET%',
body:
`Spreadsheet %NAME% has been modified:
%REPEAT%
• %SHEET%!%CELL%: %TO%%REPEAT%
• URL: %URL%
`,
noReply:  true
}
};
function notifyChangesSendEmail(e) {
function expand_(str) {
const parts = str.split(/%REPEAT%/);
if(parts[1]) {
parts[1] = changes.map(obj => {
return parts[1]
.replace(/%SHEET%/g,  obj.sheet)
.replace(/%CELL%/g,   obj.cell)
.replace(/%FROM%/g,   obj.from || '(multiple cell changes)')
.replace(/%TO%/g,     obj.to || '(multiple cell changes)');
}).join('')
}
return parts.join('')
.replace(/%NAME%/g,   ss.getName())
.replace(/%URL%/g,    ss.getUrl())
.replace(/%SHEET%/g,  changes[0].sheet)
.replace(/%CELL%/g,   changes[0].cell)
.replace(/%FROM%/g,   changes[0].from || '(multiple cell changes)')
.replace(/%TO%/g,     changes[0].too || '(multiple cell changes)');
}
const ss = SpreadsheetApp.getActiveSpreadsheet();
let changes = notifyChangesCacheRead();
if(changes.length) {
const lastUpdate = changes[changes.length - 1].time;
const now = new Date().valueOf();
const diff = Math.round(notifyChangesConf.idle - (now - lastUpdate) / (1000 * 60));
if(diff <= 0) {
notifyChangesCacheDelete();
const email = JSON.parse(JSON.stringify(notifyChangesConf.email));
email.subject = expand_(email.subject);
email.body = expand_(email.body);
console.log('notifyChangesSendEmail data:', email);
MailApp.sendEmail(email);
} else {
console.log('notifyChangesSendEmail: ' + changes.length + ' changes exist,' +
diff + ' min remaining within ' + notifyChangesConf.idle + ' min idle time');
}
} else {
console.log('notifyChangesSendEmail: no changes, no action');
}
}
function notifyChangesOnEdit(e) {
const sheet = e.source.getActiveSheet();
const sheetName = sheet.getName();
if(notifyChangesConf.exclude.indexOf(sheetName) >= 0) {
console.log('notifyChangesOnEdit: no action, ' + sheetName + ' is in exclude list');
} else {
let changes = notifyChangesCacheRead();
let now = new Date().valueOf();
const obj = {
sheet:  sheetName,
cell:   e.range.getA1Notation(),
from:   e.oldValue,
to:     e.value,
time:   now
};
console.log('notifyChangesOnEdit change:', obj);
changes.push(obj);
notifyChangesCacheSave(changes);
}
}
function notifyChangesCacheRead() {
let changes = [];
let json = CacheService.getScriptCache().get('edit-changes') || '[]';
try {
changes = JSON.parse(json);
} catch(err) {
console.log('error parse cached changes:',err);
}
//console.log('read cache', changes);
return changes;
}
function notifyChangesCacheSave(changes) {
//console.log('save cache', changes);
CacheService.getScriptCache().put('edit-changes', JSON.stringify(changes), notifyChangesConf.idle * 60 * 2);
}
function notifyChangesCacheDelete() {
//console.log('delete cache');
CacheService.getScriptCache().remove('edit-changes');
}
// EOF

Two triggers need to be installed -- see comment section at the beginning of the code.

Limitations:

• pasting a range of cells triggers a notification with note (multiple cell changes) instead of the actual changes
• no change notification is sent if a sheet is changed programmatically by a script

Sample notification email received with above settings:

From: <jimmy.neutron@example.com>
Date: Wed, Aug 9, 2023 at 5:04 PM
Subject: Spreadsheet Update: My sandbox > Sheet10
To: <jimmy.neutron@example.com>
Spreadsheet My sandbox has been modified:
• Sheet10!C6: foo 1
• Sheet10!C7: moo moo 2
• Sheet10!B2: blah blah 3
• Sheet10!B3: foo 4
• Sheet10!B4: (multiple cell changes)
• Sheet10!C5: moo 6
• Sheet10!C6: foo 7
• URL:  https://...

Try this:

function onMyEdit(e) {
//Logger.log(JSON.stringify(e));
//e.source.toast('Entry')
const sh = e.range.getSheet();
const excl = ['Sheet2', 'Sheet1'];//excluded sheets
const idx = excl.indexOf(sh.getName());
if (!~idx) {
//e.source.toast("Gate1");
let vs = sh.getRange(e.range.rowStart, e.range.columnStart, e.range.rowEnd - e.range.rowStart + 1, e.range.columnEnd - e.range.columnStart + 1).getDisplayValues();
let chg = { name: e.source.getName(), url: e.source.getUrl(), sheet: sh.getName(), row: e.range.rowStart, col: e.range.columnStart, from: e.oldValue, to: e.value, rgA1: e.range.getA1Notation(), w: e.range.columnEnd - e.range.columnStart + 1, h: e.range.columnEnd - e.range.columnStart + 1, values: [] }
vs.forEach((r, i) => {
let row = [];
r.forEach((c, j) => {
row.push(c)
})
chg.values.push(row);
});
const subject = `Spreadsheet ${chg.name} has been updated`;
const body = `- Sheet: ${chg.sheet}
- Range: ${chg.rgA1}
- UL(row,col): ${chg.row},${chg.col}
- Width: ${chg.w}
- Length: ${chg.h}
- From: ${e.oldValue ? chg.from : ''}
- To:   ${e.value ? chg.to : ''}
- url:  ${chg.url}
- values: ${JSON.stringify(chg.values)}
`;
const email = {
to: gobj.globals.emailpriv,
subject: subject,
body: body
}
//console.log('send email:', email);
//MailApp.sendEmail(email);
GmailApp.createDraft(gobj.globals.emailpriv,subject,body);
}
}

Call this function once per hour

function sendAllDrafts() {
const drafts = GmailApp.getDrafts();
drafts.forEach(d => d.send());
}

Here's away to store all of the draft ids in an array in case you have other drafts that you don't want to lose.

function onMyEdit(e) {
//Logger.log(JSON.stringify(e));
//e.source.toast('Entry')
const sh = e.range.getSheet();
const excl = ['Sheet2', 'Sheet1'];//excluded sheets
const idx = excl.indexOf(sh.getName());
if (!~idx) {
//e.source.toast("Gate1");
let vs = sh.getRange(e.range.rowStart, e.range.columnStart, e.range.rowEnd - e.range.rowStart + 1, e.range.columnEnd - e.range.columnStart + 1).getDisplayValues();
let chg = { name: e.source.getName(), url: e.source.getUrl(), sheet: sh.getName(), row: e.range.rowStart, col: e.range.columnStart, from: e.oldValue, to: e.value, rgA1: e.range.getA1Notation(), w: e.range.columnEnd - e.range.columnStart + 1, h: e.range.columnEnd - e.range.columnStart + 1, values: [] }
vs.forEach((r, i) => {
let row = [];
r.forEach((c, j) => {
row.push(c)
})
chg.values.push(row);
});
const subject = `Spreadsheet ${chg.name} has been updated`;
const body = `- Sheet: ${chg.sheet}
- Range: ${chg.rgA1}
- UL(row,col): ${chg.row},${chg.col}
- Width: ${chg.w}
- Length: ${chg.h}
- From: ${e.oldValue ? chg.from : ''}
- To:   ${e.value ? chg.to : ''}
- url:  ${chg.url}
- values: ${JSON.stringify(chg.values)}
`;
const email = {
to: gobj.globals.emailpriv,
subject: subject,
body: body
}
//console.log('send email:', email);
//MailApp.sendEmail(email);
let d = GmailApp.createDraft(gobj.globals.emailpriv, subject, body);
let ps = PropertiesService.getScriptProperties();
let idA = JSON.parse(ps.getProperty('drafts')) || [];
idA.push(d.getId());
ps.setProperty('draft',JSON.stringify(idA));
}
}
function sendAllDrafts() {
const drafts = GmailApp.getDrafts();
let ps = PropertiesService.getScriptProperties();
let idA = JSON.parse(ps.getProperty(drafts));
drafts.forEach(d => {
let idx = idA.indexOf(d.getId());
if(`idx)`){
d.send();
}
});
ps.setProperty('drafts','[]');
}

Note: I haven't debugged this last one at all.