I was desperate. My startup didn’t have money to spare. And I really needed an organised way to manage our leads. Like every other entrepreneur, I scavenged the internet for a free service. Found one. Used it for a while but eventually hit the free limits.
I was desperate… again. Started scavenging, again. But honestly, there were just too many features on all the CRM systems and I wanted something simple, easy to share and collaborate.
Google sheets! That would be my solution. But oh, was it messy. I tried one sheet with different stages of leads (contacted, pitched, negotiating etc). I tried colour coding them. But I also wanted it in alphabetical order within the colour codes. So I tried using different sheets for the stages, but that meant cutting and pasting each row on a new sheet when there was an update. But then I wanted to know when I updated the stage, so another manual date entry to make. Messy!
So I tried a new tactic — very basic and simple but it solved my problems (at least most of them). The idea was that every time I updated the stage column, I wanted the row to automatically go to the corresponding sheet and record the time that this happened. Simples :)
Below is a sample google sheet that can be downloaded and copied freely by anyone who wants to use it:
Description
There are 8 columns:
- Name — name of your lead
- Website — website of your lead
- Source — how you found the lead
- Type — sometimes leads have a type like investor, client or customer
- Stage — this is a drop-down list of the stages in the CRM pipeline. More information below.
- Deal size — If applicable
- Close Date — when the deal was sealed
- Notes
These columns can be edited as you wish but make sure you update the script as well (more below).
Stages
I have placed 7 stages for the pipeline based on what I have come across, but this is really up to you. They are in a drop-down list for the Stage column.
- Lead
- Contacted
- Demo
- Pitched
- Negotiating
- Closed — Won
- Closed — Lost
This is where the interesting things happen. I have added a script to this particular column which will update the rows based on which stage you select. So if you change a Lead to Contacted, it will automatically move that row to the Contacted sheet.
There are a couple of important things here to note,
- Each stage should have its own sheet and must be named exactly as you want to call the stage. Example: stage is called Meeting then the sheet name must also be Meeting.
- Each stage should be added as an item on the drop-down for the cells in the Stage column and the name must match the sheet name, as above.
Adding a drop-down list of stages
To add a drop-down list on any column in Google sheets,
- First select the column
- Then click on Data in the menu
- Click Data Validation…
- Make sure the cell range is correct
- Under Criteria, select List of items
- Add the list of items separated by commas
Script
Now that everything is set up, here comes the interesting part — adding the script to do the magic.
First let’s open the Script editor
- Click Tools
- And then Script Editor…
- This will open a new page with some sample code
- Remove the sample code and paste the following…
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet(),
sheet = ss.getActiveSheet(),
name = sheet.getName();
var cell = sheet.getActiveCell(),
col = cell.getColumn();
if (col == 8) {
var range = e.range;
range.setNote('Last modified: ' + new Date());
return;
}
if (col != 5) return;
var range = e.range;
range.setNote('Last modified: ' + new Date());
var val = e.value;
var row = cell.getRow();
var sendToSheet = ss.getSheetByName(val);
if (sendToSheet != null) {
var lastRow = sendToSheet.getLastRow();
sheet.getRange(row, 1, 1, 9).moveTo(sendToSheet.getRange(lastRow+1, 1));
sheet.deleteRow(row);
}
}Note: We need the script to run every time someone edits the Stage column (I have also added a bit for the Notes column where the time is recorded every time a note is made/edited). Hence, we write the
onEdit(e)function, https://developers.google.com/apps-script/guides/triggers/
You should have something like below:
SAVE IT!
That’s all you need to run the sample google sheet as part of this tutorial. But keep reading if you want a bit more info on editing and updating the script.
Making changes to the sheet and script
There are few things to keep in mind when making changes on the sheet as this could affect the script.
There are two columns that are affected by the script: Stage and Notes.
Notes column
I have added a simple Last Modified to the Notes column which is managed by the following code:
if (col == 8) {
var range = e.range;
range.setNote('Last modified: ' + new Date());
return;
}As shown, the col is identified by its number (‘8’) so if the Notes column is changed or moved, the if condition will need to be updated.
Stage column
This part of the code affects the stage column:
if (col != 5) return;
var range = e.range;
range.setNote('Last modified: ' + new Date());
var val = e.value;
var row = cell.getRow();
var sendToSheet = ss.getSheetByName(val);
if (sendToSheet != null) {
var lastRow = sendToSheet.getLastRow();
sheet.getRange(row, 1, 1, 9).moveTo(sendToSheet.getRange(lastRow+1, 1));
sheet.deleteRow(row);
}Like the Notes column, if the Stage column is moved around then the if statement needs to be updated with the correct number (‘5’).
The current code moves over 9 columns.
sheet.getRange(row, 1, 1, 9).moveTo(sendToSheet.getRange(lastRow+1, 1));If you add more columns, then make sure you change the number 9 in the above statement.
Note: I have tried to add modified by user as well but it was not very straightforward as Google Sheets doesn’t support it yet. But I guess the revision history should suffice until then.
I’ve been trying to build on my Hacking It series. Check the others out down below!
Hacking it: Generate PDFs from Google Forms
Hacking it: Setting up your own Bulk Mail Server — using Sendy and AWS
Well that’s all! If there are any problems, please do let me know. I have adapted the code from many different links that I found and do not remember, so apologies for not citing. All feedback is welcome. You can also tweet out to me — https://twitter.com/neha_m25
