A Simple but Powerful CRM with Gmail, Google Sheets and Apps Script
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:
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
It doesn’t matter what you call your columns or what you put in them. The only important thing is that one column contains raw email addresses. By this I mean email addresses of the format name@domain.com and not “full name” <name@domain.com>. You’ll get to customize which column has the email addresses and which columns contain the good stuff in the script.
Step 2: Create a new script from Tools->Script Editor…
A picture is worth a thousand words.
Step 3: Go to Github and get the code!
You can find the code here: https://github.com/dledge/Google_Sheets_CRM/
Copy the contents of the google-sheets-crm.js file.
Step 4: Go back to your newly created script and paste the code
You should see something like this:
Step 5: Configure two variables
There are two variables at the top of code to customize:
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
In the script editor, there is a menu item that looks like a clock in a call out.
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.
Step 7: Run the script once to confirm permissions
The first time you run this script, Google will prompt you to confirm that it’s okay that this script within the spreadsheet is going to access your gmail account. Note that all of this happens within Google’s nice walled garden and within your account. No data is leaving Google or your account.
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.
Step 8: Get on with your life and start keeping track of your correspondences
As you populate your spreadsheet, you’ll see it update at the rate you specified in step 5. If you have a lot of names in your spreadsheet, you’ll see that not all get updated at once. This is because Google’s Apps Scripts isn’t the fastest engine in the world (although it is awesome) and it times out after 5 minutes. However, if you use this a few times per day, you should always find it up to date.
Also, use conditional formatting to add colors!
Coda
I hope you enjoy the tool and find it useful. It’s a small testament to the age we are living in. Composable platforms and APIs are massive disruptors when used properly.
