0

I'm looking to create a log of actions that take place in a specific sheet.

This script works well, but there's one major issue:

I'm not receiving values for "TheUser" aside from my own email. Is it possible to get emails from others? They are signed in as editors, with gmails.

function onEdit(e) {
var sName = e.source.getActiveSheet().getSheetName();
if(sName !== "Recent_Changes") {
var theUser = Session.getActiveUser().getEmail();
var value;
var mA1 = e.range.getA1Notation().split(":")[0];
var time = new Date();
if(typeof(e.value) == 'string') {
var aCell = e.source.getRange(mA1);
value = aCell.getValue();
var form = "'" + aCell.getFormula();
} else {
value = e.value;
}
var data = [sName, mA1, time, value, form, theUser];
e.source.getSheetByName("Recent_Changes").appendRow(data);
}
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Anthony Madle
  • 371
  • 1
  • 10
  • The problem with doing this is that it takes a lot of time to store the results in a file. Any kind of a file takes time to open and close. And you will be doing it on every edit using a script language as opposed to a compiled language. It's just going to slow everything down. – Cooper Nov 16 '22 at 03:28
  • @Cooper thanks Cooper - do you have any recommendations on other methods? Version history isn't efficient for what I need - I'm exploring all options, but this was the best I could figure out – Anthony Madle Nov 16 '22 at 04:39
  • 2
    Have you looked at the activity API ? – Cooper Nov 16 '22 at 04:54
  • Is your spreadsheet publicly shared and the editors are accessing the spreadsheet via shared link only(Anyone with link)? – Twilight Nov 16 '22 at 07:31
  • @Shadow Reaper -- it is restricted to be permission-based via emails. Meaning, I'll have everyone's account signed in in order for them to access it – Anthony Madle Nov 16 '22 at 15:38
  • Are the other editors emails under the same workspace organization? – Twilight Nov 16 '22 at 23:50

1 Answers1

1

Upon testing, the getActiveUser() function works if the users are under the same workspace organization. However, this is not the case when the users are not in the same organization and just using their personal gmails, even when the spreadsheet is shared directly to them. This returns a blank string. See reference for more information: Detailed documentation: getActiveUser()

These restrictions generally do not apply if the developer runs the script themselves or belongs to the same Google Workspace domain as the user.

You may see this SO case with same scenario: How to get active user email with installed onEdit trigger?

Additionally, It is stated in Restrictions Section in the Simple Triggers documentation that

they may or may not be able to determine the identity of the current user, depending on a complex set of security restrictions.

Twilight
  • 1,399
  • 2
  • 11