0
function onEdit(e) {
  var sheetName = "Sheet1"; // Specify the name of the sheet to track changes

  var editedSheet = e.source.getActiveSheet();
  if (editedSheet.getName() !== sheetName) {
    return; // Exit the function if the edited sheet is not "Sheet1"
  }

  var range = e.range;
  var user = Session.getActiveUser().getEmail();
  var timestamp = new Date();
  var oldValue = e.oldValue;
  var newValue = e.value;
  var cellReference = range.getA1Notation();

  var logSheet = e.source.getSheetByName('ChangeLog'); // Replace 'ChangeLog' with your log sheet name
  if (!logSheet) {
    logSheet = e.source.insertSheet('ChangeLog');
    logSheet.appendRow(['Timestamp', 'User', 'Cell Reference', 'Old Value', 'New Value']);
  }

  logSheet.appendRow([timestamp, user, cellReference, oldValue, newValue]);
}

Need help turning this logger to account for ranged changes As of now it does log changes but no info is logged for OLD Value and New Value if ranged changes were made.

this works for individual cell changes. Not for ranged changes(multiple cells changed at the same time)

Hoping for improvement to log ranged changes as multiple individual log per cell reference.

Hoping to add "Data was Deleted"(red Font) if data was deleted instead of just a blank for New Value.

Please add If Cell Value is Empty and new Value is empty Ignore. as deleted empty cells are logged still.

copying a cell from Sheet1 and Pasting to sheet1 is are showing as blanks for old and new value when logged. Hoping this can be done for ranged changes as well.

This should record on a per cell change. OLD value and new value.

thanks for the help

DCC
  • 37
  • 6
  • 2
    Does this answer your question? [How to get all old values for a mulitple cell range with onEdit trigger](https://stackoverflow.com/questions/57242017/how-to-get-all-old-values-for-a-mulitple-cell-range-with-onedit-trigger) – SputnikDrunk2 Jun 01 '23 at 04:51
  • `multiple cells changed at the same time)` Would you please edit your question to provide an example of your process for changing multiple cells at the same time. – Tedinoz Jun 01 '23 at 08:43
  • example, if you paste data for cells A2:B2, with xxx. old value YYY. right now both old and new value are not saved and it only shows it as ranged result in the logger. wanting it like A2 old YYYY new XXXX. B2 old YYYY new XXXX. – DCC Jun 01 '23 at 10:27
  • `example, if you paste data for cells A2:B2, with xxx.` As noted in the question referenced by @SputnikDrunk2, the Event Objects for `onEdit` do NOT include new and old values when the range is more than one cell. Tanaike's answer proposes a workaround that may be suitable for you. – Tedinoz Jun 04 '23 at 03:34

0 Answers0