by on 2017-10-17

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