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.

Continue reading Connecting to the Crunchbase API with Google Sheets

Beginner guide to APIs with Google Sheets & Apps Script

The goal of this post is to guide you through connecting Google Sheets to your very first external API to retrieve data from a third-party and display it in your Google Sheet.

We’re going to start with a super simple warm-up API to retrieve some data:

Random math facts from Numbers API in Google Sheet

Then we’ll build a music discovery application using the iTunes API:

Itunes API with Google Sheets

Finally, I’ll leave you to have a go at building a Star Wars data explorer application, with a few hints:

Star Wars API explorer in Google Sheets

The basics: what is an API?

You’ve probably heard the term API before. Maybe you’ve heard how tech companies use them when they pipe data between their applications. Or how companies build complex systems from many smaller micro-services linked by APIs, rather than as single, monolithic programs nowadays.

API stands for “Application Program Interface”, and the term commonly refers to web URLs that can be used to access raw data. Basically, the API is an interface that provides raw data for the public to use (although many require some form of authentication).

As third-party software developers, we can access an organization’s API and use their data within our own applications.

The good news is that there are plenty of simple APIs out there, which we can cut our teeth on.

We can connect a Google Sheet to an API and bring data back from that API (e.g. iTunes) into our Google Sheet. It’s fun and really satisfying if you’re new to this world.


Connecting Google Sheets to an external API using Apps Script

We’re going to be using Apps Script to connect to external APIs in the following examples.

Apps Script is a Javascript-based scripting language hosted and run on Google servers, that extends the functionality of Google Apps. If you’ve never used it before, then you may want to read through my Beginner Guide before you start these examples.

Warm-up: Connecting Google Sheets to the Numbers API

We’re going to start with something super simple, so we can focus on the data and not get lost in lines and lines of code.

We’re going to write a short program that calls the Numbers API and requests a basic math fact.

Step 1: Open a new blank Google Sheet and rename it: Numbers API Example

Continue reading Beginner guide to APIs with Google Sheets & Apps Script

Creating a dynamic d3 visualization from the GitHub API

GitHub Repo Visualization
Try out the GitHub Repo Visualization here

As someone who works with data on a daily basis, I’m always impressed and inspired by interactive charts and dashboards. I’ve built plenty of dynamic dashboards within Excel (here) and, more recently, within Google spreadsheets (here, here and here), but never my own custom web charts. I’ve wanted to learn d3 for a while, but until recently didn’t have the necessary Javascript chops to do this.

This year I’ve focussed on deepening my coding skills, so I’ve finally been able to give d3 a proper go. And let me tell you, it’s brilliant. It’s exciting to hook up a data source to a custom chart that changes dynamically, and be able to see it on a live website, which other people can view.

In this post, I’m going to discuss the steps I took to create this d3 visualization of the GitHub API.

The app is live here!
Continue reading Creating a dynamic d3 visualization from the GitHub API