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;
}

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

Jeff Devine 5 July 2017 at 15 h 13 min

Joey,

Thanks so much for that script. It’s an awesome concept and has enabled me to breakthrough a big problem on my shared sheet.

The problem with getting the user’s email via the simple first two options above is that they work fine when the script is called via a button, and also everything looks fine in debugging mode, but the problems come when the script is being called via the OnEdit event – Google doesn’t like giving away the user’s email address unless the owner is using the sheet. No good for a collaborative sheet!

I just made one small adjustment – after the line
var owner = SpreadsheetApp.getActive().getOwner();
I added the line:
owner = String(owner);
as the owner variable wasn’t working properly in the next line unless it was definitely a string.

There’s probably better ways to do it, but it worked for me…! 🙂

Regards, Jeff

Reply ↓

Jeff Devine 5 July 2017 at 15 h 15 min

[Joey – sorry if this has posted twice, but I got an error first time.]

Joey,

Thanks so much for that script. It’s an awesome concept and has enabled me to breakthrough a big problem on my shared sheet.

There’s probably better ways to do it, but it worked for me…! 🙂

Regards, Jeff

Reply ↓