I have a spreadsheet in Google Sheets that is shared with five people. Only a few columns are edited; all of the other columns are locked.

We need the ability to track changes to these editable columns, so, based on this answer, I added a script in Apps Script like this:

function onEdit() {
 if( s.getName() == "MySpecialSheet" ) { // Set to sheet name
   var r = s.getActiveCell();
   var col= r.getColumn();
   if( col == 3 ) { // Check the column
     var nextCell = r.offset(0, 1);

     var datetime = new Date();
     datetime = Utilities.formatDate(datetime, "GMT+9", "yyyy/MM/dd, hh:mm:ss"); // +9 for Tokyo
     nextCell.setValue(datetime);
   };
 };
}

This automatically inserts the time when the relevant cell was edited, which is great. However, I also want to add a way to tell who edited the cell.

I checked the User class in the apps script documentation, but it seems the only method is getEmail(), which:

is not available in any context that allows a script to run without that user's authorization, like a simple onOpen(e) or onEdit(e) trigger, a custom function in Google Sheets, or a web app deployed to "execute as me" (that is, authorized by the developer instead of the user).

Since my script uses onEdit(), I have no way to get the email address. Is there a way to get any kind of ID (I don't need the email address-- it could be the name of the account or a number) for the person editing a cell, and then insert that into another cell?

If not, I suppose I can create a column that people can just type their initials into, but people tend to forget so I would prefer an automated solution if possible.

  • You can have your users run a function via the menu, where they set their ID in the PropertiesService.UserProperties. The onEdit function might be able to read it. I am not sure if on-edit is allowed, but it would be worth a try. – Riël Jun 5 '17 at 11:19
up vote 1 down vote accepted

I searched for this earlier and figured out that it depends if you have private accounts or part of the same company. For private accounts it's not possible, at least it was not when I researched. My solution was to have each person fill out his/her name.

I copy my code here, you have to change the name of the sheet, row number etc

function onEdit(e) { 
 if (e.source.getActiveSheet().getName() !== 'adresseliste') return;
 if (e.range.columnStart < 1 || e.range.rowStart < 2) return;
 e.source.getActiveSheet().getRange(e.range.rowStart, 37)
        .setValue(Utilities.formatDate(new Date(), "GMT+2", "dd.MM.yyyy HH:mm"));
 var navn = Browser.inputBox('Hvem er du?');
 e.source.getActiveSheet().getRange(e.range.rowStart, 38)
    .setValue(navn);
 var why = Browser.inputBox('Hvorfor endret du?');  
 e.source.getActiveSheet().getRange(e.range.rowStart, 39)
    .setValue(why);
 }

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.