How to retrieve Company info in Google Sheets with Clearbit and Apollo API

Scripting in Google Sheets gives you super power, and it’s very easy to use APIs to enrich your document.

In this article I’ll show you how-to:

1. Create custom functions in Google Sheets
2. Use Clearbit API to translate a company name into a company domain
3. Use Apollo API to get details about a company using a company domain
4. Bonus: Use Google CacheService to avoid API rate limits

Try it now! :

• Link to the Excel demo
• Link to the code
• Bonus: Clearbit provides a very clean and useful API, as easy as

https://autocomplete.clearbit.com/v1/companies/suggest?query=hubware

1. Create custom functions in Google Sheets

>> Link to the documentation

Google Apps Script 2022 is much better that it used to be (there was a major redesign in 2020). It only takes a few clicks to create a custom function, which will be available in Google Sheets like any other function.

To access Apps Script, follow Extensions > Apps Script. It will be automatically associated to your document.

From there, you can write your javascript function, which name will be used as a custom function.

Create a function for Clearbit

>> Link the documentation

Clearbit is a marketing data platform with many tools and connectors. Their gmail extension is very useful.

Let’s create a function called “clearbit_getDomain” in a file called Clearbit.gs:

const base_url_clearbit = “https://autocomplete.clearbit.com/v1/companies/";
function clearbit_getDomain(name) {
var suggest_url = base_url_clearbit + ‘suggest?query=’ + name;
var suggest_response = UrlFetchApp.fetch(suggest_url);
var suggest_response_text = JSON.parse(suggest_response.getContentText());
var domain = suggest_response_text[0][‘domain’];
return domain;
}

You can now use the function in Sheets:

The API is free and I encourage you to have a look at Clearbit’s full API documentation. You need a premium account though.

Create a function for Apollo

>> Link to the documentation

Apollo is an even bigger marketing data platform, the amount of data is impressive. I like how they did not base their pricing on requests (it’s more about features).

Most of their competitors like Hunter or Uplead sell credits to get lead emails at a high rate (like, 1$ = 1 lead email). Apollo has a freemium account with a lot of credits.

Using their API, we can enrich an organization using its domain name and get many useful information:

Let’s create a function called apollo_getInfo in a file called Apollo.gs. This function takes as input the domain name (from Clearbit) and also the first-level field we want.

Warning! You can only call this API 100 times per hour, so DO NOT use this function on many rows yet.

const BASEURL_APOLLO = “https://api.apollo.io/v1/organizations/";
function apollo_getInfo(domain, field) {
var enrich_url = BASEURL_APOLLO + ‘enrich?api_key=’ + AUT_KEY_APOLLO + ‘&domain=’ + domain;
var find = UrlFetchApp.fetch(enrich_url);
result = JSON.parse(find.getContentText());
return result[“organization”][field];
}

NB: you need to create an account at Apollo to get the API Key, it’s really straightforward. Follow this link to activate the API and get your key.

In a file called Secret.gs:

const AUT_KEY_APOLLO = “CHANGE_ME”;

Using CacheService

>>Link to the documentation

Now, it’s working, but you do not have many credits, use them wisely!

I encourage you to buy one of Apollo’s premium account, because their service is awesome. The API premium rate limit won’t much greater that the freemium though, up to 1.000 calls per hour.

However, Google Sheets call functions repetitively, it will burn your credits very quickly. We need to use CacheService, which is very easy to use:

var cache = CacheService.getScriptCache();
cache.put(key, data);
cache.get(key);

The updated script with CacheService and a logging utily:

function apollo_getInfo(domain, field) {
var enrich_url = BASEURL_APOLLO + 'enrich?api_key=' + AUT_KEY_APOLLO + '&domain=' + domain;
var cache = CacheService.getScriptCache();
var ret = cache.get(CACHE_PREFIX + field + "_" + domain);
if (!ret) {
var find = UrlFetchApp.fetch(enrich_url);
if (find.getResponseCode() == 200) {
var result = JSON.parse(find.getContentText());
if (result != "{}") {
ret = result["organization"][field];
var cache_put = CACHE_PREFIX + field + "_" + domain
cache.put(cache_put, ret, 21600);
} else { ret = "Error, org is empty"; }
} else { ret = "Error"+find.getResponseCode(); }
}
return ret;
}

The cache is limited to 250 characters, which is why we only store the field value (and not the whole result).

Put all pieces together

Now, you can build the final function, which calls Clearbit then Apollo.

function fetchCompanyInfo(name, field) {
 return apollo_getInfo(clearbit_getDomain(name), field);
}

You can use in Google Sheets, e.g. to get company country:

=fetchCompanyInfo(A2;”country”)

And to make things easier, here’s a file containing all the code you need to copy in Apps Script (separated in 3 file scripts for clarity).

I hope you enjoyed this tutorial, don’t forget to clap below and share it on your favourite professional network.

Comment or contact me if you have any remarks or requests!

Also, pardon my javascript, I haven’t been coding for a while.