Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Basic Gmail statistics
// To load this script, open a Google Sheet (yeah, weird I know), then select "Tools->Script Editor"
// From there, past this content into a script. You can set up triggers to run this script every day at a certain time
// by selecting Resources -> Triggers.
// I recommend you set the trigger to every 5-10 minutes. This will let the batches complete. If the trigger is too infrequent, it wont have time to finish.
// https://developers.google.com/apps-script/reference/gmail/
// For large inboxes (the ones you want to analyze) gmail will rate limit you.
// They recommend adding a sleep to call less, but then your exec time will be too long.
// To solve this, we run in batches. This is the batch size. Decrease this if exec time is too long.
var BATCH_SIZE = 50;
// Helper function to convert JS date objects to something gmail search understands
function dateToGmailString(date){
return date.getFullYear()+"/"+(date.getMonth()+1)+"/"+date.getDate();
}
// Set a hash element to 0 if it's not set, otherwise increment it
function setOrIncrementKey(hash, key)
{
if (key in hash)
hash[key]++
else
hash[key]=0
}
// Return a sorted tuple (tuple[i][0]=key, tuple[i][1]=value) from a hash
function sortHashByKey(obj)
{
var tuples = [];
for (var key in obj) tuples.push([key, obj[key]]);
tuples.sort(function(a, b) {
a = a[1];
b = b[1];
return a > b ? -1 : (a < b ? 1 : 0);
});
return tuples
}
// Raw query to gmail api, collecting conversations in a time range
function getConversationsInRange(start, end, batch_start, batch_size)
{
var query = "after:"+dateToGmailString(start)
if (end != null)
query += " before:"+dateToGmailString(end);
query += " in:anywhere -label:sms -label:call-log -label:chats -label:spam -filename:ics";
query += " -from:maestro.bounces.google.com -from:unified-notifications.bounces.google.com -from:docs.google.com";
query += " -from:group.calendar.google.com -from:apps-scripts-notifications@google.com";
query += " -from:sites.bounces.google.com -from:noreply -from:notify -from:notification";
conversations = GmailApp.search(query, batch_start, batch_size);
return conversations;
}
// Extract statistics from a list of conversations.
function getStats(start, stats, conversations)
{
stats.conversations += conversations.length
for (i=0; i < conversations.length; i++)
{
conversation = conversations[i];
messages = conversation.getMessages();
Utilities.sleep(1000); // prevent rate limitting
stats.messages += messages.length;
for (m=0; m < messages.length; m++)
{
message = messages[m]
date = message.getDate();
// Some therads have messages that weren't sent today
if (date > start)
{
// Count messages sent directly to user
to = message.getTo().replace(/"[^"]*"/g,'');
email_regex = new RegExp(Session.getActiveUser().getEmail(), 'i');
if (to.search(email_regex) != -1) stats.direct_sent++
// Collect sender
setOrIncrementKey(stats.senders, message.getFrom());
// Collect label statistics
labels = message.getThread().getLabels();
if (labels.length == 0){
setOrIncrementKey(stats.labels, 'unlabeled')
}
for (j=0; j < labels.length; j++)
{
label = labels[j].getName();
setOrIncrementKey(stats.labels, label)
}
}
}
}
return stats;
}
// Select get conversations in the specified date range, and collect statistics on them.
function collectDateRange(start, end)
{
var userProperties = PropertiesService.getUserProperties();
var stats_prop = userProperties.getProperty("stats");
if (stats_prop == null) // FIXME: make this dict indexed by day
{
var stats = {};
stats.conversations = 0
stats.messages = 0
stats.direct_sent = 0
stats.labels = {}
stats.senders = {}
stats.subjects = {}
stats.range = 0
}else {
var stats = JSON.parse(stats_prop);
}
conversations = getConversationsInRange(start, end, stats.range, BATCH_SIZE);
stats.range += BATCH_SIZE
stats = getStats(start, stats, conversations)
// If the number of conversations is less then the batch size, we are done!
if (conversations.length < BATCH_SIZE)
{
userProperties.deleteProperty("stats"); // we have to clean up because we have a limited amount of storage
userProperties.setProperty("last_ran", dateToGmailString(start) +"_"+dateToGmailString(end) );
sendReport(stats);
}
else
{
userProperties.setProperty("stats", JSON.stringify(stats) );
// MailApp.sendEmail(Session.getActiveUser().getEmail(), "Gmail Stats - incremental", JSON.stringify(stats)); // for debug
}
}
// Used to email the report
function sendReport(stats)
{
body = "Yesterday's statistics:\n"
body += "\tThere were "+stats.conversations+" active conversations with a total of "+stats.messages+" messages\n";
body += "\tSent directly to you:"+stats.direct_sent+"\n";
body += "\tTop senders:\n"
senders = sortHashByKey(stats.senders)
for (i=0; i < 10; i++)
body += "\t\t"+senders[i][0]+" sent "+senders[i][1]+"\n"
body += "\tCount by label:\n"
for (var label in stats.labels)
body += "\t\t"+label+": " + stats.labels[label] +"\n";
MailApp.sendEmail(Session.getActiveUser().getEmail(), "Gmail Stats", body);
}
// Wrapper to collect stats for just yesterday
function collectYesterday()
{
var userProperties = PropertiesService.getUserProperties();
yesterday = new Date(new Date().setDate(new Date().getDate()-2));
today = new Date();
today_string = dateToGmailString(yesterday) +"_"+ dateToGmailString(today)
last_ran = userProperties.getProperty("last_ran");
// userProperties.deleteProperty("stats");
if (today_string != last_ran) // only run once a day
collectDateRange(yesterday, today);
}
@J00hnny

This comment has been minimized.

Copy link

@J00hnny J00hnny commented Aug 3, 2015

Hello,

Thank you for your work. When I tried to run your script i got some errors:

TypeError: Cannot read property "conversations" from undefined. (line 151, file "Code")

TypeError: Cannot read property "direct_sent" from undefined. (line 152, file "Code")

TypeError: Cannot read property "senders" from undefined. (line 155, file "Code")

ReferenceError: "senders" is not defined. (line 157, file "Code")

TypeError: Cannot read property "labels" from undefined. (line 160, file "Code")

Could you please help me with this?

Thank you in advance

Janos

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment