Basic Gmail statistics

gmailstats.gs

// 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:[email protected]";
  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);
  
}

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