Google Sheets: how to resolve emails to user names

Question

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  [email protected]         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?

Answer 1

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 = '[email protected]';
  var user = AdminDirectory.Users.get(userEmail);
  Logger.log('User data:\n%s', JSON.stringify(user, null, 2));
} 

Answer 2

Using Rubén's answer, here's the code I created:

function getUser() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var row = 2; var currentCell = sheet.getActiveSheet().getRange(row, 2);
  while (currentCell.getValue() != "") { 
    currentCell.setNote(AdminDirectory.Users.get(currentCell.getValue()).name.fullNa‌​me);
    row++; 
    currentCell = sheet.getActiveSheet().getRange(row, 2); 
  }
}

It basically goes through all email addresses in the second column of the spreadsheet and inserts a note containing the user's full name for cells that contain domain email addresses. I'll improve this code in the future as it's quite barebones and might put it on the Marketplace.