Get Email Notifications for Edits in Google Spreadsheet

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();
    
  }

}