Beginner guide to APIs with Google Sheets & Google Apps Script

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:

Random math facts from Numbers API in Google Sheet

Then we’ll use Google Apps Script to 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 using Google Apps Script

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.

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


Step 2: Open up Tools > Script Editor…

Access script editor through toolbar

Step 3: A new tab opens and this is where we’ll write our code. Name the project: Numbers API Example

Step 4: Remove all the code that is currently in the Code.gs file, and replace it with this:

We’re using the UrlFetchApp class to communicate with other applications on the internet to access resources, to fetch a URL.

Now your code window should look like this:

Numbers API Google Apps Script code

Step 5: Run the function by clicking the play button in the toolbar:

Run Apps Script button

Step 6: This will prompt you to authorize your script to connect to an external service. Click “Review Permissions” and then “Allow” to continue.

Apps Script Review Permissions

Apps Script authorization

Step 7: Congratulations, your program has now run. It’s sent a request to a third-party for some data (in this case a random math fact) and that service has responded with that data.

But wait, where is it? How do we see that data?

Well you’ll notice line 5 of our code above was Logger.log(....) which means that we’ve recorded the response text in our log files.

So let’s check it out.

Go to View > Logs:

View apps script logs

You’ll see your answer (you may of course have a different fact):

[17-02-03 08:52:41:236 PST] 1158 is the maximum number of pieces a torus can be cut into with 18 cuts.

which looks like this in the popup window:

Apps script logger output

Great! Try running it a few times, check the logs and you’ll see different facts.

Next, try changing the URL to these examples to see some different data in the response:

http://numbersapi.com/random/trivia
http://numbersapi.com/4/17/date
http://numbersapi.com/1729

You can also drop these directly into your browser if you want to play around with them. More info at the Numbers API page.

So, what if we want to print the result to our spreadsheet?

Well, that’s pretty easy.

Step 8: Add these few lines of code (lines 7, 8 and 9) underneath your existing code:

Line 7 simply assigns the response text (our data) to a variable called fact, so we can refer to it using that name.

Line 8 gets hold of our current active sheet (Sheet1 of Numbers API Example spreadsheet) and assigns it to a variable called sheet, so that we can access it using that name.

Finally in line 9, we get cell A1 (range at 1,1) and set the value in that cell to equal the variable fact, which holds the response text.

Step 9: Run your program again. You’ll be prompted to allow your script to view and manage your spreadsheets in Google Drive, so click Allow:

Apps Script Review Permissions

Apps script spreadsheet authorization

Step 10: You should now get the random fact showing up in your Google Sheet:

Random math fact from Numbers API in Google Sheet

How cool is that!

To recap: we’ve requested data from a third-party service on the internet. That service has replied with the data we wanted and now we’ve output that into our Google Sheet!

Step 11: The script as it’s written will always overwrite cell A1 with your new fact every time you run the program. If you want to create a list and keep adding new facts under existing ones, then make this minor change to line 9 of your code (shown below), to write the answer into the first blank row:

Your output now will look like this:

Random math facts from Numbers API in Google Sheet

One last thing we might want to do with this application is add a menu to our Google Sheet, so we can run the script from there rather than the script editor window. It’s nice and easy!

Step 12: Add the following code in your script editor:

Your final code for the Numbers API script should now match this code on GitHub.

Step 13: Run the onOpen function, which will add the menu to the spreadsheet. We only need to do this step once.

Add custom apps script menu

Step 14: Use the new menu to run your script from the Google Sheet and watch random facts pop-up in your Google Sheet!

Use custom apps script menu

Alright, ready to try something a little harder?

Let’s build ourselves a music discovery application in Google Sheets.


Music Discovery Application using the iTunes API

This application retrieves the name of an artist from the Google Sheet, sends a request to the iTunes API to retrieve information about that artist and return it. It then displays the albums, song titles, artwork and even adds a link to sample that track:

Google Sheets and iTunes API using Apps Script

It’s actually not as difficult as it looks.

Start with a blank Google Sheet, name it “iTunes API Explorer” and open up the Google Apps Script editor.

Clear out the existing Google Apps Script code and paste in this code to start with:

Run the program and accept the required permissions. You’ll get an output like this:

iTunes API output

Woah, there’s a lot more data being returned this time so we’re going to need to sift through it to extract the bits we want.

So try this. Update your code to parse the data and pull out certain bits of information:

Line 4: We send a request to the iTunes API to search for Coldplay data. The API responds with that data and we assign it to a variable called response, so we can use that name to refer to it.

Lines 7 and 8: We get the context text out of the response data and then parse the JSON string response to get the native object representation. This allows us to extract out different bits of the data.

