Google Spreadsheets: Can't Fetch User Email via Session.getActiveUser().getEmail()

Question

So I have a spreadsheet that is supposed to automatically fetch every users' emails and assign custom usernames for their emails. I use this data to display a per-row "last edited by ---" on a sheet. This also allows me to list all the users viewing the spreadsheet and log when they last opened it.

However, the Session.getActiveUser().getEmail() returns a blank string for anyone else other than me.

I've seen that you could make it so that the scripts run under the user's account. But that is only for deployed web apps. I need to integrate it to spreadsheets. Any ideas on how I should do that?

have you tried .getEffectiveUser().getEmail()? Or, what are the settings you used when you published the script? Did you set it to run as you, or to run as the user accessing the email? Look at the documentation here developers.google.com/apps-script/reference/base/session — thoughtcrime
getEffectiveUser() only returns my own email, even if other people are opening the sheet. — Jerome Indefenzo
Then your app is published to run as you, not as user accessing the app. If you can change it, and retain functionality, try it that way. If not, you'll have to add an interface for editing the sheet that collects their email as part of the Ui. I will look at putting something together in a couple of hours after I get the kids to school. — thoughtcrime
Thanks, but afaik, deploying the script as a web app wouldn't allow me to use the native google spreadsheet interface. And I can only set the script to run as the user if I deploy it as a web app. I'm dealing entirely with spreadsheets, since all the data I need are already there. Thanks, I'll be waiting for your mockup. — Jerome Indefenzo
are you using a regular gmail account or a domain account ? In the first case this is normal behavior for quite a long time (it was working a few years ago but has been intentionally removed) doc here : developers.google.com/apps-script/reference/base/session?hl=en — Serge insas

thoughtcrime thoughtcrime · Accepted Answer · 2014-03-11T18:20:20

Deploying as a web app (publishing) lets you set under what "authority" or entity the script runs as. If you set the script to run as user accessing the app, and then use the code below, you will get "Row updated 3/11/14 by " with the actual user name there. If you run the app as you, then it will treat you as the effective user, and it will always put your email in there. If the app runs as the user, not author, and it's published as the user accessing, then the app will write the user's email in the updated string. I tested this in my own domain, but can't link to the test sheet outside of our domain.

function onEdit(event){
  Logger.log(event.source.parameters);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var actSht = event.source.getActiveSheet();
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();
  var user = Session.getEffectiveUser().getEmail();
  Logger.log(index);
  var dateCol = actSht.getLastColumn();
  var lastCell = actSht.getRange(index,dateCol);
  var date = Utilities.formatDate(new Date(), "GMT-6", "MM/dd/yyyy HH:mm:ss");
  lastCell.setValue("Row Updated " +date+" by "+user);

}

Google Spreadsheets: Can't Fetch User Email via Session.getActiveUser().getEmail()

2 Answers