-1

In order to track the latest change on a specific line in a google sheet and adding user credentials to a specified column on that line with the function below works perfectly in a test sheet I created my own.

However, the exact same formula with parameters (sheet name / column numbers) adapted, it does not work any more in another sheet.

The only difference I can think of is, that in case 1 (test sheet) I am the owner of the document and in the latter I am not. Other Makro functions versions / extensions of the function below did make changes to the sheet though, so I am expecting it is not a write permissions issue.

What could be the issue here?

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var usercolumn = 1; // Change this to the column number you want to edit
  var users = {
    "User1@gmail.com": "User1",
    "User2@gmail.com": "User2",
    "User3@gmail.com": "User3"
  }; // Change this to the mapping of user email addresses to values
  
  if (sheet.getName() == "MySheetName" && e.range.getColumn() == 2 && users.hasOwnProperty(e.user.getEmail())) {
    sheet.getRange(e.range.getRow(), usercolumn).setValue(users[e.user.getEmail()]);
  }
}

Tried different variations of the function; sometimes it would delete the content of the specified cell, that I was expecting it to write "User1" ( ... ) for me.

jodevelops
  • 1
  • `However, the exact same formula with parameters (sheet name / column numbers) adapted`. Would you please edit your question to provide the script for the function that does not work. – Tedinoz Apr 12 '23 at 13:17
  • The function is identical in both environments and was tested with specific values in both environments for column numbers and sheet names. – jodevelops Apr 13 '23 at 09:00

1 Answers1

0

I suspect you are using a simple trigger and I could see there being a problem with your line

e.user.getEmail()

because (Source: https://developers.google.com/apps-script/guides/triggers#restrictions)

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

You could try using an installable trigger https://developers.google.com/apps-script/guides/triggers/installable

However this would mean that every user would need to create their own trigger

Installable triggers always run under the account of the person who created them. >For example, if you create an installable open trigger, it runs when your colleague >opens the document (if your colleague has edit access), but it runs as your >account. This means that if you create a trigger to send an email when a document >is opened, the email is always sent from your account, not necessarily the account >that opened the document. However, you could create an installable trigger for each >account, which would result in one email sent from each account.

https://developers.google.com/apps-script/guides/triggers/installable#restrictions

PrettyWhales
  • 1
  • 2