Google Sheets: Auto Populate Emails to Match Names

This is a Google Workspace Power Hack to Grab Email Addresses from Your Directory

Ever need to add email addresses to data sets that are missing them?

Set up

1. Put your data in a Google Sheet

2. Column A needs to be empty

3. In Column B, ensure the first and last names are added. For example, “John Smith”

4. Open a new Apps Script

5. Name it whatever you want

6. Add these services

   

   
   

7. Paste the code and save it

8. Run the code and complete all the permissions steps

// This script retrieves email addresses from the Google Workspace Admin Directory based on full names
// provided in Column B of a Google Sheet. Column B must contain full names in the format "FirstName LastName"
// (e.g., "John Smith"). If full names are not available in a formula like `=FirstName&" "&LastName` can be used
// to construct them in Column B before running the script.
//Emails are written to Column A, make sure it is empty from A2

function lookupEmails() {
  // Declare a variable to store the unique identifier of the Google Spreadsheet
  // Placeholder: Replace with the actual Google Sheet ID found in the URL of your spreadsheet
  var spreadsheetId = "<YOUR_SPREADSHEET_ID>";

  // Access the Google Spreadsheet using the provided ID and select the specific sheet
  // Placeholder: Replace with the actual sheet tab name where your data resides
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName("<YOUR_SHEET_TAB_NAME>");

  // Retrieve all values in Column B starting from row 2 down to the last row with data
  // getRange("B2:B" + sheet.getLastRow()) defines a range from B2 to the last row
  // getValues() extracts the data as a 2D array
  var names = [];
  names = sheet.getRange("B2:B" + sheet.getLastRow()).getValues();

  // Initialize an empty array to store the retrieved email addresses
  var emails = [];

  // Iterate through each name in the names array to perform the email lookup
  for (var i = 0; i < names.length; i++) {
    // Extract the name from the current row
    // names[i][0] accesses the first (and only) column of the i-th row
    var name = names[i][0];

    // Check if the name is empty or blank to avoid unnecessary processing
    // If empty, skip to the next iteration
    if (!name) continue;

    // Attempt to search for the user in the Google Workspace Admin Directory
    try {
      // Query the Admin Directory for users matching the full name
      // The query parameter searches by name, and "my_customer" specifies the domain
      var users = AdminDirectory.Users.list({
        query: "name:" + name, // Search for users with the given name
        customer: "my_customer", // Represents the Google Workspace domain
        maxResults: 1 // Limit to one result for efficiency
      });

      // Check if any users were found in the search results
      if (users.users.length > 0) {
        // Extract the primary email address of the first matching user
        var email = users.users[0].primaryEmail;
        // Add the email to the emails array as a single-element array
        emails.push([email]);
      } else {
        // If no user is found, add a placeholder indicating no email was found
        emails.push(["No Email"]);
      }
    } catch (e) {
      // Handle any errors that occur during the Admin Directory lookup
      // Log the error message for debugging purposes
      Logger.log("Error with user lookup: " + e.message);
      // Add an error placeholder to the emails array
      emails.push(["Error"]);
    }
  }

  // Write the retrieved emails to Column A, starting from row 2
  // getRange(2, 1, emails.length, 1) defines the target range:
  // - Row 2, Column 1 (A), height of emails.length, width of 1
  // setValues(emails) populates the range with the emails array

  sheet.getRange(2, 1, emails.length, 1).setValues(emails);
}

Enjoy the time savings! This is a good function to build on you can do alot with this type of logic.