Connecting to the Crunchbase API with Google Sheets

UPDATE: This article was originally written in 2017. Since then, the Crunchbase API is now part of their enterprise tier. The old API, which this article is based on, is no longer accessible. As a result, the code I share below will no longer return Crunchbase data. I leave it here for reference. It may be useful if you do use the paid tier.

The Crunchbase API is easily accessible with Apps Script, meaning you can retrieve Crunchbase company data to display, or analyze further, in your Google Sheet. This article shows you how to connect to the Crunchbase API.

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 from the Crunchbase API
• Retrieving People Data from the Crunchbase API
• Resources

Retrieving Organization Data from the Crunchbase API

The Google Sheet setup is super simple:

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...

var USER_KEY = 'insert your API key in here';

// function to retrive organizations data
function getCrunchbaseOrgs() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Organizations');
  var query = sheet.getRange(3,2).getValue();
  
  // URL and params for the Crunchbase API
  var url = 'https://api.crunchbase.com/v/3/odm-organizations?query=' + encodeURI(query) + '&user_key=' + USER_KEY;
  
  var json = getCrunchbaseData(url,query);
  
  if (json[0] === "Error:") {
    // deal with error with fetch operation
    sheet.getRange(5,1,sheet.getLastRow(),2).clearContent();
    sheet.getRange(6,1,1,2).setValues([json]);
  }
  else {
    if (json[0] !== 200) {
      // deal with error from api
      sheet.getRange(5,1,sheet.getLastRow(),2).clearContent();
      sheet.getRange(6,1,1,2).setValues([["Error, server returned code:",json[0]]]);
    }
    else {
      // correct data comes back, filter down to match the name of the entity
      var data = json[1].data.items.filter(function(item) {
        return item.properties.name == query;
      })[0].properties;
      
      // parse into array for Google Sheet
      var outputData = [
        ["Name",data.name],
        ["Homepage",data.homepage_url],
        ["Type",data.primary_role],
        ["Short description",data.short_description],
        ["Country",data.country_code],
        ["Region",data.region_name],
        ["City name",data.city_name],
        ["Blog url",data.blog_url],
        ["Facebook",data.facebook_url],
        ["Linkedin",data.linkedin_url],
        ["Twitter",data.twitter_url],
        ["Crunchbase URL","https://www.crunchbase.com/" + data.web_path]
      ];
      
      // clear any old data
      sheet.getRange(5,1,sheet.getLastRow(),2).clearContent();
      
      // insert new data
      sheet.getRange(6,1,12,2).setValues(outputData);
      
      // add image with formula and format that row
      sheet.getRange(5,2).setFormula('=image("' + data.profile_image_url + '",4,50,50)').setHorizontalAlignment("center");
      sheet.setRowHeight(5,60);
    }
  }
}


// general query to call Crunchbase API
function getCrunchbaseData(url,query) {
    
  try {
    var response = UrlFetchApp.fetch(url); // POST emails to mailchimp
    var responseData = response.getContentText();
    var json = JSON.parse(responseData);
    return json;
  }
  catch (e) {
    Logger.log(e);
    return ["Error:", e];
  }
}

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:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Crunchbase Data')
    .addItem('Retrieve organization data...', 'getCrunchbaseOrgs')
    .addItem('Retrieve people data...', 'getCrunchbasePeople')
    .addToUi();
}

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”:

and then click “Allow”:

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:

Retrieving Person Data from the Crunchbase API

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

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:

// function to retrive people data
function getCrunchbasePeople() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('People');
  var query = sheet.getRange(3,2).getValue();
  
  // URL and params for the Crunchbase API
  var url = 'https://api.crunchbase.com/v/3/odm-people?query=' + encodeURI(query) + '&user_key=' + USER_KEY;
  
  var json = getCrunchbaseData(url,query);
  
  if (json[0] === "Error:") {
    // deal with error with fetch operation
    sheet.getRange(5,1,sheet.getLastRow(),2).clearContent();
    sheet.getRange(6,1,1,2).setValues([json]);
  }
  else {
    if (json[0] !== 200) {
      // deal with error from api
      sheet.getRange(5,1,sheet.getLastRow(),2).clearContent();
      sheet.getRange(6,1,1,2).setValues([["Error, server returned code:",json[0]]]);
    }
    else {
      var data = json[1].data.items[0].properties;
      
      // correct data comes back, parse into array for Google Sheet
      var outputData = [
        ["Name",data.first_name + ' ' + data.last_name],
        ["Gender",data.gender],
        ["Type",data.organization_name],
        ["Short description",data.title],
        ["Country",data.country_code],
        ["Region",data.region_name],
        ["Website url",data.homepage_url],
        ["Facebook",data.facebook_url],
        ["Linkedin",data.linkedin_url],
        ["Twitter",data.twitter_url],
        ["Crunchbase URL","https://www.crunchbase.com/" + data.web_path],
        ["Crunchbase Organization URL","https://www.crunchbase.com/" + data.organization_web_path]
      ];
      
      // clear any old data
      sheet.getRange(5,1,sheet.getLastRow(),2).clearContent();
      
      // insert new data
      sheet.getRange(6,1,12,2).setValues(outputData);
      
      // add image with formula and format that row
      sheet.getRange(5,2).setFormula('=image("' + data.profile_image_url + '",4,50,50)').setHorizontalAlignment("center");
      sheet.setRowHeight(5,60);
    }
  }
}

The full code is also available here on GitHub.

The final output should look like this:

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.