Email analysis using Gmail and Google Sheets
There’s a lot of information trapped in your email and you could try to get at that manually in your inbox or through some free or paid tool, but sometimes it’s best just to pull everything into a spreadsheet and question the data yourself.
What information is lurking in your email? Well, beyond the obvious message content there is also the subject line, the To, From, and Cc fields, as well as date stamps and a few others things. This metadata is useful when trying to answer certain questions that involve a complexity or quantity that confounds other methods.
Until recently I’d gotten by with tools or manually manipulating my inbox. Then my boss asked for a report on a huge email blast (1,000+ emails) that someone else sent involving lots of responses from multiple people at multiple companies. I couldn’t’ find a tool that did what I needed and a manual method would risk errors and take hours. I used the following snippet of code to pull all those emails into a Google Sheet after which it only took a few minutes to find the answers I needed.
Step-by-step for pulling email info from Gmail to Google Sheets.
- Create a new label in Gmail (Gmail’s version of folders) and add the label/move the emails to the folder that you want to pull into Sheets. (I created an “Email Analysis” label/folder).
- Open a new blank Google Sheet. Name it whatever you want.
- In Google Sheets go to “Tools” in the menu bar and select “Script editor”.
- A new tab containing the Google Script Editor will open. Now don’t be shy if you consider yourself “non-technical”. If you can use basic excel you’ll be fine.
- Clear the auto-populated “my function” and paste the following function into the Script editor.
- In the third line of code change “Email Analysis” to whatever label you created.
function pullEmailData() {
var ss = SpreadsheetApp.getActiveSheet();
var label = GmailApp.getUserLabelByName("Email Analysis");
var threads = label.getThreads();
for (var i = 0; i < threads.length; i++)
{
var messages = threads[i].getMessages();
for (var j = 0; j < messages.length; j++)
{
var to = messages[j].getTo();
var from = messages[j].getFrom();
var sub = messages[j].getSubject();
var date = messages[j].getDate();
ss.appendRow([to, from, sub, date]);
}
}
}6. The script will ask you to save it (name it whatever you want). And will ask for permissions.
7. Give the script a minute or two to run. You should see a little yellow notification similar to the “email sent” one you see in gmail telling you the program is running.
7. You’re done! Click back into your Google Sheet and get to work running your analysis.
Speaking of analysis . . .
Want more/different data from your emails?
Try tinkering with the script. Head over to Google’s documentation to see what else you can can do.
For example, if you wanted to pull in the body of the email along with To, From, Subject, and Date you could make the following changes, adding what’s in bold:
var date = messages[j].getDate();var body= messages[j].getPlainBody();ss.appendRow([to, from, sub, date, body]);
}
}
}
And if you wanted to remove the label from the emails after you pull their info Google Sheets you could add this code at the end (in bold).
var from = messages[j].getFrom();
var sub = messages[j].getSubject();
var date = messages[j].getDate();
ss.appendRow([to, from, sub, date]);
}
threads[i].removeLabel(label);
}
}Analyzing the data in Google Sheets
Google Sheets has a lot of great built in functions. And you can always download the data as an excel file if you prefer working in MS Excel.
Need some help getting started? Click “filter” under the Data tab. Take a look at Google’s list of Sheet functions. Look up the functions AND, IF, OR, SPLIT, VLOOKUP.
You also might find useful . . .
RegexMatch — a function that lets you search for an exact text match (word or phrase). It does not work for numbers. The syntax is:
REGEXMATCH(text, regular_expression)
Text is the thing you are searching through, so probably a cell in the column you’re interested in searching.
Regular_expression is the word or phrase you are looking for in your data, like “blue dog”.
Unique — a function that will give you a list of the select cells that’s filtered by unique entries. Useful for remove duplicates from a list. The syntax is:
UNIQUE(range)
Range would be something like A2:A45, or the whole column, A:A.
Good luck! Hopefully this will save you as much time as it did for me.
