Macro to list active users

Question

Sorry, a total newby to Google Sheets here, but with reasonable vba experience.

I'm trying to create an active list of active users in a hidden sheet that could then be used to highlight the existence of those users with a conditional format and vlookup.

Can this work in Sheets?

So far, I have the code below, but it's early days, and this only seems to list the one user that activated the code, rather than all. I think I can see why that is but I'm finding it really difficult to find resources to help me along (again - a newby to Sheets).

function GetUserNames() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sht = ss.getSheetByName("ActiveUsers");
// Log the email address of the person running the script.
var email = Session.getActiveUser().getEmail();
Logger.log(email);
//SET CELL A1 TO EMAIL ADDRESS OF ACTIVE USERS
sht.getRange(1,1).setValue(email);
}

My plan is to run this with a From spreadsheet - On open trigger. Is that the preferred option for such things? I haven't begun to look at the hidden sheet aspect but presume that will work OK, even if my script has to un-hide/re-hide it each time?

Answer 1

You should bear in mind that Session.getActiveUser.getEmail() only works under certain conditions, i.e. the users of your spreadsheet should belong to the same G Suite domain as the spreadsheet owner.

Also, you should bear in mind that there are two types of triggers, simple and installable; simple triggers has several limitations and installable triggers are executed using the credentials of the user who created the trigger.

Using on-open triggers to un-hide/re-hide sheets, rows or columns usually is a bad idea because affects all the users so you and the spreadsheet users should be aware of this but IMHO even being aware this could be disruptive.

Related

• Active VS Effective User for Google Sheets