Determine current user in Apps Script

Question

I'm trying to identify current user's name to make notes of who edited what like this:

  r.setComment("Edit at " + (new Date()) + " by " + Session.getActiveUser().getEmail());

but it won't work - user's name is an empty string. Where did I go wrong?

Answer 1

I suppose you have this piece of code set to execute inside an onEdit function (or an on edit trigger).

If you are on a consumer account, Session.getActiveUser().getEmail() will return blank. It will return the email address only when both the author of the script and the user are on the same Google Apps domain.

Answer 2

function onEdit(e) {
  SpreadsheetApp.getUi().alert("User Email is " + e.user);
}

Answer 3

GOOD NEWS: It's possible with this workaround!

I'm using some protection functionality that reveals the user and owner of the document and I'm storing it in the properties for better performance. Have fun with it!

function onEdit(e) {
  SpreadsheetApp.getUi().alert("User Email is " + getUserEmail());
}

function getUserEmail() {
  var userEmail = PropertiesService.getUserProperties().getProperty("userEmail");
  if(!userEmail) {
    var protection = SpreadsheetApp.getActive().getRange("A1").protect();
    // tric: the owner and user can not be removed
    protection.removeEditors(protection.getEditors());
    var editors = protection.getEditors();
    if(editors.length === 2) {
      var owner = SpreadsheetApp.getActive().getOwner();
      editors.splice(editors.indexOf(owner),1); // remove owner, take the user
    }
    userEmail = editors[0];
    protection.remove();
    // saving for better performance next run
    PropertiesService.getUserProperties().setProperty("userEmail",userEmail);
  }
  return userEmail;
}

Answer 4

I had trouble with Wim den Herder's solution when I used scripts running from triggers. Any non script owner was unable to edit a protected cell. It worked fine if the script was run from a button. However I needed scripts to run periodically so this was my solution:

When a user uses the sheet the first time he/she should click a button and run this:

function identifyUser(){
   var input = Browser.inputBox('Enter User Id which will be used to save user to events (run once)');
  PropertiesService.getUserProperties().setProperty("ID", input);
}

This saves the user's input to a user property. It can be read back later at any time with this code:

var user = PropertiesService.getUserProperties().getProperty("ID");

Answer 5

In this code you can use a cell for input. Authorising scripts are not required.

function onEdit(e){
  checkUsername(e);
}

function checkUsername(e){
  var sheet = e.source.getActiveSheet();
  var sheetToCheck = 'Your profile';
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetToCheck);
  var CellInputUsername = 'B4';
  var ActiveCell = SpreadsheetApp.getActive().getActiveRange().getA1Notation();

  if (sheet.getName() !== sheetToCheck || ActiveCell !== CellInputUsername){return;}

  var cellInput = sheetName.getRange(CellInputUsername).getValue();
  PropertiesService.getUserProperties().setProperty("Name", cellInput);

  // Make cell empty again for new user
  sheetName.getRange(CellInputUsername).setValue("");

  var Username = PropertiesService.getUserProperties().getProperty("Name");      
  SpreadsheetApp.getUi().alert("Hello " + Username);
}