How to insert user email after Sheets cell is edited

Question

Working on a sheet that when the user edits a cell (example C3), it will insert his/her email (name). Have a scrip that does work for only one column ("C"), but when moved to another sheet or column ("5 or E") it gives “Error – you do not have permission to call getActiveUser (line 41)".
Because it does work once, not sure what I am missing or why the same script wont work at all on another sheet. Thanks for any assistance.

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet(); 
 if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
   var r = s.getActiveCell();
    var email = Session.getActiveUser().getEmail();
   Logger.log(email);
   if( r.getColumn() == 3 ) { //checks the C column
    var nextCell = r.offset(0, 1);
   nextCell.setValue(email);
  if( r.getColumn() == 5 ) { //checks the E column
   var nextCell = r.offset(0, 1);
  if( nextCell.getValue() !== '' ) //is empty?
 nextCell.setValue(email);
   } 
  }
 }

The formatting on this code is a bit messed up, your nested "if" statements can be lined up to help with whoever may have an answer. — Ryanman

Ritz Ritz · Accepted Answer · 2017-11-17T22:37:34

Make sure you are the owner of the spreadsheet and you have granted the necessary permission to run the script.

function onEdit() {
    var s = SpreadsheetApp.getActiveSheet();
    if (s.getName() == "Sheet2") { //checks that we're on the correct sheet
        var r = s.getActiveCell();
        var email = Session.getActiveUser().getEmail();
        if (r.getColumn() == 3) { //checks the C column
            var nextCell = r.offset(0, 1);
            nextCell.setValue(email);
        }
        if (r.getColumn() == 5) { //checks the E column
            var nextCell = r.offset(0, 1);
            Logger.log(nextCell.getValue());
            if (nextCell.getValue() == '') //is empty?
                nextCell.setValue(email);
        }
    }
}

How to insert user email after Sheets cell is edited

1 Answers