How can I use Google Sheets script to capture a user's email, name, or any information when a checkbox is selected?

Question

Purpose is a timestamp of approval - and while I can capture the date and my own email (since I'm the owner of the script), it currently captures only the date and no personal information

function onEdit(e) {
  
    var s = SpreadsheetApp.getActiveSheet();
    if (s.getName() == "Brand Approval") { //checks that we're on the correct sheet
        var r = s.getActiveCell();
        var email = Session.getActiveUser().getEmail();

        if (r.getColumn() == 18) { //checks the C column
            var nextCell = r.offset(0, 2);
            nextCell.setValue(email);
             var nextCell = r.offset(0, 1);
            nextCell.setValue(new Date());
        }
    }
}

Welcome to StackOverflow! Try to add Logger.log(e) and see what is inside — Waxim Corp, Jan 11 '23 at 19:30
If you have permission to get access to that private data then it will be in the user object of the event object. If you don't have permission then you probably can't get it for that person. — Cooper, Jan 11 '23 at 19:32

score 0 · Answer 1 · answered Jan 11 '23 at 19:43

The onEdit(e) function is a simple trigger and thus runs in a restricted context where the identity of the user at the keyboard is usually not available:

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

See Session.getActiveUser() and Session.getEffectiveUser().

How can I use Google Sheets script to capture a user's email, name, or any information when a checkbox is selected?

1 Answers1