So, looking first at the data object (line 10):

iTunes api data packet

You can see it’s an object with the curly brace at the start {

The structure is like this:

{
resultCount = 50,
results = [ ....the data we're after... ]
}

iTunes api data packet

Line 11: we extract the “results”, which is the piece of data that contains the artist and song information, using:

data["results"]

Line 12: There are multiple albums returned for this artist, so we grab the first one using the [0] reference since the index starts from 0:

data["results"][0]

This shows all of the information available from the iTunes API for this particular artist and album:

iTunes api data results

Lines 13 – 16: Within this piece of data, we can extract specific details we want by referring to their names:

e.g. data["results"][0]["collectionName"]

Lines 13 – 16 above give the following output:

iTunes api details

Use comments (“//” at the start of a line) to stop the Logger from logging the full data objects if you want. i.e. change lines 10, 11 and 12 to be:

// Logger.log(data);
// Logger.log(data[“results”]);
// Logger.log(data[“results”][0]);

This will make it easier to see the details you’re extracting.

Putting this altogether in an application

If we want to build the application that’s showing in the GIF at the top of this post, then there are a few steps we need to go through:

  • Setup the Google Sheet
  • Retrieve the artist name from the Google Sheet with Google Apps Script
  • Request data from iTunes for this artist with Google Apps Script
  • Parse the response to extract the relevant data object with Google Apps Script
  • Extract the specific details we want (album name, song title, album artwork, preview url)
  • Clear out any previous results in the Google Sheet before showing the new results
  • Display the new results in our Google Sheet
  • Add a custom menu to run the program from the Google Sheet, not the script editor

It’s always a good idea to write out a plan like this before you commit to writing any lines of code.

That way you can think through the whole application and what it’s going to do, which allows you to make efficient choices with how you setup your code.

So the first thing to do is setup a Google Sheet. I’ll leave this up to you, but here’s a screenshot of my basic Google Sheet setup:

iTunes Google Sheet

The important thing to note is the location of the cell where a user types in the artist name (11th row, 2nd column) as we’ll be referring to that in our code.

And here’s the Google Apps Script code for our application:

Here’s the iTunes API script file on GitHub.

Let’s talk about a few of the key lines of code in this program:

Lines 16 – 25 describe a function that takes an artist name, calls the API with this artist name and then returns the search results from the API. I’ve encapsulated this as a separate function so I can potentially re-use it elsewhere in my program.

The main program starts on line 28.

On line 34, I retrieve the name of the artist that has been entered on the Google Sheet, and we call our API function with this name on line 36.

On lines 42 – 47, I take the results returned by the API, loop over them and pull out just the details I want (artist name, album name, song title, album artwork and preview track). I push all of this into a new array called output.

Next I sort and add an index to the array, although both of these are not mandatory steps.

On line 68, I clear out any previous content in my sheet.

Then on line 71, I paste in the new data, starting at row 15.

Finally, lines 74 – 76 format the newly pasted data, so that the images have space to show properly.

Run the onOpen() function from the script editor once to add the custom menu to your Google Sheet. Then you’ll be able to run your iTunes code from the Google Sheet:

Custom iTunes API menu

Now you can run the program to search for your favorite artist!

More details on the iTunes API:

Documentation for searching the iTunes Store.

Documentation showing the search results JSON packet.


Star Wars data explorer using the Star Wars API (SWAPI)

This one is a lot of fun!

The Star Wars API is a database of all the films, people, planets, starships, species and vehicles in the Star Wars films. It’s super easy to query and the returned data is very friendly.

Star Wars API in Google Sheet

It’s a little easier than the iTunes API because the data returned is smaller and more manageable, and therefore easier to parse when you first get hold of it.

As with both the previous APIs, start with a simple call to see what the API returns:

The data returned looks like this:

Star Wars API data

So, it’s relatively easy to get the different pieces of data you want, with code like this:

Well, that should be enough of a hint for you to give this one a go!

Some other tips

In addition to custom menus to run scripts from your Google Sheet, you can add buttons to your Google Sheet and connect them to a script to run the script when they are clicked. That’s what I’ve done in this example.

On the menu, Insert > Drawing...

Google Sheets insert drawing

Create a button using the rectangle tool:

Google Sheet drawing tool

Finally, right click the drawing when it’s showing in your sheet, and choose Assign Script and type in the name of the function you want to run:

Google Sheet drawing assign script

What else?

Use this formula to add stars to your Google Sheet:

=char(9734)

I used the font “Orbitron” across the whole worksheet and, whilst it’s not a Star Wars font, it still has that space-feel to it.

The big Star Wars logo is simply created by merging a bunch of cells and using the IMAGE() formula with a suitable image from the web.

Finally, here’s my SWAPI script on GitHub if you want to use it.

Other APIs to try

Here’s a few other beginner-friendly apis to experiment with:

> Giphy API. Example search endpoint: Funny cat GIFs

> Pokémon API. Example search endpoint: Pokemon no. 1

> Open Movie Database API. Example search endpoint: Batman movies

> International Space Station Current Location. Example search endpoint: ISS current location

Also, here’s the official Google documentation on connecting to external APIs.

Let me know in the comments what you do with all these different APIs!

14 thoughts on “Beginner guide to APIs with Google Sheets & Google Apps Script”

  1. I changed the url from the script as follows:
    UrlFetchApp.fetch(“http://numbersapi.com/random/date”)
    in a hope to see what happened in that date, but the result was still the math related calculation. Should I modify another script? Thanks.

  2. Hi Ben,
    Please forget my previous comment. I can see it properly now without changing anything else. Thanks.
    Regards,
    Julian

  3. Hi Ben,

    Another question to you. Regarding the iTunes API script , it’s fine with function calliTunesAPI( ), however, I got the following error message when run the function displayArtistData( )
    The coordinates or dimensions of the range are invalid. (line 76, file “Code”)
    Could you please tell me what happened? Thanks.

    1. Hi Julian!

      Sounds like the size of your range in sheet.getRange(15,1,len,6) is not matching the size of the output data array you’re trying to paste in. The code wants to paste in values starting at the 15th row and the 1st column, and then paste in data that is len (equal the size of the output array) rows down and 6 columns wide.

      So you need to ensure that your output data matches these dimensions. The len should be ok because it’s based off your output array, so maybe the width is wrong and not equal to 6? Are you adding the index number at the start of your array?

      (this is from the lines:
      sortedOutput.forEach(function(elem,i) {
      elem.unshift(i + 1);
      });

      )

      Hope that helps. Feel free to share your sheet here.

      Thanks,
      Ben

  4. Hi Ben,
    I made a serious mistake without putting any artist name in the Range(11,2). That’s the reason why the mentioned error message showed up. However, When I wrote down “Serah” trying to get my favorite 3 albums from her, a lot of unrelated results also in the list. How can I modify the script to get the results more accurately?
    Thanks,
    Julian

  5. Hi there,

    Have you possibly seen SheetSU. It creates an API url to simply include in your mobile app code.

    Question is, how can we reference your examples from a mobile app.

    1. Hey Ian,

      Thanks for sharing. I’ve heard of Sheetsu before, but never tried it out. It’s a very cool concept. I think it demonstrates another great use case for Google Sheets for quick prototyping. I do intend to check it out at some stage (full plate at the moment), and I’m sure it would make for an interesting case study to write about here.

      Cheers,
      Ben

  6. Ben, this is amazing content. As always, very well done. Do you have tips/tutorials for doing the same thing with a service that requires OAuth 2.0 authentication? For instance, I would love to fetch my running data from TomTom Sports and set those values in a Google Spreadsheet. However, I am struggling with setting up the OAuth 2.0 steps in Google Scripts.

  7. Hi Ben,

    I have tried this great tutorial about api’s. I have tried to make my code to get a price from a site. This is my script :

    function callNumbers() {

    // Call the Numbers API for random math fact
    var response = UrlFetchApp.fetch(“https://api.coinmarketcap.com/v1/ticker/bitcoin/?convert=EUR”);
    var json = response.getContentText();
    var data = JSON.parse(json);
    Logger.log(data);
    Logger.log(data.price_eur);

    }

    When I open the log I get as answer for the price_eur undefined.

    [17-06-11 16:19:24:386 CEST] [{price_usd=2894.94, symbol=BTC, last_updated=1497190466, 24h_volume_eur=1754584216.0, total_supply=16385025.0, 24h_volume_usd=1964380000.0, market_cap_eur=42367748929.0, price_btc=1.0, available_supply=16385025.0, market_cap_usd=47433664274.0, percent_change_1h=-0.26, percent_change_24h=2.15, name=Bitcoin, rank=1, id=bitcoin, percent_change_7d=14.92, price_eur=2585.760408}]
    [17-06-11 16:19:24:386 CEST] undefined

    I don’t see what I’m doing wrong.

    John

    1. Hello Ben,

      I have found the problem :
      I had to add [0] to the last logger.log

      Logger.log(data[0].price_eur);

      Then it works.

      Kind regards,

      John

Leave a Reply

Your email address will not be published. Required fields are marked *