Christian Genco

Gmail Log

I've spent a lot of time thinking about how to be more productive, especially around email. The biggest part of improving these kinds of systems is tracking what you want to improve, so I wrote a Google Apps script that shows me how well I'm doing at email.

This script also fits into my Spark email flow by changing emails labeled _star to have an actual star (which is the one thing I don't think Spark can do yet).

Here's how to install this tracking in your own Gmail account:

1. Visit Google Sheets and create a new spreadsheet #

2. Name that spreadsheet "Gmail Log", and name the first four cells in the first row timestamp, starred emails, oldest, and inbox count #

3. Copy that spreadsheet's URL (select the URL bar, then Edit / Copy or CMD/CTRL + C) #

4. Create a new Google Script, name it "Gmail Log", and paste in this code: #

function logStarred() {
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetURL);
  var sheet = spreadsheet.getSheetByName("StarredCount");

  // give any email with the _star label an actual star
  var _star = GmailApp.getUserLabelByName('_star');
  var threads = _star.getThreads();
  for(var i in threads){
    var thread = threads[i];
  // ...then take the _star label away

  // mark all starred read threads as unread
  var starredReadThreads ="is:starred is:read");
  for(var i=0; i<starredReadThreads.length; i++){

  // calculate the oldest message age
  var now = new Date();
  var starred = GmailApp.getStarredThreads();
  var oldestStarred = starred[starred.length-1];
  var oldestStarredDate = (oldestStarred ? oldestStarred.getLastMessageDate() : now);
  var oldestDaysAgo = (now - oldestStarredDate) / 1000 / 60 / 60 / 24;

  // log it to the spreadsheet
  sheet.appendRow([new Date(), GmailApp.getStarredUnreadCount(), oldestDaysAgo, GmailApp.getInboxUnreadCount()]);

5. Replace YOUR_SPREADSHEET_URL_GOES_HERE at the top with the URL of the spreadsheet you copied in step 3 #

6. Click the play icon to run your script and make sure it's working so far. A new row should appear on your spreadsheet #

7. Back in your Google Script, set up this function to run automatically by opening Edit / Current project's triggers, then add a Time-driven Hour-timer to run every 6 hours #

8. Back in your Google Sheet, select the four columns at the top (click A, then shift+click D so all four columns turn blue), then open Insert / Chart #

9. Change the type to Line chart #

10. Done :D #