Automate finding a company URL with a company name on Google Sheets. If you manage the contact list, you sometimes get a list without company name OR company URL.
Now need the company name or company URL right? There are a million reasons you could need this. It takes a lot of time to do these one by one.
But from today onwards there is no need to waste time. Here are 3 easy steps to do these things using formula and a ready script, on google sheet. So here is how it’s done!
The steps are written below, but if you are not familiar with Sheets or didn’t understand, I suggest you watch this short video:
STEP 1 :
Copy this link:
https://autocomplete.clearbit.com/v1/companies/suggest?query=
We are going to use the clearbit autocomplete API, which is FREE.
It’s pretty simple, you feed it a company name/company domain and it gives you back normalized data with the company name/company domain, logo and domain!!
STEP 2:
Now it’s time to generate a set of these requests on your sheet
Examples:
https://autocomplete.clearbit.com/v1/companies/suggest?query=pure tips
https://autocomplete.clearbit.com/v1/companies/suggest?query=google
https://autocomplete.clearbit.com/v1/companies/suggest?query=facebook
https://autocomplete.clearbit.com/v1/companies/suggest?query=linkedin
Use a CONCATENATE Function. And drag-down a whole column of them.
STEP 3:
In Google Sheets, open Tools> Script Editor and copy paste this code:
function FETCH(url) {
var response = UrlFetchApp.fetch(url);
Logger.log(response.getContentText());
var out = JSON.parse(response.getContentText());
return out[0][‘domain’];
}
Or What you want write in – return out[0][‘domain’];
function FETCH(url) {
var response = UrlFetchApp.fetch(url);
Logger.log(response.getContentText());
var out = JSON.parse(response.getContentText());
return out[0][‘what you want’];
}
Don’t forget to save this code! (shortcut: CTRL+S) Now you are done!
You can go back to your sheet.
In an empty column just apply the function: FETCH (url)
[*url = the cell with your generated request from step 2].
Here You can see Live G-Sheet : [ https://docs.google.com/spreadsheets/d/1vUcAnyZqR5I74gIBh-uTrz-CvqCbUPuIpxfE3MfUCoE/edit?usp=sharing ]
Apply the formula to all the generated requests and the script will find the domains for you!! Nice & easy.