Like most people, I use a great many of the free applications provided by Google. Unlike a lot of people, I like to occasionally collect data on how I’m using some of these applications, especially Gmail.

Unfortunately, there do not appear to be easy ways to collect usage data from Gmail; however, Google does provide a scripting platform, Google Apps Script, that allows you to code against Google’s various APIs and get at most of the data you might be interested in.

Recently, I found myself interested in analyzing the categories of email coming into my inbox. How could I collect simple email properties like date, subject line, and category and write the results to a CSV for further analysis?

Step 1: Launch a new spreadsheet

Navigate to Google Docs in your browser and launch a new spreadsheet. This tutorial was excellent help.

Step 2: Launch the script editor

Following that tutorial, open up a new instance of the Script Editor under the Tools menu. This step is pretty important because it seems to “bind” your scripting work to the new spreadsheet. Initially, I simply started a new scripting project in Apps Script apart from any spreadsheet and found no way to get that work to write to any of my spreadsheets.

Step 3: Code away

In this endeavor, my goal was pretty simple: grab particular email metadata and write the data to a spreadsheet. This required me coding against both the GmailApp API and the Spreadsheet API. Here’s the code I ultimately came up with (I believe this is vanilla JavaScript):

function write_gmail_stats() {
  //get reference to active spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet();
  // get collection of email categorized as "promotions" sent after 31 Mar 2020
  var promos = GmailApp.search('category:promotions after:2020/03/31');
  //iterate through the email collection and write date, subject, and category to spreadsheet
  for (var i = 0; i < promos.length; i++){
    var m = promos[i];
    var d = m.getLastMessageDate();
    var s = m.getFirstMessageSubject();
    sheet.appendRow([d, s, "promotions"]);
  }
  
  var social = GmailApp.search('category:social after:2020/03/31');
  //iterate through the email collection and write date, subject, and category to spreadsheet
  for (var i = 0; i < social.length; i++){
    var m = social[i];
    var d = m.getLastMessageDate();
    var s = m.getFirstMessageSubject();
    sheet.appendRow([d, s, "social"]);
  }
  
  var primary = GmailApp.search('category:primary after:2020/03/31');
  //iterate through the email collection and write date, subject, and category to spreadsheet
  for (var i = 0; i < primary.length; i++){
    var m = primary[i];
    var d = m.getLastMessageDate();
    var s = m.getFirstMessageSubject();
    sheet.appendRow([d, s, "primary"]);
  }
}

This code will take the date, subject, and category name of the three default categories in Gmail and write the results to my spreadsheet. To keep my results modest, I crafted a search query to only look at email from April (this support page was very helpful in figuring out the query I needed). The results worked out pretty well:

Sweet! This certainly beats screen scraping or manual data collecting.