SIMPLE Leads Intake with Sheets, Monday.com, and Make.com
There’s nearly 900 million people using Google Sheets each month. Despite that, in the engineering eco-sphere, you may hear some shade thrown at spreadsheet tools, and in my opinion, the shade is not totally unwarranted. The fact is that spreadsheet tools are insanely versatile and allow any level of user to quickly build models, tools, and dashboards – but that’s also why they can also grow to be headaches. When management gets a taste of what spreadsheet tools can do (and for the low cost of a single subscription), they tend to throw too much business functionality behind them.
This is not one of those use cases.
I want to show how you can set up a simple pipeline from Sheets, to Make.com, to Monday.com or any of CRM or tool that you need driven from your intake process. One of the highlights of this process is that we start with a tool anyone can use – Google Sheets. I know what I said before about how Sheets can be overused, but versatility is one of its strong suits. Part of the reason that we use it in this setup is that we’re solving two major problems that I see with some of my clients:
- Sheets can be shared amongst different users. You don’t need to add an additional license like you would in your CRM setup to allow an outsider to use the sheet.
- We don’t want to be manually entering details of the lead into your Monday.com instance, and we want automation driven off the complete record of the lead that’s input into Monday.com.
Let’s take a look at our basic setup:
When I said basic, I meant it. We connect a simple trigger up with an action on Monday.com, which grabs data from a test sheet I created, and connects it with a Monday.com instance and creates and item on a board. Let me show some more details into the Sheets module.
When you go to enter the spreadsheet you want to watch into our module, you have three options of selecting: Search by path, Select from All, or Enter manually. My experience is that it’s usually easier to select to enter the ID of the spreadsheet manually, as even a small organization can have thousands of sheets and complicated folder trees in Google Drive to look through. See how we grabbed that ID with sheets and input it into the module directly:
This module sits on a schedule and checks the spreadsheet on that schedule, whether it be every minute or every hour, to see if the spreadsheet contains new rows. When it finds them, it sends them to our next module for input into Make.com:
On a simple level that’s really all there is to it. We take in new data from Sheets, we input that into Monday.com, and everyone’s happy. HOWEVER there’s another consideration that we’re missing and one that’s plagued several of the teams I’ve worked with. That is: when you run this scenario repeatedly, it racks up a lot scenario usage. I had one team that ran this scenario to check Sheets every minute, 24 hours a day, every day for an entire month. This one scenario alone, even without running any actual new rows coming from Sheets racks up nearly 43,000 scenarios each month, a cost that eats through most small businesses scenario usage.
Improvement #1:
To fix this, we would want to use an instant trigger that’s driven by changes to a sheet. To find this trigger, click on the Sheets app in Make, scroll down to Calls and select the Watch Changes action that has the INSTANT and ACID tags next to it’s name. Use the prompts on the action to create a web-hook that we can enter into our Make extension later.
Now we take that URL and add it to our Google Sheets workbook. Luckily Make has excellent documentation to guide you through the process, and all you need to do now is to add the Make add-on for sheets, open the tool by going to Extensions > Make for Google Sheets. This will let you see the following screen.
Once you’ve entered the webhook given to you by the Watch Change module, you’re all set to go! You’ll get updates on your sheets as soon as they come in, and you won’t waste any extra operations on simple checks for new rows in the sheet.
HOWEVER we’ve still got another problem to consider. The Watch Changes action for the Google Sheets app on Make only checks for changes made in the actual editor on Google Sheets. While this works if you have people working explicitly inside the sheet, what happens when you want to drive the leads intake process from other third party apps? If you decide to let users input information via Google Forms for example: the Watch Changes action won’t fire when the forms app adds a new row to your Sheet. So if row three in the picture above was added by a form intake, the scenario won’t fire. I’ve surprisingly come across this example several times when working with clients as they wanted these intake sheets to be a single source for multiple streams of leads.
The good new is: we’ve got one final option to consider.
Improvement #2:
In order to capture all the input from our Sheets intake process, we can set up an event that fires from Google’s App Scripts.
First, let’s setup a new Webhooks module in Make. We are going to use the Custom Webhook option, and we are going to setup a new webhook.
Once we have that link, we then want to setup a new script in Sheets. Go to Extensions > Apps Script. There let’s go to the Editor section marked by the <> on the left hand side of the screen. Here, we’re going to enter the following code:
function atEdit(e) {
var liveWebhookUrl = 'new_url_here';
var sheet = e.source.getActiveSheet();
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
var range = sheet.getRange(lastRow, 1, 1, lastCol);
var values = range.getValues()[0];
var headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
var payload_body = ""
var jsonData = {};
for (var i = 0; i < headers.length; i++) {
jsonData[headers[i]] = values[i]; // building our data
}
var payload_body = JSON.stringify(jsonData, null, 2);
if (sheet.getName() == "Sheet1") {
// we are sending just the sheet name and the data itself
var payload = {
'sheetName': sheet.getName(),
payload_body
};
// Send the request via post request to the make webhook url
UrlFetchApp.fetch(liveWebhookUrl, {
'method': 'POST',
'contentType': 'application/json',
'payload': JSON.stringify(payload)
});
}
}You don’t need to know exactly what each part of this code does, but it essentially takes the last line of the sheet, puts it into a neat JSON format that we can work with in Make, then sends it to our new webhook. Take this code, enter it into the editor, hit save on the top, then go to the triggers tab on the left hand navigation menu.
Check and use the settings I have above after selecting Add Trigger on the right lower hand side. We use On change here, and select the atEdit function which is the name I gave the function when entering it earlier. Our major issue with this approach is that technically, ANY change will trigger this event to fire. Because of the way we’ve written the code we only grab the last row in the sheet, but if a random cell on the sheet is changed 5 times, it will cause us to trigger the last row to the Make scenario 5 times. Because of the limitations in our other approaches I’ve deemed this to be an acceptable risk, and I’ve setup the scenario as such that duplicates are checked early and often during each run before we’ve risked too much scenario usage. I’ve also made sure to use the triggering sheet in question as a production environment rather than a worksheet style environment.
Without getting too much into the weeds of duplicate checking (use your own business logic, or a Datastore!) let’s see what our final scenario looks like.
In order: we receive the incoming webhook with our Custom webhook action, we use the Parse JSON block with the payload body as the input (as the incoming input will just a string, not a Make object like we’re use to), and the output of that Parse JSON block is the output we want from Sheets! Now we are free to operate in our scenario exactly as we did before.
