Google app script's Session.getActiveUser() not working for cross domain users

Question

I'am using Google App Script and Google Spreadsheet onOpen() trigger. I'm locking logged in user in Google Spreadsheet.

The restriction for the same is that it's not logging email id on cross domain which I found on forums.

Here is the code for the same:

function onOpen(e)
{ 
  try
  {
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('Action')
    .addItem('Add Data', 'addData')
    .addToUi()  

    var sheet =SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Login Details");
    sheet.appendRow([Session.getActiveUser().getEmail(),new Date()]);

  }
  catch(e)
  {
    Logger.log(e);
  }
}

One observation from onOpen() functionality :

1] I'm owner of spreadsheet and it only logs the email ids of our company domain. (Automatically onOpen() of spreadsheet)

2] From other domain when I open bounded script of spreadsheet and run onOpen() then it logs the logged in user.

What is reason behind the same and is there any workaround for user on cross domain?

What do you mean by 'not logging email id on cross domain which I found on forums', what forums? And also in your second observation you are saying that it logs logged in user, then what's the issue? is it logging or not logging email id?. Yes, it will fetch logged in user only. If you want to get the user who has given authorizartion to the script, use getEffectiveUser(). Please explain on above points which I mentioned. — Shyam Kansagra
@ShyamKansagra: On StackOverflow & link. Lets say my domain is abc.com and clients domain is xyz.com then functionality works fine on abc.com. Means when I logged in with [email protected] then it logs the username in 'Login Details' worksheet. But if I logged in with [email protected] then I get the blank username. As per second observation when I logged with [email protected] and open script editor and run onOpen then it logs email id as [email protected] but when directly open spreadsheet it won't. — Poonam Malve
Might be because you must have forgotten to set the trigger on [email protected] ....please check that if you have set trigger for onOpen() in your domain then similarly you have to set trigger on client's ID also, triggers do not get copied with the code. — Shyam Kansagra
@ShyamKansagra: I've set trigger on [email protected] but the issue remains same. But normally onOpen() is a simple trigger which automatically set whenever a user opens google spreadsheet. — Poonam Malve
can you share your sheet? Also, are you getting any logs from catch part? — Shyam Kansagra

Shyam Kansagra Shyam Kansagra · Accepted Answer · 2017-01-06T10:46:04

I think Session.getActiveUser().getEmail() has issues with onOpen trigger on outside domain when that person is not the owner.

Also, automatic per minute triggers also are not able to log active user.

Here's the workaround:

Add menu in the spreadsheet itself, with an option which when clicked, will run the function to append the row that you want. As, Session.getActiveUser().getEmail() is working when we run function manually from script.

Use this code:

function addMenu()
{
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Log user')
      .addItem('Run script', 'appendRow')
    .addToUi()
}

And also add this function:

function appendRow()
{ 
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("YOUR_SHEET").appendRow([Session.getActiveUser().getEmail(),new Date()]);
}

Now, whenever you want to log the active user, go to that Log user and click Run script and it will append the row.

Also, don't forget to set onOpen trigger for addMenu() function on client side or even your side.

For more details, we can read this: Class session

Google app script's Session.getActiveUser() not working for cross domain users

1 Answers