Google-sheets – ny way to identify a user in Google Apps Script

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

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.

Best Answer

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);
 }
Related Topic