How to get active user email with installabled onEdit trigger?

Question

I have a Google spreadsheet with some data. I wrote script to track changes of some specific columns.

function onOpen() {
  var ss = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Turn on', functionName: 'createSpreadsheetEditTrigger'}
    ];
  ss.addMenu('Tracker', menuItems);
}


function changeTrack(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ui = SpreadsheetApp.getUi();
  var ws = ss.getActiveSheet(); 
  const headerRow = 4;
  const editBodyCols = [2, 3, 4, 5];
  const fResultCol = 6;
  var range = ws.getActiveRange();
  var row = range.getRow();
  var col = range.getColumn();
  let target1 = ws.getRange(row, fResultCol);  
  let target2 = ws.getRange(row, fResultCol + 1)
  let activeUser = getCurrentUserEmail();
  if(row > headerRow && editBodyCols.some(x => x === col) === true){
    if(target1.getValue() !== ""){
    target2.setValue(result(ss, ws, row, activeUser)[1]);
    } else {
      target1.setValue(result(ss, ws, row, activeUser)[0])
      target2.setValue(result(ss, ws, row, activeUser)[1])
    }
  }  
}

function createSpreadsheetEditTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('changeTrack')
      .forSpreadsheet(ss).onEdit()     
      .create();
}

function date(){
  return  Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");  
}

function result(ss, ws, row, activeUser) {  
  const ssName = ss.getName();  
  let data = `Создал ${activeUser} ${date()}`;
  let exp = `Файл ${ssName}, Лист ${ws.getName()}, изменил ${activeUser}, строка № ${row}, ${date()}`;
  let adds = [];
  adds.push([data],[exp]);
  return adds;
}

function getCurrentUserEmail()
{
  var email=Session.getActiveUser().getEmail();
  return email;
}

My problem is to get active user's email. This script can get it but not all the time. Seems like random success. It means sometimes I can get expected value, sometimes not. I don't understand what is it depends from. Where I'm wrong and how to fix it?

Answer 1

From the documentation on Session.getActiveUser():

Gets information about the current user. If security policies do not allow access to the user's identity, User.getEmail() returns a blank string. The circumstances in which the email address is available vary: for example, the user's email address 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).

So this seems pretty expected and there is no hard workaround you can make to retrieve the users mail. You should maybe just ask for it and see if they be willingly want to give it to you.

Although if you are the developer or the users are inside your organization this restrictions may be ignored:

However, these restrictions generally do not apply if the developer runs the script themselves or belongs to the same G Suite domain as the user.

Answer 2

Based on the comment by b-frid.

The flow:

1. create a custom menu and tell each user to run the function twice. First time for authorization, and the second time to actually run the code.
2. this will install the same trigger with the user's privileges and let the onEdit trigger get the email of an active user (author of the trigger)

video-instruction:

https://www.loom.com/share/30b11e4d012447f7a1efdd9a7eac4fca

gist:

https://gist.github.com/Max-Makhrov/e3e89e7fe0c6e86b68a4be1262e53629

Code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu(' run me 2x times')
      .addItem('please let script to see your email', 'install')
      .addToUi();
}


// function test() {
//   var e = {
//     range: SpreadsheetApp.getActive().getSheetByName('test_onEdit').getRange('B2')
//   }
//   edit_(e)
// }
function edit_(e) {
  var targetcol = 1;
  if (e.range.getSheet().getName() === 'Sheet1') {
    var user = Session.getActiveUser().getEmail();
    if (user !== '') {
      var col = e.range.getColumn();
      var rows = e.range.getHeight();
      var ratgetrange = e.range.offset(
        0,
        targetcol - col,
        rows,
        1);
      ratgetrange.setValue(user);
    }
  } 
}

function install() {
  setOnEditTrigger_(
    SpreadsheetApp.getActive().getId(),
    'edit_');
}


/**
 * create onEdit trigger
 * 
 * @param {string} spreadsheetId
 * @param {string} functionName
 * 
 */
function setOnEditTrigger_(spreadsheetId, functionName) {
  console.log('OnEdit trigger ' + functionName +
     ' for new file' +
     spreadsheetId);
  var trigger;

  if (existsOnEditTrigger_(functionName, spreadsheetId)) {
    console.log('stopped execution. Trigger exists.');
    return;
  }

  trigger = ScriptApp
    .newTrigger(functionName)
    .forSpreadsheet(spreadsheetId)
    .onEdit()
    .create();  
  
  console.log('Created new trigger!')
  return trigger.getUniqueId();
}

/**
 * check if onEdit trigger exists
 * 
 * @param {string} spreadsheetId
 * @param {string} functionName
 * 
 */
function existsOnEditTrigger_(functionName, spreadsheetId) {
  var triggers = ScriptApp.getProjectTriggers();
  var trigger = {};  
  for (var i = 0; i < triggers.length; i++) {
    trigger = triggers[i];
    if (
      trigger.getHandlerFunction() === functionName && 
      trigger.getTriggerSourceId() === spreadsheetId &&
      trigger.getEventType().toString() === 'ON_EDIT') return true;
  }  
  return false;
}

Original comment:

The key is separate triggers for each user. Then I simply added code to execute the update only if the Session.getActiveUser().getEmail() call does not come back blank. Of course, because each's user's trigger will run, the function will execute x times where x = the number of users (i.e. triggers), but with the check for a blank return value the logic only runs once (and so no overwrites). A bit clumsy indeed, and perhaps not practical if you have more than a handful of users, but workable in my case.