Apps Script - onEdit combine timestamp and username/email address not working

0
votes

I'm attempting to pull the ActiveUser and Email Address of the person making the change to the Google Sheet and thought it would be possible using the following, but all I can get is the timestamp. If I change "var obj2 = (Session.getActiveUser().getEmail()); to literally anything else, it pulls the new data so I feel like the rest of my script is fine but can't figure out why it never pulls a username or address...

Any thoughts as to what I'm doing wrong? I heard that possibly google does not allow this information to be pulled via onEdit anymore? If that's the case is there another way?

DOESN'T WORK

function onEdit(e) {
  var r = e.range;
  var ss = r.getSheet();
  
  // Prepare an object for searching sheet name.
  var obj = {'SHEET1': "D", 'SHEET2': "G"};
  var obj2 = (Session.getActiveUser().getEmail());

  // Using the object, check the sheet and put or clear the range.
  if (r.getColumn() == 1 && obj[ss.getSheetName()]) {
    var celladdress = obj[ss.getName()] + r.getRowIndex();
    if (r.isChecked()) {
      ss.getRange(celladdress).setValue(new Date()).setNumberFormat(("MM/DD/YYYY hh:mm:ss") + " " + obj2);
    } else {
      ss.getRange(celladdress).clearContent();
    }
  }
}

DOES WORK (spits out Timestamp with a space and the word TEST at the end. Test is where I'd expect the username/email in the first example that doesn't currently work)

function onEdit(e) {
  var r = e.range;
  var ss = r.getSheet();
  
  // Prepare an object for searching sheet name.
  var obj = {'SHEET1': "D", 'SHEET2': "G"};
  var obj2 = ("TEST");

  // Using the object, check the sheet and put or clear the range.
  if (r.getColumn() == 1 && obj[ss.getSheetName()]) {
    var celladdress = obj[ss.getName()] + r.getRowIndex();
    if (r.isChecked()) {
      ss.getRange(celladdress).setValue(new Date()).setNumberFormat(("MM/DD/YYYY hh:mm:ss") + " " + obj2);
    } else {
      ss.getRange(celladdress).clearContent();
    }
  }
}
1
google-apps-scripttimestamp

1 Answers

2
votes

It is limited for consumer accounts

From:

https://developers.google.com/apps-script/guides/triggers/events under User

A User object, representing the active user, if available (depending on a complex set of security restrictions).

From:

https://developers.google.com/apps-script/reference/base/user#getemail

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.

If you are in a Workspace Domain then what you need to do is to make it an installable trigger. Then anyone in your domain who edits it, will appear in the logs if you call:

Logger.log(e.user.getEmail())

But for consumer (gmail) accounts this is far more limited, usually returning just a blank string. This is for security as much as anything, because without a policy like this, someone might use Sheets as a way to mine e-mail addresses.