Skip to content

Instantly share code, notes, and snippets.

@leoherzog
Created Mar 18, 2022
Embed
What would you like to do?
Find emails in your Google Directory for names in a Google Sheet
/*
* This is a script to look through column x for names,
* search your directory for email addresses for that name,
* and print them into column y.
*
* To get started:
* 1. Open an existing Google Sheet or create a new one (https://sheets.new) and give it a name.
* 2. Click Extensions → Apps Script and give that new Apps Script project a name (e.g. "Lookup Email from Name").
* 3. Paste all of this code into that project.
* 4. Specify if it has a header row on line 19 of the code (true or false), as well as the name and email column letters on lines 20 and 21.
* 5. Click ➕ Services on the left sidebar, scoll down and select "Peopleapi", and click Add.
* 6. Click "▷ Run" in the top toolbar, allow authorization, and it should fill in the emails if it can find them!
*
*/
/**
* @OnlyCurrentDoc
*/
const hasHeaderRow = true;
const nameColumn = 'A';
const emailColumn = 'B';
function findEmails() {
let sheet = SpreadsheetApp.getActiveSheet();
let names = sheet.getRange(nameColumn + ':' + nameColumn).getValues();
for (let i in names) {
if (hasHeaderRow && i == 0) continue;
if (!names[i][0]) continue;
try {
console.log('Checking ' + names[i]);
sheet.getRange(emailColumn + (new Number(i) + 1)).setValue(GETEMAILFROMNAME(names[i][0]));
}
catch(e) {
console.error(e);
}
Utilities.sleep(200); // Peopleapi quota
}
SpreadsheetApp.flush();
}
// this is the only function you _should_ need, if freaking advanced services and oauth worked from custom the sheet formulas context
function GETEMAILFROMNAME(name) {
if (!name) return name;
let people = People.People.searchDirectoryPeople({
"query": name,
"readMask": "emailAddresses",
"sources": "DIRECTORY_SOURCE_TYPE_DOMAIN_PROFILE"
});
// console.log('Found ' + people.totalSize + ' matches');
return people?.people?.[0]?.emailAddresses?.[0]?.value;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment