2

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?

migrated from superuser.com Mar 11 '16 at 21:25

This question came from our site for computer enthusiasts and power users.

3

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));
} 
  • Thanks a bunch! Here's the code I created based on your answer: 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.fullName); row++; currentCell = sheet.getActiveSheet().getRange(row, 2); } } – user569715 Mar 12 '16 at 21:21
  • 1
    @user569715 Thanks for sharing the code that you created. Consider to add it as an answer including a brief description of it. – Rubén Mar 13 '16 at 19:50
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.