Google Sheets: Session.getEffectiveUser() returns

Question

So what I'm trying to do is to log the edits of a spreadsheet on a corresponding column.

http://i.imgur.com/P8qrJOe.gif

The last column is the autogenerated text from the embedded script.

Here's the code:

function triggeredEdit () {
  // set sheet variables
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var activeRange = activeSheet.getActiveRange();

  // last updated

  // LAST EDITED FOR SCRIPTS (check M3 if "Latest Row Modifications")
  if (activeSheet.getRange("M3").getValue() == "Latest Row Modifications") {
    for (var i = 1; i <= activeRange.getHeight(); i++) {
      // modified TL column
      if (activeRange.getCell(i, 1).getRow() > 4 && activeRange.getColumn() >= 6 && activeRange.getLastColumn() <= 6) {
        activeSheet.getRange(activeRange.getCell(i, 1).getRow(), 13)
        .setValue(Session.getEffectiveUser() + "\n" + Utilities.formatDate(new Date(), "GMT", "dd MMM YY, HH:mm:ss"));
      }
      // modified TLC column
      else if (activeRange.getCell(i, 1).getRow() > 4 && activeRange.getColumn() >= 8 && activeRange.getLastColumn() <= 8) {
        activeSheet.getRange(activeRange.getCell(i, 1).getRow(), 14)
        .setValue(Session.getEffectiveUser() + "\n" + Utilities.formatDate(new Date(), "GMT", "dd MMM YY, HH:mm:ss"));
      }
      // modified edit column
      else if (activeRange.getCell(i, 1).getRow() > 4 && activeRange.getColumn() >= 10 && activeRange.getLastColumn() <= 10) {
        activeSheet.getRange(activeRange.getCell(i, 1).getRow(), 15)
        .setValue(Session.getEffectiveUser() + "\n" + Utilities.formatDate(new Date(), "GMT", "dd MMM YY, HH:mm:ss"));
      }
      // modified QC column
      else if (activeRange.getCell(i, 1).getRow() > 4 && activeRange.getColumn() >= 11 && activeRange.getLastColumn() <= 11) {
        activeSheet.getRange(activeRange.getCell(i, 1).getRow(), 16)
        .setValue(Session.getEffectiveUser() + "\n" + Utilities.formatDate(new Date(), "GMT", "dd MMM YY, HH:mm:ss"));
      }
    }
  }
}`

The function triggeredEdit is run through a project trigger, which is also dynamically created in another function. As you can see in the gif, it seems like the triggeredEdit is called twice (since it changes twice). What's worse is that the correct email to be displayed is the first one, but then it gets replaced with the wrong one.

How do I go around fixing this? I'm quite stuck with this problem.

Answer 1

From what you explained in the comments It seems that you have 2 triggers on that spreadsheet that work simultaneously.

When you call Session.getEffectiveUser in a function triggered by onEdit, it returns the email of the user that installed the trigger. If you want to get the user at the keyboard then use Session.getActiveUser. Note that this will work only if you are in a domain, nothing is returned in normal gmail accounts.

To test that I used this small code :

function triggeredEdit () {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var activeRange = activeSheet.getActiveRange();
  Logger.log('xx');
  activeSheet.getRange(1, 1).setValue('effective = '+Session.getEffectiveUser() + "\n" + Utilities.formatDate(new Date(), "GMT", "dd MMM YY, HH:mm:ss"));
  activeSheet.getRange(1, 3).setValue('active = '+Session.getActiveUser() + "\n" + Utilities.formatDate(new Date(), "GMT", "dd MMM YY, HH:mm:ss"));
}

and here is what I get when I edit a cell (Serge created the trigger and Lambda was the user at the keyboard.

:

If I use the spreadsheet with my gmail account then I get this result below : no value as activeUser

and, finally, if I create a second trigger with my gmail account I have a double instance like yours.(I didn't capture the gif, sorry ;) All this seems quite logical after all when I refer to the documentation about script authorization : triggers run under the authority of the user that creates it.

The soluion in your case will probably to change the way the triggers are created to avoid that anyone can do it... or try to filter execution in some way but I'm not sure that can be done.