Import Crunchbase Data to Google Sheets

In this guide, we’ll walk through how to pull company data from the Crunchbase API directly into Google Sheets, using the API Connector add-on for Sheets. Note that to access the Crunchbase API, you must have a Crunchbase Enterprise or Applications License (pricing). This article assumes that you already have your license and have been granted an API key.

PART 1: CREATE YOUR API REQUEST URL

The Crunchbase API has a rather complex method of querying data through cards and fields. Therefore, let’s first approach the Crunchbase API through their Swagger page, as it assists with constructing request URLs.

  1. First, authorize yourself by entering your API key:
    crunchbase-img4
  2. Next, click “Try it out” next to any API request you’re interested in. Let’s try the Crunchbase Entity Lookup API.
    crunchbase-img5
  3. For our first request, let’s get information about Uber’s founders. Enter “uber” into the entity_id field, and “founders” into the card_ids field. Click Execute.
    crunchbase-img6
  4. Underneath you’ll see the raw API response in JSON format, as well as the Curl representation of your API request. Curl is a command-line tool for interacting with APIs.
    crunchbase-img7
  5. Pay attention to these values, as we’ll copy them directly into API Connector. We now have our first request URL:
https://api.crunchbase.com/api/v4/entities/organizations/uber?card_ids=founders

PART 2: PULL CRUNCHBASE API DATA INTO SHEETS

Let’s now look at how to enter those values into API Connector.

  1. Open up Google Sheets and click Add-ons > API Connector > Open
  2. In the Create tab, enter the API URL we just created.
    crunchbase-img1
  3. Under Headers, enter the two sets of key-value pairs that were shown in the Curl request, like this:
    X-cb-user-keyYOUR_API_KEY
    acceptapplication/json
    crunchbase-img2
  4. We don’t need any extra authentication, so leave the authentication section set to None.
  5. Create a new tab, give it a name, and click ‘Set current’ to use that tab as your data destination.
  6. Name your request and click Run. A moment later you’ll see details about Uber’s founders populate your sheet.
    crunchbase-img3

PART 3: GET MORE CRUNCHBASE API DATA

For our next request, let’s use the Crunchbase Search API. The Search API requires constructing a POST body containing all the filters and conditions of your request. Let’s start with one of their examples.

  1. As before, click “Try it out”. We’ll try their request to search for companies in Europe with funding total between 25m and 100m USD.
    crunchbase-img8
  2. Click Execute
    crunchbase-img9
  3. You’ll now see a Curl representation of your request, which we will use to build our request in API Connector:
    crunchbase-img10
  4. Parsing this block into its components yields the following:

Method: POST
URL: https://api.crunchbase.com/api/v4/searches/organizations
Headers:

acceptapplication/json
X-cb-user-keyYOUR_API_KEY
Content-Typeapplication/json

POST Body (paste it into an unescape tool first to remove all the backslashes:

{"field_ids":["identifier","categories","location_identifiers","short_description","rank_org"],"order":[{"field_id":"rank_org","sort":"asc"}],"query":[{"type":"predicate","field_id":"funding_total","operator_id":"between","values":[{"value":25000000,"currency":"usd"},{"value":100000000,"currency":"usd"}]},{"type":"predicate","field_id":"location_identifiers","operator_id":"includes","values":["6106f5dc-823e-5da8-40d7-51612c0b2c4e"]},{"type":"predicate","field_id":"facet_ids","operator_id":"includes","values":["company"]}],"limit":50}

Enter these values into API Connector and click Run to see the API response in your spreadsheet.
crunchbase-img11

This article gives additional examples of how to use the Crunchbase Search API: https://data.crunchbase.com/docs/examples-search-api

PART 4: HANDLE PAGINATION

  1. Note Crunchbase’s limits on the number of records returned on a response. By default, there is a maximum of 1000 records returned, unless you include the “after_id” key in your POST body as described in their documentation.
    crunchbase-img12
  2. The uuid of the last item can be seen in your sheet.
    crunchbase-img13
  3. Since the value will always appear in cell B1001, you can retrieve the second set of 1000 records by directly entering in that uuid, or by referencing that cell. Assuming your data lies in Sheet1, this POST body would retrieve that second set of 1000 records. You can edit the original request and use append mode to send the data into the same sheet right after the first set of records.
    {"field_ids":["identifier","categories","location_identifiers","short_description","rank_org"],"order":[{"field_id":"rank_org","sort":"asc"}],"query":[{"type":"predicate","field_id":"funding_total","operator_id":"between","values":[{"value":25000000,"currency":"usd"},{"value":100000000,"currency":"usd"}]},{"type":"predicate","field_id":"location_identifiers","operator_id":"includes","values":["6106f5dc-823e-5da8-40d7-51612c0b2c4e"]},{"type":"predicate","field_id":"facet_ids","operator_id":"includes","values":["company"]}],"limit":1000,"after_id": "+++Sheet1!B1001+++"}

Leave a Comment