Requesting data from any API

You’ve got the knowledge and Rows tools to authenticate your requests. But, you’re probably wondering how to get data from an API. Stick with us, as in this article we’ll talk about:

1. Some more basic terms in API documentation
2. How to use Rows’ GET() function to request data

Some basic API terminology

Again, we’ll need to just quickly make sure that we’re all on the same page regarding API terminology.

Finding the right endpoint to access

Endpoints are usually grouped according to the data that they interact with and what you can do with it.

If you have a look at the Spotify API:

You’ll see that they group them by data (Playlists), and then provide you with the base URL, along with the endpoints (along with a description with what you can do). So for example, if you wanted to get all the playlists in your account you’d just need to send your request to this URL (with some authentication, of course):

https://api.spotify.com/v1/me/playlists

But if you look at how to get a specific playlist, you'll see some curly brackets {}? These mean that you need to provide some parameters to the URL. For example, if you wanted to get all the tracks for a playlist:

https://api.spotify.com/v1/playlists/{playlist_id}/tracks

Then you’d need to find out the Spotify ID of the playlist and replace the {} brackets with that ID. For example:

https://api.spotify.com/v1/playlists/00981234jsadf920lu/tracks

So, now you have all the knowledge you need to start getting data from APIs! Let’s try some out in Rows.

Getting data from the OpenWeather API

You’ll never guess what function you need to use in Rows to get data from any API… it’s called GET()!

And you already know what you need to put in for the action_url and headers parameters.

Hint

In action_url you would have the base URL, the endpoint you want to use, and possibly your authentication.

In headers you'd put your authentication (if needed).

Let’s put this all into practice by getting some weather data with OpenWeatherAPI. To give you a head start, we've created a tutorial spreadsheet for you.

• You can duplicate the spreadsheet to your own workspace, then open it in the Editor to see how the functions work.
• Before you can pull data, you’ll need to create an OpenWeather account and get an API key—it won’t take you longer than two minutes.
• Once you have the API key, paste it into cell B2 replacing the placeholder value.

In this excercise, we want to get the current weather for a location, say, Lisbon, Portugal.

To pull data from the OpenWeather API, follow these steps:

1. Go to OpenWeatherMap’s API documentation.

2. In Call current weather data for one location, we can see that we need to add a city name and the API key to the query string to get the current weather.

   API documentation screenshot

   

3. In Rows, make your own copy of the tutorial spreadsheet by clicking Duplicate in the top-right.
   
   The resulting spreadsheet should look like this:
   

   

4. Once you have your API key, replace the placeholder value enter-your-api-key-here with your real API key.
   
   

5. (Optional) Change the default location "Lisbon,Portugal" to whatever city/country combination you prefer.
   
   

Building the action URL

In cell B4, we've used a CONCATENATE formula to combine all the different elements of the action URL that are stored in different cells:

Here's a breakdown of how the action URL is constructed:

Running the GET request

In cell B5, we just need to write =GET(B4).

❇️ Boom! B5 then becomes a {data} cell - open the Data Explorer and look at the results! In the {data} cell, click the Cell Actions button and in the menu that appears, click View Data.

• If you'd prefer to see the result in a table, try creating a Data Table out of the {data} cell.

Once you've got the hang of it, try playing around with a few other cities and countries 😁.

And if you still need more help, check out this handy tutorial from our YouTube Channel: