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
Put your data in a Google Sheet
Column A needs to be empty
In Column B, ensure the first and last names are added. For example, “John Smith”
Open a new Apps Script
Name it whatever you want
Add these services
Paste the code and save it
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.