Created
September 19, 2024 18:48
-
-
Save cdnsteve/b485930cf0b9b58a7b94c81659a4a992 to your computer and use it in GitHub Desktop.
App Script that looks up users email address in gmail by name
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| function lookupEmails() { | |
| // Replace 'Sheet1' with your actual sheet name | |
| var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); | |
| var namesRange = sheet.getRange('A2:A'); // Adjust to your names column range | |
| var names = namesRange.getValues(); | |
| // Iterate over the names | |
| for (var i = 0; i < names.length; i++) { | |
| if (names[i][0] !== '') { // If there's a name in the cell | |
| var userEmail = getUserEmailByName(names[i][0]); | |
| // Place the found email in the adjacent column (B in this case) | |
| if (userEmail) { | |
| sheet.getRange(i + 2, 2).setValue(userEmail); // Adjust the column accordingly | |
| } else { | |
| sheet.getRange(i + 2, 2).setValue("Not found"); | |
| } | |
| } | |
| } | |
| } | |
| function getUserEmailByName(fullName) { | |
| try { | |
| // Call the Admin Directory API to get users' information | |
| var users = AdminDirectory.Users.list({ | |
| domain: 'yourdomain.com', // Replace with your Google Workspace domain | |
| query: `name:${fullName}` | |
| }); | |
| if (users.users && users.users.length > 0) { | |
| return users.users[0].primaryEmail; | |
| } else { | |
| return null; | |
| } | |
| } catch (e) { | |
| Logger.log('Error: ' + e.toString()); | |
| return null; | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment