I interact with a lot of people using Gmail messages and Google Calendar events. I'd like to collect the contact info of these individuals in Google Contacts as my de-facto CRM. Adding these records manually takes too much time: I'd like this done programmatically. Thing is, I don't want to mine every email and invite: some of these are transactional emails or one-off meetings. Instead, I'd like to have the contact info aggregated into a Google Sheet where I can visually inspect the rows, mark which ones to save and which ones to ignore.
This post will show you how to create such a system using Google Apps Script.
To get started, create a new Google Sheet. Rename its tab to "New users." Add two more tabs: "Ignored users" and "Ignored domains." The second tab will contain email addresses of users we don't want to save and don't want to pull again from messages and events. The third tab will contain domains for which we don't want to capture any email address.
Open Apps Script and rename "Code.gs" to "Common.gs." Here we will write a few utilities that we will use in our other script files. In Common.gs, add the followning code:
const g = {
nuSheetName: 'New users',
iuSheetName: 'Ignored users',
idSheetName: 'Ignored domains',
DAYS_BACK: 3,
CALENDAR_NAME: 'your-calendar-name',
};
function init_() {
if (g.ss) {
return;
}
g.ss = SpreadsheetApp.getActive();
g.nuSh = g.ss.getSheetByName(g.nuSheetName);
g.iuSh = g.ss.getSheetByName(g.iuSheetName);
g.idSh = g.ss.getSheetByName(g.idSheetName);
}
function onOpen(e) {
const ui = SpreadsheetApp.getUi();
ui.createMenu('🟢 Automation')
.addItem('Fetch Gmail/Calendar contacts', 'fetchNewContacts')
.addSeparator()
.addItem('Sync contacts', 'syncContacts')
.addToUi();
}
function c_(text) {
return text.toString().toLowerCase().trim();
}The global g object holds a few pieces of info that our functions will reference time and again: the names of our sheet tabs, the numbers of days back we want to search for messages and events (this helps the script execute faster), and your calendar name.
The init_ function will be called from our different functions to initialize the script and avail the different tabs.
onOpen creates two custom menu options: one to fetch new contacts to the "New users" tab, and the other to process any row in the tab whose "Action" dropdown has been selected (more on that later).
Finally, c_ will simply standardize our strings so that we can make apples-to-apples comparisons.
Next, create a new script file inside the IDE (let's call it Fetch.gs). Inside the file, define the fetchNewContacts function that we specified inside our onOpen function when creating the custom menu. This function is a heavy-lifter; it's supposed to perform the following:
- Pull contacts from Gmail
- Pull contacts from Google Calendar
- Remove duplicates
- Get the emails and domains we previously stored in the sheet
- Remove new users that were previously ignored
- Remove new users that have ignored domains
- Get existing connections from Google Contacts
- Remove these connections from the new users so we don't duplicate 'em
- Save the new contacts to the "New users" tab
- Add "Action" dropdown for every new user
Like I said, heavy lifting. Let's create the individual functions that perform some of the above actions and then reference them inside fetchNewContacts.
function getGmailContacts_() {
let after = new Date();
after.setDate(after.getDate() - g.DAYS_BACK);
after = after.toLocaleDateString('en-US');
const query = `after: ${after}`;
const threads = GmailApp.search(query);
const messages = threads.flatMap((thread) => thread.getMessages());
let users = messages.flatMap((message) => {
const from = [message.getFrom()];
const to = message.getTo().split(',');
const cc = message.getCc().split(',');
const bcc = message.getBcc().split(',');
return from.concat(to, cc, bcc);
});
users = users.filter(Boolean);
users = users.map((user) => splitNameEmail_(user));
return users;
}Above, we search GmailApp for any email messages sent after today - g.DAYS_BACK; in our case three days ago. We get back thread objects, where each thread can contain multiple messages, so we flatMap the threads into messages, by calling getMessages on each thread. We then pull out all the contacts: sender and recipients (including to, cc, and bcc). We filter out empty rows and then we split the name and email. Add the splitNameEmail_ function to the "Common.gs" file:
function splitNameEmail_(text) {
let name = '';
let email;
if (text.includes('<')) {
[name, email] = text.split('<');
email = email.slice(0, -1);
} else {
email = text;
}
name = name.replace(/"/g, '');
return [name, email];
}An email address can appear either as "john.smith@mysite.com" or as "John Smith <john.smith@mysite.com>. So we split the text if it includes the < sign. Otherwise, name is an empty string and email gets the entire text. We clean out any quotes as wll as the > sign from the end of the email.
Back in "Fetch.gs", let's get the event recipients:
function getCalendarContacts_() {
let after = new Date();
const before = new Date(after.getTime() + 1000 * 60 * 60 * 24 * 7);
after.setDate(after.getDate() - g.DAYS_BACK);
const calendar = CalendarApp.getCalendarsByName(g.CALENDAR_NAME)[0];
const events = calendar.getEvents(after, before);
const guests = events.flatMap((event) => event.getGuestList(true));
const users = guests.map((guest) => [guest.getName(), guest.getEmail()]);
return users;
}We're doing a similar exercise here: we get the calendar by name, and then pull out the events that are scheduled inside the window of three days ago and seven days from now. We flatmap the guests out of the events using event.getGuestList, which accepts true to include the event owner. We then convert the array of guests to users by pulling their names and email addresses.
Next on the agenda is to pull out all the email addresses and domains that are stored in our Google Sheet. We don't want to list those in our New users tab. So we will extract the email addresses from the three tabs and return them:
function getSaved_() {
const nuData = g.nuSh
.getRange('B2:B')
.getValues()
.flat()
.filter(Boolean)
.map((email) => c_(email));
const iuData = g.iuSh
.getRange('B2:B')
.getValues()
.flat()
.filter(Boolean)
.map((email) => c_(email));
const idData = g.idSh
.getRange('A2:A')
.getValues()
.flat()
.filter(Boolean)
.map((email) => c_(email));
return {
savedUsers: nuData.concat(iuData),
savedDomains: idData,
};
}Let's get the email addresses from our existing Google Contacts connections; we will later remove new users that have these addresses, so we don't create duplicate connections. Note that you must enable the "People" Advanced Service in the Google Apps Script IDE in order to use it below:
function getGoogleContacts_() {
let gContacts = [];
let nextPageToken = null;
do {
const resp = People.People.Connections.list('people/me', {
personFields: 'emailAddresses',
pageSize: 100,
pageToken: nextPageToken,
});
const connections = resp.connections;
if (connections) {
const emailArrays = connections.flatMap(
(connection) => connection.emailAddresses
);
const emailAddresses = emailArrays.flatMap((emailArray) => {
if (emailArray) {
return c_(emailArray.value);
}
});
gContacts = gContacts.concat(emailAddresses);
nextPageToken = resp.nextPageToken;
}
} while (nextPageToken);
gContacts = gContacts.filter(Boolean);
gContacts = [...new Set(gContacts)];
return gContacts;
}We use a do/while loop to iterate over the contacts requests, each time pushing a page token that tells Google which page of the results we're interested in. We request 100 results at a time, so if there are more than 100 connections then the loop will repeat itself, sending a new token until no new token is available on the response object. We traverse the array of connections, get the email array out of each connection, and get the email addresses that are held in the value keys. Outside the loop we de-dup the addresses using a set.
Now that we have all of the functions, we can go back into fetchNewContacts and collate our new users:
init_();
// Pull contacts from Gmail
const gmailContacts = getGmailContacts_();
// Pull contacts from Calendar
const calendarContacts = getCalendarContacts_();
let newUsers = gmailContacts.concat(calendarContacts);We want to remove duplicates now, but we want to do it in a way that captures the name of the user if it exists. Sometimes the email will not have a corresponding name, so we want to iterate through the users to get the name if it's there:
// remove duplicates
const userObj = {};
newUsers.forEach(([newName, newEmail]) => {
if (!userObj[newEmail]) {
userObj[newEmail] = newName;
} else {
if (newName) {
userObj[newEmail] = newName;
}
}
});
newUsers = Object.entries(userObj).map(([email, name]) => [name, email]);Now can we get the data from the three tabs – as well as from Google Contacts – and de-dup:
// Get saved users from sheet
const { savedDomains, savedUsers } = getSaved_();
// Remove new users with ignored domains
if (savedDomains.length > 0) {
newUsers = newUsers.filter((newUser) => {
const email = c_(newUser[1]);
return !savedDomains.some((savedDomain) => email.includes(savedDomain));
});
}
// Remove new users previously ignored
if (savedUsers.length > 0) {
newUsers = newUsers.filter((newUser) => {
const email = c_(newUser[1]);
return !savedUsers.includes(email);
});
}
// Get saved users from Google Contacts
const googleContacts = getGoogleContacts_();
// Remove new users previously stored in Contacts
if (googleContacts.length > 0) {
newUsers = newUsers.filter((newUser) => {
const email = c_(newUser[1]);
return !googleContacts.includes(email);
});
}We iterate over new users and if we find a saved domain that appears in the user's email address then we filter the user out. Same goes with ignored users and Google Contacts: if they math then they're out.
Last but not least: we can write the new users to the tab if there are any left after all the de-duping:
// Save new contacts to the sheet
if (newUsers.length === 0) {
return;
}
const nextRow = g.nuSh.getLastRow() + 1;
g.nuSh.getRange(nextRow, 1, newUsers.length, 2).setValues(newUsers);
// Add action dropdowns
const rule = getValidationRule_();
const ruleArr = new Array(newUsers.length).fill([rule]);
g.nuSh
.getRange(`C${nextRow}:C${nextRow + newUsers.length - 1}`)
.setDataValidations(ruleArr);
SpreadsheetApp.flush();We're adding the users, as well as the dropdown selectors for each of them. We have a quick getValidationRule_ function that we can add to "Common.gs":
function getValidationRule_() {
return SpreadsheetApp.newDataValidation()
.requireValueInList(['Save user', 'Ignore user', 'Ignore domain'])
.setAllowInvalid(false)
.build();
}We now have everything we need to pull net-new contacts into our Google Sheet. In the next post I'll review how to push the data to Google Contacts and how to update the latter two tabs with ignored users and domains.