API Tutorial for Beginners with Google Sheets & Apps Script

In this API tutorial for beginners, you will learn how to connect to APIs using Google Apps Script to retrieve data from a third party and display it in your Google Sheets spreadsheet.

Example 1: Connecting Google Sheets to the Numbers API

Let’s start with something very simple in this API tutorial for beginners, so you can focus on the data and not get lost in lines and lines of code.

Step 1: Open a new sheet

Open a new blank Google Sheets spreadsheet and rename it to “Numbers API Example”.

Step 2: Access the script editor

Go to Tools > Script editor…

Step 3: Name your project

A new window will open, and this is where we’ll write our code. Name the project “Numbers API Example”.

Step 4: Add the sample API code

Delete all the existing code in the Code.gs file and replace it with the following:

function callNumbers() {
  // Call the Numbers API for a random math fact
  var response = UrlFetchApp.fetch("http://numbersapi.com/random/math");
  Logger.log(response.getContentText());
}

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

Your code window should now look like this:

Step 5: Run your function

Run the function by clicking the play button in the toolbar.

Step 6: Authorize your script

This will prompt you to authorize your script to connect to an external service. Click on “Review Permissions,” then click “Allow” to continue.

Step 7: View the logs

Congratulations, your program has now run. It sent a request to a third party to get data (in this case, a random math fact), and that service responded with the data.

But wait, where is it? How can we see this data?

Well, you’ll notice that line 5 of our code above was Logger.log(...), which means we logged the text of the response to our logs.

So let’s take a look.

Go to the “Run” menu, then “Execution Logs”

You’ll see your response (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 pop-up window:

Great! Try running it multiple times, checking the logs, and you’ll see different facts.

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

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

You can also insert them directly into your browser if you want to play around. More information on the Numbers API page.

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

Well, that’s quite easy.

Step 8: Add data to the sheet

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

function callNumbers() {
  // Call the Numbers API for a random math fact
  var response = UrlFetchApp.fetch("http://numbersapi.com/random/math");
  Logger.log(response.getContentText());

  var fact = response.getContentText();

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1,1).setValue([fact]);
}

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

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

Finally, at line 9, we get cell A1 (range at 1,1) and set the value of that cell to be equal to the “fact” variable, which contains the text of the response.

Step 9: Run and re-authorize

Run your program again. You will be asked to authorize your script to view and manage your spreadsheets in Google Drive, so click “Allow.”

Step 10: See external data in your sheet

You should now see the random fact displayed in your Google Sheets spreadsheet.

That’s awesome!

To recap our progress so far in this API tutorial for beginners: We have requested data from a third-party service on the internet. That service responded with the data we wanted, and now we have displayed it in our Google Sheets spreadsheet!

Step 11: Copy data to a new cell

The script as written in this API tutorial for beginners will always overwrite cell A1 with your new fact each time you run the program. If you want to create a list and continue adding new facts below the old ones, make this slight modification to line 9 of your code (indicated below), to write the response in the first empty row:

function callNumbers() {
  // Call the Numbers API for a random math fact
  var response = UrlFetchApp.fetch("http://numbersapi.com/random/math");
  Logger.log(response.getContentText());

  var fact = response.getContentText();

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 1,1).setValue([fact]);
}

Your output will now look like this:

One last thing we might want to do with this application is to add a menu to our Google Sheets spreadsheet so we can run the script from there instead of from the script editor. It’s simple and nice!

Step 12: Add code for a custom menu

Add the following code to your script editor:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Numbers API Menu')
      .addItem('Show a random fact', 'callNumbers')
      .addToUi();
}

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

Step 13: Add the custom menu

Run the “onOpen” function, which will add the menu to the spreadsheet. We only need to do this step once.

Step 14: Run your script from the custom menu

Use the new menu to run your script from the Google Sheets spreadsheet and watch random facts appear in your Google Sheets spreadsheet!

Alright, ready to try something more challenging?

Let’s create a music discovery application in Google Sheets!

Example 2: Music Discovery Application using the iTunes API

This application retrieves the name of an artist from the Google Sheets spreadsheet, sends a request to the iTunes API to retrieve information about that artist, and then displays the albums, song titles, artwork, and even adds a link to a song preview.

It’s actually not as difficult as it sounds.

Getting Started with the iTunes API Explorer

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

Clear the existing Google Apps Script code and paste this code to get started:

function calliTunes() {
  // Call the iTunes API
  var response = UrlFetchApp.fetch("https://itunes.apple.com/search?term=coldplay");
  Logger.log(response.getContentText());
}

Run the program and accept the required permissions. You will get output like this:

Woah, there’s a lot more data returned this time, so we’ll have to filter it to extract the items we’re interested in.

Analyzing the iTunes data

So try this. Update your code to parse the data and extract some information:

function calliTunes() {
  // Call the iTunes API
  var response = UrlFetchApp.fetch("https://itunes.apple.com/search?term=coldplay");

  // Parse the JSON response
  var json = response.getContentText();
  var data = JSON.parse(json);

  Logger.log(data);
  Logger.log(data["results"]);
  Logger.log(data["results"][0]);
  Logger.log(data["results"][0]["artistName"]);
  Logger.log(data["results"][0]["collectionName"]);
  Logger.log(data["results"][0]["artworkUrl60"]);
  Logger.log(data["results"][0]["previewUrl"]);
}

• Line 4: We send a request to the iTunes API to search data about Coldplay. 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 contextual text of the response and then parse the JSON response to get the native object representation. This allows us to extract different parts of the data.

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

You can see it’s an object with a curly brace at the beginning {.

The structure looks like this:

{ resultCount = 50, results = [ ….the data we’re interested in…. ] }

You can see that it’s an object with a curly brace at the beginning {.

The structure looks like this:

{ resultCount = 50, results = [ ….the data we’re interested in…. ] }

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

data["results"]

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

data["results"][0]

This displays all the available information from the iTunes API for that specific artist and album:

data["results"][0]["artistName"], data["results"][0]["collectionName"], data["results"][0]["artworkUrl60"], data["results"][0]["previewUrl"]

to give the following output:

Use comments (“//”) at the beginning of a line to prevent the Logger from logging the complete data objects if you want. That is, modify lines 10, 11, and 12 like this:

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

This will make it easier for you to see the details you are extracting.

Putting it all together in an application

If we want to build the application shown in the GIF at the beginning of this article, we need to go through a few steps:

1. Set up the Google Sheets spreadsheet
2. Retrieve the artist name from the Google Sheets spreadsheet using Google Apps Script
3. Request data from iTunes for that artist using Google Apps Script
4. Parse the response to extract the relevant data object using Google Apps Script
5. Extract the specific details we want (album name, song title, album artwork, preview URL)
6. Clear all previous results in the spreadsheet before displaying new results
7. Display the new results in our Google Sheets spreadsheet
8. Add a custom menu to run the program from the Google Sheets spreadsheet, not the script editor

It’s always good to write a plan like this before committing to writing lines of code. This way, you can think about the whole application and what it will do, allowing you to make efficient choices for your code setup.

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

Have fun with these different APIs!