Monday, August 27, 2012

How to know which user modified the row

How to know which user modified the row

Are you wondering about how to know which user (collaborator) modified the row?

Following action works for all users (collaborators) including owner:

When any collaborator modified a row in the spreadsheet this script will add "the date and time" in the last cell of that row and will add a comment in that last cell with email address of that collaborator who had modified that row.

The following script will work when either the owner or any other collaborator of a spreadsheet modified that spreadsheet:

function onEdit(event)

  var sheet = event.source.getActiveSheet();
  // Note: actRng = return the last cell of the row modified
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();
  var cindex = actRng.getColumnIndex();
  // Note: date = return date + time
  // Note: user = return the user email
  var dateCol = sheet.getLastColumn();  
  var lastCell = sheet.getRange(index,dateCol);
  var date = Utilities.formatDate(new Date(), "GMT-0300", "dd-MM-yy HH:mm");
  //var user = event.user;  // Note: event.user will not give you collaborator's Id
  var user = Session.getEffectiveUser();
  // Note: setcomment = Inset a comment with the user email
  // Note: setValue = Insert in the cell the date + time when this row was modified
  lastCell.setValue("'" + date).setComment(user);

I hope this script will do your task of getting to know which collaborator has modified which row and at what time.

If you are new to Google Spreadsheet Scripts, then you can check out the following link:

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: 



  1. I am still unable to track the collaborator's ID using this code. Please suggest.

  2. Thanks! Works great, yes except for the user's email/ID...that piece of data isn't showing along with the date.