CEO-ing with Google Apps Script | Blog | Fivetran

When Fivetran started almost 10 years ago, I was our first programmer. I was fired from the software engineering team a long time ago, but I still occasionally find a reason to write a little code. 

I recently discovered Google Apps Script, and I’ve used it to solve a big problem I have as the CEO of a growing company: the avalanche of email assaulting my inbox, all day every day. I’m a loyal user of Gmail since the public beta. I even have a thread with a group of college friends that has been continuously active since 2005, but that is a story for another day. I hear good things about other email clients like Superhuman, but my personal goal is to keep using Gmail and make it work better for me.

Gmail’s tabbed inbox does a great job of keeping 95% of emails out of the primary tab, but because of the sheer volume of emails I receive, I found that the search feature of Gmail became unusable due to the sheer number of old unread emails in the other tabs. This was the first problem I attacked, by writing a simple script that deletes all emails outside the primary tab after 30 days:

const queries = [
 "category:social older_than:30d",
 "category:promotions older_than:30d",
 "category:updates older_than:30d",
 "category:forums older_than:30d",
];
function myFunction() {
 for (let query of queries) {
   for (let thread of GmailApp.search(query)) {
     let subject = thread.getFirstMessageSubject();
     thread.moveToTrash();
     console.log(`Deleted ${subject} because it matched ${query}`);
   }
 }
}

I set this script to run every hour, and this has fixed my search problems. 

The second problem I set out to solve was the amount of cold solicitation emails I get from recruiters and sales reps. I’m sure that some of them have great products to tell me about, but to the extent that I am still involved in evaluating software and services, reading cold emails is not how I do it. My solution to this problem was a little more complicated.

First, I created a label called “Triage” and a hidden label called “SkipTriage.” Then I created the following script and set it to run every minute:

function myFunction() {
  let triageLabel = GmailApp.getUserLabelByName("Triage");
  let skipTriageLabel = GmailApp.getUserLabelByName("SkipTriage");
  for (let thread of GmailApp.search("category:primary is:unread -label:SkipTriage", 0, 10)) {
    // If this message was already triaged and has been moved back to the inbox, remove the triage label.
    if (thread.getLabels().some(label => label.getName() == "Triage")) {
      console.log(`SkipTriage ${thread.getFirstMessageSubject()}`);
      thread.removeLabel(triageLabel);
      thread.addLabel(skipTriageLabel);
      continue;
    }
    // Check if any of the messages were sent by someone I have corresponded with.
    let knownSender = false;
    for (let message of thread.getMessages()) {
      knownSender = knownSender || GmailApp.search(`in:sent to:${message.getFrom()}`).length > 0;
    }
    // If the sender is unknown, skip the inbox and add the "Triage" label.
    if (!knownSender) {
      console.log(`Triage ${thread.getFirstMessageSubject()}`);
      thread.moveToArchive();
      thread.addLabel(triageLabel);
    }
  }
}

This script looks at every new email that arrives in my inbox. If this email is from someone I’ve never emailed before, it removes it from the inbox and applies the “Triage” label. I have a fantastic executive assistant who has access to my email, and several times a day she checks each email in this category and either moves it back to the Primary tab, or banishes it to the Promotions tab. The SkipTriage label is used by the script to prevent an infinite loop, and I also set Gmail filters that apply SkipTriage to emails that I know should go straight to the inbox.

This system won’t work for most people, because most people don’t have executive assistants. But it’s rescued my inbox, and it’s fun to write a little code again.

Head to our blog to read more articles by Fivetran CEO George Fraser.