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.
Recent Comments