Google sheets - User/Email stamp triggered ON EDIT works only with OWNER account - how to enable user/email stamps for all editors?

Question

I need a solution to enable all editors to leave their name stamps after they make changes in a sheet. I was able to come up with script code which does work ONLY FOR OWNER.

I tried to authorize the script by running the script manually from editors accounts - the app has the authorization but even though it doesn't work for Editors.

Norbert Wagner: "However authorizing from the script editor did not work for me. But as soon as I added a menu in the document and executed the function from there, I got an authorization request in the document. From then on also the onEdit function works, for all users. I did this as the documents owner though. " -

maybe this is the solution? But how can I run the onEdit from the document? Simple edit doesn't work, but how about this menu? Is there a way to execute ALL SCRIPTS AND FORCE AUTHORIZATION from document level?

function onEdit(e) {

// Your sheet params
var sheetName = "Arkusz1";
var dateModifiedColumnIndex = 3;
var dateModifiedColumnLetter = 'C';

var range = e.range; // range just edited
var sheet = range.getSheet();
if (sheet.getName() !== sheetName) {
 return;
}

// If the column isn't our modified date column
if (range.getColumn() != dateModifiedColumnIndex) { 
var row = range.getRow();
var user = Session.getActiveUser().getEmail();
  user = String(user);
user = user.replace('@gmail.com', '');
var dateModifiedRange = sheet.getRange(dateModifiedColumnLetter + row.toString());
dateModifiedRange.setValue(user);

};
};

This function works only when using owner account. Even after Editor has authorised script manually from script editor - the onEdit user stamp function doesn't trigger.

Below is another function which I used for testing. When I run it manually from editors account IT WORKS - it saves editor's name in A1.

function USERNAME(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0]; // this will assign the first sheet
var cell = sheet.getRange('A1');
var d = new Date();
d.setHours(d.getHours() +1);
var user = Session.getActiveUser().getEmail();
user = String(user);
user = user.replace('@gmail.com', '');
cell.setValue(user);
}

St3ph St3ph · Accepted Answer · 2019-08-30T13:41:29

In fact you have to use installable trigger.

Rename your function onEdit(e) with another name for example customFunction(e)

Then you setup an installable trigger using this function :

function createTrigger(){
ScriptApp.newTrigger('customFunction')
  .forSpreadsheet(SpreadsheetApp.openById('YOU_SHEET_ID'))
  .onEdit()
  .create();
}

Or you can setup the trigger manually by going to 'Edit >> Current project's triggers >> Click on + button (bottom right)'

By this way trigger will run each time there is an Edit no matter the user.

Stéphane

Google sheets - User/Email stamp triggered ON EDIT works only with OWNER account - how to enable user/email stamps for all editors?

1 Answers