CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Hasan A Yousef
Hasan A Yousef

Reputation: 24928

How can I get email of the last user who modified each cell

Trying to add a 'rolling' note of changes at each cell, including user email, time, old and new values, so I wrote the below script:

/**
 * The event handler triggered when editing the spreadsheet.
 * @param {Event} e The onEdit event.
 */
function onEdit(e){
  // Set a comment on the edited cell to indicate when it was changed.
  var range = e.range;
  var row = range.getRow();
  var column = range.getColumn();

  // Get the email address of the person running the script.
   var email = Session.getActiveUser().getEmail();

  Logger.log("data changed");
  range.setNote(range.getNotes().concat('Last modified at: ' + new Date() + ' by: ' + email + ' from: ' + String(e.oldValue) + ' to: ' + range.getValue() + "\n"));
}

I'd the below notes, that are not as I expect:

  1. User email not displaying at all,
  2. If I made copy/paste of multiple cells, then old value appear as undefined, and new value for all cells shows only the new value of the first cell.

My sheet can be opened here

Upvotes: 0

Views: 2917

Answers (1)

Wicket
Wicket

Reputation: 38130

The info that you are adding is already automatically recorded and available by right clicking on a cell and selecting the corresponding menu item, anyway, the simple on edit trigger is very limited compared with the installable on edit trigger but the Session.getActiveUser().getEmail() only returns an email if the script is ran by using G Suite accounts or by the owner of the spreadsheet / script or the user created the installable on edit trigger that called the script. Somehow this is already documented in the official docs.

Reference

Related

Upvotes: 2

Related Questions