A client wants to receive email notifications as soon as someone edits a Google Spreadsheet that is shared with a team of people. Google Docs supports the onEdit() trigger that runs whenever an edit is made to any cell of the sheet but a limitation is that the onEdit trigger cannot send emails. Nor can be used to call external API though the URLFetch service.
As a workaround, the edits were stored as a Property and another time-based trigger would periodically send the stored value by email.
/** * @OnlyCurrentDoc */ function onEdit(e) { var key = "ROWCHANGES"; var range = e.range; var date = Utilities.formatDate(new Date(), e.source.getSpreadsheetTimeZone(), "dd-MM-yy HH:MM:s"); var properties = PropertiesService.getUserProperties(); var sheet = e.source.getActiveSheet(); var data = sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).getValues()[0]; data[range.getColumn()-1] = "" + data[range.getColumn()-1] + ""; var edits = { name: sheet.getSheetName(), data: data }; var existing = JSON.parse(properties.getProperty(key)) || {}; existing[date] = edits; properties.setProperty(key, JSON.stringify(existing)); } function onEdit_Email() { var properties = PropertiesService.getUserProperties(); var json = JSON.parse(properties.getProperty("ROWCHANGES")); var html = "": for (var keys in json) { html = html + "
[" + keys + "][" + json[keys].name + "] — " + json[keys].data; } if (html !== "") { MailApp.sendEmail(email, subject, "", {htmlBody:html}); properties.deleteAllProperties(); } }