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

The Import Cookbook: How to import social media data into Google Sheets with formulas

Updated for 2017!

Google Sheets has a powerful and versatile set of IMPORT formulas that can import data from other websites.

This article looks at importing data from popular social media channels into a Google sheet, for social network analysis or social media management. If you manage a lot of different channels then you could use these techniques to set up a master view (dashboard) to display all your metrics in one place.

The formulas below are generally set up to return the number of followers (or page likes) for a given channel, but you could adapt them to return other metrics (such as follows) with a little extra work.

Caveats: the websites these formulas retrieve data from frequently change, which means the formulas stop working. I will try to keep this post updated with working versions.

Example workbooks: Each example has a link to an associated Google Sheet workbook, so feel free to make your own copy: File > Make a copy....

Continue reading The Import Cookbook: How to import social media data into Google Sheets with formulas

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

Setting up a Rails model to check for “http(s)” at the front of a URL

This issue arose when I asked my brother to test drive the Rails app I’m working on, UpLearn, without any supervision. It was really useful to have a second person use the software without any knowledge of how it was built, as issues surfaced that I might have otherwise missed.

One issue was the handling of URLs submitted by the user without an “http(s)” at the front. My brother had typed a link in to the submission form directly, rather than copy-pasting the URL, so it was missing an “http://” or “https://” at the front. As a result, my Rails app treated this as a relative path, rather than an absolute path, and the result was a broken link that didn’t take the user to the correct resource page.

Continue reading Setting up a Rails model to check for “http(s)” at the front of a URL