Google Apps Script: Get current user email from a spreadsheet Add-On

Retrieve the current user email using App Script in your Spreadsheet Add-On is a little bit tricky. You can easily use the Session object to get it but in some cases, this object will return undefined.

Current user email using Session object

var userEmail = Session.getActiveUser().getEmail();

So, you need to find a workaround. The workaround could be to use the PropertyService object.

Current user email using PropertiesService object

var userEmail = PropertiesService.getUserProperties().getProperty('userEmail');

Why not, that’s another way to get it but not 100% too. If you want to secure it and combine another way to get it, you need to work with the sheet protection.

Current user email using spreadsheet protection

var protection = SpreadsheetApp.getActive().getRange('A1').protect();
protection.removeEditors(protection.getEditors());
var editors = protection.getEditors();
if (editors.length === 2) {
    var owner = SpreadsheetApp.getActive().getOwner();
    editors.splice(editors.indexOf(owner), 1);
}
userEmail = editors[0];
protection.remove();
PropertiesService.getUserProperties().setProperty('userEmail', userEmail);

Free to play

The full method, ready to use, capy-paste in your code and have fun:

function getCurrentUserEmail() {
    var userEmail = Session.getActiveUser().getEmail();
    if (userEmail === '' || !userEmail || userEmail === undefined) {
        userEmail = PropertiesService.getUserProperties().getProperty('userEmail');
        if (!userEmail) {
            var protection = SpreadsheetApp.getActive().getRange('A1').protect();
            protection.removeEditors(protection.getEditors());
            var editors = protection.getEditors();
            if (editors.length === 2) {
                var owner = SpreadsheetApp.getActive().getOwner();
                editors.splice(editors.indexOf(owner), 1);
            }
            userEmail = editors[0];
            protection.remove();
            PropertiesService.getUserProperties().setProperty('userEmail', userEmail);
        }
    }
    return userEmail;
}