Google Sheets – How to Resolve Emails to User Names

google sheetsgoogle-appsgoogle-apps-for-work

We have a Google Apps account for our organization. When you create a Google Form using your Apps account, there is a helpful option to have the form capture the user's email address when the form is submitted. Now, it shows up like this in the resulting spreadsheet:

Timestamp           Username                    Question 1

3/11/2016 22:28:18  username@domain.com         Option 1

However, I'm trying to find a way resolve that email address to a user's first and last name. It would be helpful if there were a formula, or a Google Scripts script, that could add another column that has the user's real name, resolved from the email address. Does anyone know if this is possible?

Best Answer

There isn't a built-in function but there is ad advanced Google Apps Script service -> Admin SDK Directory Service

From the above link:

Get user

This sample gets a user by their email address and logs all of their data as a JSON string.

function getUser() {
  var userEmail = 'liz@example.com';
  var user = AdminDirectory.Users.get(userEmail);
  Logger.log('User data:\n%s', JSON.stringify(user, null, 2));
}
Related Topic