2

I created an add-on for Google spreadsheets.

Everytime it runs, it must check the user's email address

Session.getActiveUser().getEmail();

Therefore, I created an onOpen trigger to fetch the email address, but it seems this isn't possible according to Google's documentation here.

Then, how do I use the User Class?

This is how the functon to get the email looks like:

function myFunction() {
  var userEmail=Session.getActiveUser().getEmail();
  Browser.msgBox("Your email address is "+userEmail);
}

I tried so getEffectiveUser() but it gives the developer's email address instead.

I tried sharing the spreadsheet with a second user, he gave permission to show the email bit no matter what I do I always get an empty string!

I need this app to check if the user's email is on a list of another spreadsheet.

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • 1
    onOpen runs in a restricted so you cannot get the email address once it is published. The best you can do is access the PropertiesService. What are you trying to do? If you could find a workflow where someone has to click to get to the menu then it might work better. For one add-on I moved the menu to the sidebar, which that can getEffectiveUser() and display a custom set of buttons/menu. – Bjorn Behrendt Dec 19 '14 at 19:25
  • Thanks, I'm trying to check if the users email is on a list of another spreadsheet –  Dec 19 '14 at 19:51

1 Answers1

0

Essentially, it's intended that this is nearly impossible, or at least incredibly difficult. As the documentation you linked indicates, you're not able to pull another users email address using a custom function in sheets, and this is a good thing, as it would be easy to farm email addresses using this method on unsuspecting users.

A great rule of thumb is -> If they're not using Google Apps in the same organisation as yourself = You can't get their email address. (A moments thought shows us why this is a good thing. Once you authorise someone to see what your address is, even for a legitimate purpose, you have no idea what they do with it after, and spammers and scammers would love to be able to use Apps script to farm addresses).

The only reasonable solution here is to have the script check for the address, and if a blank string is returned, request the user manually enter their address into a text box into the form/sheet/App etc.

HDCerberus
  • 2,113
  • 4
  • 20
  • 36
  • Thanks, I got the idea. If the script is deployed as an add-on, perhaps I can use getEffectiveUser() instead? –  Dec 26 '14 at 23:00
  • Yes... In some circumstances, but this again depends on whos running it and when. If you deploy it as an add-on it will still (in most cases) only work for the users that install it, not external ones. For example, if i install the Add-on on a sheet in my domain, and a user external to my domain accesses the sheet, it still wont be able to fetch the external users address, only mine as i was the one who installed it. – HDCerberus Dec 26 '14 at 23:19
  • Ok, that is good enough. I only need the email of the users who install the addon –  Dec 27 '14 at 15:41