Sales Automation. Part 1: How to connect any sales software to Google Sheets

I’m a big fan of personalization at scale even if some of the approaches we use here at Reply aren’t scalable at all. 

Or are they?

The truth is that we can automate anything we want – at least to some extent. And sales/sales development is no exception to this. There are tons of tasks and processes that can and should be automated, e.g. list building, email research, email verification, pushing prospects to sales engagement and sales acceleration software, etc.

That is why I’ve decided to write a series of posts with some of the proven automation hacks we’ve been using at Reply. 

And the first hack will be dedicated to Google Spreadsheets. A go-to prospect database tool used by growth hackers, marketers, recruiters, sales reps, and SDRs, it can be easily connected with other tools I mentioned above to create a number of automation workflows. 

Read on to learn some of our hacks for automating Google Sheets.

How to connect any sales software to Google Spreadsheets

First of all, here’s what you’ll need to get started:

1. Google Spreadsheet
2. Spreadsheet’s Script Editor Feature
3. A sales tool (or a bunch of sales tools) that provide APIs

And that’s it.

Now, let’s dive into the process. Here’s a step-by-step guide on how to connect your sales stack to Google Spreadsheets:

• Create a new Google Spreadsheet
• Open the Script Editor tool

• Click this link and copy the code from that page. In a nutshell, this will allow you to create a new function in Google Spreadsheets and pull data from any software that provides an API just simply by calling its API methods.
• Paste the code into a script editor and rename the project to ImportJSON Google Sheets

• Do not forget to click a floppy disc icon ???? on the top left corner.
• Go back to a Google Spreadsheet, refresh the page, and start typing “=ImportJSON“

Voila! This particular spreadsheet will always support this custom function so you are ready to automate your sales process ????.

Implementation example

Now that we know how to link your Google Spreadsheets to any hypothetical sales software, let’s put that knowledge into practice with a real example. There are hundreds of use cases for this solution – from syncing the data back and forth within your sales tack to sending automated emails from Google Sheets.

Now, let’s take a closer look at one of the possible ways to automate your sales routine with Google Sheets. 

Imagine that we have a list of companies along with their domain addresses that we need to prospect. So our input data will look like this:

Our goal is to find decision-makers info, their email addresses, verify emails, and move them to a sales sequence – all using Google spreadsheet automation.

Here’s what we should do:

1. First of all, let’s add to that information CEO/decision-makers’ full names (as exemplified above).
2. Now, having the names and domains, we can find their email addresses. There are many different tools you can use to do that. In this case, we will use Hunter.io – one of the most popular tools on the market that has a simple and intuitive API.A quick look into Hunter’s API docs and we can see that we need an API endpoint called Email Finder – https://api.hunter.io/v2/email-finder?domain=&full_name=&api_key=. To call this API method from Google Spreadsheet, we will use ImportJson() function we’ve mentioned above and this quite simple yet effective formula:=ImportJSON(CONCATENATE(“https://api.hunter.io/v2/email-finder?domain=”,B2,”&full_name=”,C2,”&api_key={insert_your_api_key}”),”/data/email”,”noInherit,noTruncate,noHeaders“) When it’s done, just apply the same function to all rows in the spreadsheet.
3. To minimize your bounce rate (and prevent any domain reputation issues), I’ll strongly recommend using an email verification service as an additional step of your prospecting workflow to make sure the email addresses on your list are valid.Luckily, there’s plenty of tools to choose from (we even have a dedicated category with proven email verification providers in our catalog). For this example, I will use TrueMail. So, let’s try calling Trumail API using ImportJSON() function again:=ImportJSON(CONCATENATE(“https://truemail.io/api/v1/verify/single?address_info=1&timeout=100&access_token={your_api_key_here}&email=”,D2),”/result”,”noInherit,noTruncate,noHeaders“)Apply this function for all rows you have: As you can see, the first two emails are risky. So if I were you, I’d definitely double-check those emails manually or just skip adding them to your sequences for now.
4. Next, let’s cut off one more routine task that SDRs, salespeople, recruiters do all the time – import the prospect list to a web app as a CSV file.

While it’s impossible to send data back to web apps using Google Sheets and ImportJSON function, we can still use Zapier to automate this step. In order to do so, you can use this zap that will trigger every time you update a row in your spreadsheet. 

For example, you can create a checkbox field and mark it as checked to trigger this action every time you want to add a prospect to a sequence.

As a result, your automated Google Spreadsheet workflow will look like this:

Wrapping up

There’s a good reason we have picked data research and prospecting automation as a starting point in this post series. Those are two of the most time-consuming tasks that are also fundamental to your sales strategy success.

And while this approach might seem too simplistic for some of you, the easiest solutions usually turn to be the most effective ones. 

So we hope that our hacks will help you build a more robust and effective sales process within your organization or become a great foundation for building more extensive workflows in the future.