How to record delete events in Google Spreadsheet

Question

I need to record the delete events in my Google Spreadsheet using Google Apps Script (delete a cell or entire row) and get the cell number and user name.

Function onEdit(e) triggers when cells are edited and not when deleted.

function onEdit(e)
{

var range = e.range;
Logger.log(range.getRow());
Logger.log(Session.getActiveUser().getEmail());
  if (range.getRow() === 3.0) 
  {
    var recipient = "user@example.com";
    var subject = 'Roster Notification';
    MailApp.sendEmail(recipient, subject, "Hello, your roster has been updated. Please check. Thanks."); 
  }
}

`onEdit` does trigger on cell deletion(see duplicate). But not on `REMOVE_ROW`, in which case `onChange`+ `getActiveRange()` can be used. — TheMaster, Sep 11 '20 at 04:44
Related: https://stackoverflow.com/questions/60926676/gas-on-edit-trigger-not-running-when-edited-from-api — TheMaster, Sep 11 '20 at 09:01

score 3 · Answer 1 · edited Sep 11 '20 at 04:42

Update: Issue#2 linked below is fixed and you can use getActiveRange() to retrieve the correct row.

Good news & Bad news, I'm afraid.

You can use an installable Change trigger to get notified about row deletions. (The full list of changes is EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT, or OTHER).

The bad news is that the event does not tell you what changed, so if you wish to track changes to a roster, you'll need to do that by keeping track of changes yourself.

Related issues from Google's issue tracker:

Issue#2 marked as fixed and `getActiveRange()` does return the correct row. — TheMaster, Sep 11 '20 at 04:41

How to record delete events in Google Spreadsheet

1 Answers1

Linked