![](https://i.imgur.com/EwhsZEV.png)
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 #
![](https://i.imgur.com/XMcip4x.png)
2. Name that spreadsheet "Gmail Log", and name the first four cells in the first row timestamp
, starred emails
, oldest
, and inbox count
#
![](https://i.imgur.com/eDao3b4.png)
3. Copy that spreadsheet's URL (select the URL bar, then Edit / Copy
or CMD/CTRL + C
)
#
![](https://i.imgur.com/XEgXCCE.png)
4. Create a new Google Script, name it "Gmail Log", and paste in this code: #
function logStarred() {
var spreadsheetURL = "YOUR_SPREADSHEET_URL_GOES_HERE";
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];
thread.getMessages()[0].star();
}
// ...then take the _star label away
_star.removeFromThreads(threads)
// mark all starred read threads as unread
var starredReadThreads = GmailApp.search("is:starred is:read");
for(var i=0; i<starredReadThreads.length; i++){
starredReadThreads[i].markUnread();
}
// 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
#
![](https://i.imgur.com/52t5rkD.png)
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 #
![](https://i.imgur.com/FQ5alst.png)
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
#
![](https://i.imgur.com/hRZ7jtJ.png)
![](https://i.imgur.com/mfPMHQx.png)
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
#
![](https://i.imgur.com/p6U0hIP.png)
9. Change the type to Line chart
#
![](https://i.imgur.com/5NKLGDX.png)