Step-by-step instruction for creating the custom function “GETWEBSITEDETAILS”, as example of how to use the Google Places API within Script Editor
There are many advantages of using Sheets rather than Excel, in my humble and extensively documented opinion. Most of my posts have focused on the advantages that stem from querying functions like FILTER and QUERY. This article speaks to another high-level advantage: the ability to dynamically import data from across the web. One method to do that is to use Script Editor to access APIs.
The example I describe here, “GETWEBSITEDETAILS”, comes from a request from a friend. Let’s call him Earl. Earl had newly launched a search fund. He was sent a lead list of tens of thousands of small companies for sale and his first step was simply to clean the list - remove the duplicates, filter out those without a phone number and website publicly listed, etc. When I spoke to him he was like 15 hours in, and less than 10% of the way through the effort. Earl’s time is valuable. And this was low-value work.
Here is what I created for him:
In columns B : D you can type or paste in the name, city and state of the businesses (or whichever of those three you have), and the critical information will get pulled in automatically into columns F : J. The key feature is a the custom function being used in column F, "GETWEBSITEDETAILS", which uses the Google Places API to essentially replicate the details that would come back if you Googled that same combination of name + city + state. Here's the exact formula:
IFERROR ( IF ( AND ( name = "" , city = "" , state = "" ) , "--" , IF ( offButton = "OFF" , "--" , TRANSPOSE ( GETWEBSITEDETAILS ( name , city , state ) ) ) ) , "No Results" )
In order to use the Custom Function yourself, you will need to obtain a Google Places API Key and create a Project on Google Cloud Platform. Before I get into the details on that, let me say once again: I am not an engineer and I know only a minimal amount of Javascript (the language Google Script is based on). I hope that is encouraging, in a way (you can do it too. Woo hoo). But it is also meant to be a warning: there may well be a better way to do this.
Ok, here’s how you do it. Setting this up should take roughly 10 minutes. And unless you are using the function at massive scale, it will be free.
Step 1: Obtain a Google Places API Key*
Make sure you are signed in to your Google account and then click here
Immediately, a modal should pop up in the center of the page with title “Enable Google Maps Platform”. Select all three boxes (“Routes”, “Maps”, and “Places”) and press “Continue”
On the next screen name your Project (you can always change this later), and select “Yes” to agree to the terms. Press “Next”
Click “Create a Billing Account” and you will arrive at a page asking for name, address and credit card information. This, unfortunately, is a requirement to get an API Key. That said, you will only have to make payments if you exceed ~50K API calls per month. The pricing details can be found here. Warning: it’s confusing (you actually are charged from the getgo, but you get $200 of credits monthly blah blah blah)
After completing that, you should receive a prompt to “Enable your APIs”. Click Next
And FINALLY, you will receive your API key. It should look something like this: “AIzaSyBtlek5RWUwE_nlUG6OVC0baFnMOLmeoCI” (don’t try to use that, it won’t work)
*Note: if you already have Cloud set up, this flow is much simpler. Just 1) enable the "Places" API for the new project; and 2) Create your API
Step 2: Create the Custom Function: GETWEBSITEDETAILS
In a new workbook, go to Tools and Script Editor. Create a new Project and name it getWebsiteDetails
Paste the following block of text:
(Click the image above and a doc will open where you can copy the text)
Replace “YOUR KEY HERE” with your Google Places API Key. Make sure that you don’t lose the apostrophes that need to surround it
Click the Save button and you’re done
A quick note: I’ve written this to return 5 specific company details: name, website, phone number, address, and type. There are roughly 30 to choose from, listed here.
Step 3: Create your Sheet (and the On/Off Button)
This should be pretty self-explanatory, but I do want to call out the importance of creating the “On / Off Button” as I’ve done in the example. Why bother? Because, as mentioned before, there is a threshold of monthly API calls, above which you will get charged. And the actions that trigger the formulas to re-calculate can be a little unpredictable. So, as a fail safe, create the button and keep it at “OFF” as default.
Thanks for reading! Don't forget to subscribe the newsletter. New post every two weeks in your inbox.