A Simple but Powerful CRM with Gmail, Google Sheets and Apps Script

Dan Ledger
May 12, 2016 · 4 min read

It’s amazing what you can do in this day and age when armed with a composable platform like Google Apps and a bit of Javascript!

Here’s a simple CRM tool built around Google Sheets that I use to help me keep up with people in my life. It allows me to:

  • Keep track of how long it’s been since I’ve emailed my important people
  • Determine if I ever replied to their latest emails
  • Show me if the latest email thread was starred (I usually use this to keep track of threads where there is unfinished business)

For each of my important people I see the following view in Google Sheets:

Image for post
Image for post

Important note: this is a simple tool based on hard binary logic. It doesn’t deal perfectly with the fuzziness of our lives so use it at your own risk!

Step 1: Create a new Google Sheet and fill it with your important people

Image for post
Image for post

Step 2: Create a new script from Tools->Script Editor…

Image for post
Image for post

Step 3: Go to Github and get the code!

Copy the contents of the google-sheets-crm.js file.

Step 4: Go back to your newly created script and paste the code

Image for post
Image for post

Step 5: Configure two variables

COLUMN_WHERE_MAGIC_BEGINS: This is the column where the script should place the the four columns related to when you last contacted this person, who had the last email, etc. Note that the first column has a value of 1 (human) and not 0 (computer). In other words, the 3rd column would have a value of 3.

COLUMN_WITH_EMAIL_ADDRESSES: The column that contains the email addresses. Same rules apply.

Step 6: Set it up to run continuously

Image for post
Image for post

Click on this icon to set the frequency of execution of this script. I run the script every 15 minutes. Don’t make this any lower than 5 minutes.

Image for post
Image for post

Step 7: Run the script once to confirm permissions

To run the script for the first time, click the bug icon in the menu bar of your script. You should get a prompt from Google to confirm permissions.

Image for post
Image for post

Step 8: Get on with your life and start keeping track of your correspondences

Also, use conditional formatting to add colors!

Coda

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store