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