Edit History in Google Sheet

user_11823363 0
Viewed 235

This code updates only the Last edit of the cell (User, Date & Time) and inserts as Note OnEdit

function onEdit(e) {
 var email = Session.getActiveUser().getEmail();
  var curDate = Utilities.formatDate(new Date(), "GMT+05:30", "EEEE dd-MMM-yyyy ● hh:mm a")
  if (e.range.columnStart === 5 && e.source.getActiveSheet().getName() == 'ARTWORK') {// Column 12 is Column L
    //Set a comment on the edited cell to indicate when it was changed.
    e.range.setNote('Updated by - ' +email+ '\n\nDate & Time\n' + curDate);
  };
}

But my requirement is, can we add a history of edit in the cell as a Note?

e.g.
EDITED by NYK at 11-Sep-2020 - 11:20 am
EDITED by XYZ at 12-Sep-2020 - 12:20 pm
EDITED by ABC at 15-Sep-2020 - 15:01 pm

Is this possible?

google-apps-script google-sheets javascript
1 Answers

Explanation:

  • You can get the current notes of the cell and append a new note when an edit is made to this particular cell.

  • To accomplish this task, you can use getNote() to get the current notes of the selected cell and then use setNote() to update the notes by adding the additional note to the existing ones:

    var current_notes = e.range.getNote();
e.range.setNote(`${current_notes}\n\n Updated by -  ${email} \nDate & Time\n  ${curDate}`);

  • Since the latter string expression involves a lot of variables, it is preferable to use Template literals instead.

Solution:

function onEdit(e) {
 var email = Session.getActiveUser().getEmail();
  var curDate = Utilities.formatDate(new Date(), "GMT+05:30", "EEEE dd-MMM-yyyy ● hh:mm a")
  if (e.range.columnStart === 5 && e.source.getActiveSheet().getName() == 'ARTWORK' && e.range.getValue() !="") {// Column 12 is Column L
    var current_notes = e.range.getNote();
    e.range.setNote(`${current_notes}\n\n Updated by -  ${email} \nDate & Time\n  ${curDate}`);  
    SpreadsheetApp.flush();
  };
}

Illustration:

example

Relevant information:

Edit history of a cell is a new feature recently introduced by google. As of now there is no way to use Google Apps Script to get the edit history of a cell. This feature has been already reported for implementation in the IssueTracker. You can click on the star button on the left top corner of the screen to increase the chances that this feature will be implemented in the future by google.

user_112252910
Related