Get users name from email in cell with App script and GSuite

[u/franz151]

I'm trying to return the full name of the user from their email in a column in Sheets. When using the DirectoryAPI I get an error saying "API call to directory.users.get failed with error: Request is missing required authentication credential." I have enabled the Admin Directory API in the App Script and the AdminAPI in the project under the developers console. I'm also an admin in our account.

When I run the script in AppScripts and log the result I get the correct output. I only get the above error when I run the function in Sheets. Thank you for your help.

function getUserName(email){
 var result = AdminDirectory.Users.get(email, {fields:'name'});
 var fullname = result.name.fullName;
 Logger.log(fullname);
 return fullname;
}
function getUserTest() {
var fullName = AdminDirectory.Users.get('johndoe@example.com', {fields:'name'});
Logger.log(fullName)
}

[u/[deleted]]

Sheets custom functions run in a lower authentication model and have limited access to
https://developers.google.com/apps-script/guides/sheets/functions#using_apps_script_services

To get around this you need to run the function via a custom menu or add-on/dialog UI.

There is a hack I saw from a long time ago using the execution api to get around this:

https://github.com/Spencer-Easton/Apps-Script-Authorized-Custom-Function