Connecting to the Crunchbase API with Google Sheets

Crunchbase API with Google Sheets

How to import data from the Crunchbase API into Google Sheets

Crunchbase is a business information platform; a sort of giant database of information on organizations and people in the business world, especially the digital/technology/startup world.

They have an API so you can programmatically access and retrieve business data. There’s a free tier, which is what I’ll show in this article, and a paid, pro tier, which has a much richer dataset available through the API.

On the free tier, you’re limited to data on organizations and people profiles, through their Open Data Map. It’s a RESTful API with two endpoints (/odm-organizations and /odm-people) and you need to apply for basic access first to get a user key for access.

Retrieving Organization Data

The Google Sheet setup is super simple:

google sheet setup

The root point of our URL is:

https://api.crunchbase.com/v/3/odm-organizations

to which we add the query (i.e. the name of the organization we want) and our user key, so we can access the API.

The code is as follows, which you add in Tools > Script editor...

There are essentially five parts to this code:

  1. Storing our user key in the global variable USER_KEY (line 1)
  2. Creating our custom URL for the Crunchbase API, including the query parameter and our USER_KEY (lines 4 – 11)
  3. Calling the API and retrieving the raw data (line 13, and lines 63 – 75)
  4. Handling any errors with the fetch operation or from the server (lines 15 – 25)
  5. Parsing this data (lines 25 – 46)
  6. Outputting it into a Google Sheet (lines 49 – 56)

Finally, let’s add a menu to the spreadsheet, so we can run the functions from there:

Note: this menu also includes the function to retrieve people data, which we haven’t written yet. That is coming below.

The full code is also available here on GitHub.

The first time you run this script, you’ll be prompted to allow the script to connect to a third-party service and to access your spreadsheet (which is shown in the image below).

First, click “Review permissions”:

Allow Google script to access third-party and spreadsheet

and then click “Allow”:

Allow Google script to access third-party and spreadsheet

To use this script, type the name of the organization you’re interested in into cell B3, and then run the script from the custom menu: Crunchbase Data > Retrieve organization data...

The output should look like this:

Crunchbase organizations API with Google Sheets

Retrieving Person Data

We can do exactly the same for people in the open data Crunchbase:

crunchbase people api into Google Sheets

This time the URL is:

https://api.crunchbase.com/v/3/odm-people

and the data returned is slightly different, but there is no material difference in how we retrieve it, parse it or present it in our sheet.

So I’ll just post the code here for the people function:

The full code is also available here on GitHub.

The final output should look like this:

Crunchbase people API with Google Sheets

Resources:

Crunchbase documentation for the Open Data Map.

Crunchbase documentation for the open data organizations API.

Crunchbase documentation for the open data people API.

Google documentation for the URL Fetch App class.

An excellent post from Mogsdad that goes into more detail about the URL Fetch App and includes further code examples.

Leave a Reply

Your email address will not be published. Required fields are marked *