Getting data from any API using http GET

Let's learn about the HTTP GET request to retrieve data from any API using Rows.

New to Rows?

Rows is the spreadsheet with superpowers, where anyone can build automation and internal tools without code. It has 40+ built-in data integrations with the tools you use everyday, and a powerful sharing experience to instantly turn any spreadsheet into a web app, a form, or a dashboard.

Get started for free.


You’ve got the knowledge and Rows tools to authenticate your requests. But you’re probably wondering how to test and 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 directly on your spreadsheet.
  3. How to use a POST() function to send data to a spreadsheet

Some more basic terms in APIs

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

Base URL
The Base URL (also called the Request URL) is the URL that you need to use to access the API. Look for these terms in the API docs.

Endpoint
The Endpoint is where the data that you want to interact with is located. You add it to the base URL after a forward slash /.

Query
This is what you want to get. After the endpoint, you’ll see a ? followed by a lot of key-value pairs, joined with &.

Now, all these three things together create what in Rows we refer to as the action_url:

kb-adv-URL-example

How to know which endpoint you need to access?
Well, that’s up to you 😅. 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:

kb-spotify-endpoints-example

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

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

kb-function-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 testing some API requests on a dummy API.

JSON Placeholder API

We’ll be using a free dummy API provided by JSON Placeholder to clearly illustrate some examples. Of course, you are free to use the API of your choice.

The base URL for JSON Placeholder is https://jsonplaceholder.typicode.com

JSON Placeholder has 6 different endpoints

  • /posts
  • /comments
  • /albums
  • /photos
  • /todos
  • /users

In our test, we'll request from the /posts endpoint.

On top of this, JSON Placeholder also allows you to get a specific record ID from each endpoint, by giving a value from 1 to 100 after the endpoint. E.G /posts/1

To get set up, we will create a table to add all the options and lay them out in a logical order.

jsonplaceholder table logic

Fill in your spreadsheet with the following info:

CellContent
A1Base URL
A2jsonplaceholder.typicode.com
B1Endpoint
B2/posts/
C1Record ID
C21
D1Action_URL
D2=CONCATENATE(A2,B2,C2)
E1GET()
E2=GET(D2)

As soon as you place all the info in the table and hit Enter in cell E2, the API request will be made and the cell will populate with a {data} cell.

Congratulations 🥳! You just made your first API call in Rows.

You can now inspect the data you received by clicking the {data} cell and choosing "Inspect data". In this inspection window, you can also quickly add the response value to any cell, which will add the PARSE() function formula to that cell. Read more about Parsing JSON data here.

Alternatively, you can also create a data table from the {data} cell by clicking on it and choosing "Create new Data Table" from the menu.

create new data table

Try exploring more functionality from the JSON Placeholder API. Try testing a new endpoint or try getting all records, by leaving the value in C2 blank.

Sending data

Using the Rows API, you are able to send data directly to a spreadsheet's table by using two distinct methods.

  1. Overwriting cells in a range
  2. Appending values to a range

Please review the full API documentaiton regarding this method.

We also have a template available to help you get started quickly and to have an example of this at your finger tips to experiment with. You will find the 2 relevant API requests in the Spreadsheet page and in the tables named Overwrite cells in range & Append values to range respectively.

Up next

OK, so you learned some theory as well as successfully used an API call in Rows to get weather data! That’s pretty amazing, right?

More for Advanced users

Want to learn more? Well then, check the rest of our Advanced Rows series:

  1. Authenticating your API calls
  2. Getting data from any API (you're here)
  3. Sending data to any API: Up next! 🚀
  4. Modifying JSON
  5. Filtering with JsonPath
  6. Managing multiple integrations