The goal of this post is to guide you through connecting Google Sheets to your very first external API using Google Apps Script, to retrieve data from a third-party and display it in your Google Sheet.
We’re going to start by using Google Apps Script to connect to a super simple warm-up API to retrieve some data:
Then we’ll use Google Apps Script to build a music discovery application using the iTunes API:
Finally, I’ll leave you to have a go at building a Star Wars data explorer application, with a few hints:
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 using Google Apps Script. It’s fun and really satisfying if you’re new to this world.
Connecting Google Sheets to an external API using Google Apps Script
We’re going to be using Google Apps Script to connect to external APIs in the following examples.
Warm-up: Connecting Google Sheets to the Numbers API using Google Apps Script
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 & Google Apps Script