Sending data to an API

Learn how to send information to an API using POST or PUT.

APIs are good for getting data from a service, but also sending data to a service.

In this article, you'll learn about:

The request body
Creating entries with POST()
Updating entries with PUT()
Sending tables of information with RANGE2JSON

Httpbin

We'll be using a service called Httbin to make our PUT and POST calls. This is because we don't want to abuse any API by asking you to make these types of calls that make actual changes in the their databases.

Request Body

Whenever you make a POST or PUT request, you need to send some information along with it containing the values you want to create. This information is called the body of the request - and most of the time it’s just a JSON object or array.

You’ll need to have a look at the API documentation of the service that you want to use in order to find out exactly what information is required and how to send it to that service (the endpoint, the required fields, and the structure of the JSON). However, we’ve scraped together a few examples from the web and provided some helpful information:

Spotify: Create a playlist

kb-advanced-spotify-example

To create a playlist in Spotify (aside from the Authentication header), we can see that to create a playlist, the request body must have a name. And that's all! But we can also add a description:

1{
2  "name": "A spreadsheet playlist",
3  "description": "Let’s make some spreadsheets."
4}

You just need to create a JSON object with those keys and values, and put the in the body parameter.

To create the JSON object, use PAIR2JSON(“name”, “A spreadsheet playlist”, “description”, “Let’s make some spreadsheets.”).

Spoonacular: Add to Shopping List

kb-advanced-spoonacular-example

To add an item to a shopping list, we can decipher from their documentaiton that only item and parse are mandatory. You just need to send a JSON object with these keys and values in the body of your request.

To create the JSON object, use PAIR2JSON().

Google Books: Add a book to your bookshelf

kb-advanced-books-example

The Google Books API is quite different, as you send the information you want to update in the action_url as a query, and not as JSON in a body.

If you look at the example they provide, you can see that after the endpoint addVolume, we need to send a volumeID key with a value.

Once you’ve deciphered how an API wants to receive the information, then it’s simply a matter of plugging that into our POST() or PUT() functions.

Using POST

Httpbin authentication and endpoints

Httpbin does not require any authentication. To make POST requests, the endpoint is: https://httpbin.org/post. To make PUT requests, the endpoint is: https://httpbin.org/put.

Let’s try using our POST() function to send something to httpbin. We’ll use the documentation example from Spotify to create a playlist, which basically just needs this in the body:

1{
2  "name": "A spreadsheet playlist",
3  "description": "Let’s make some spreadsheets. "
4}

Create a new table and set it up so it looks like this:

kb-post-newUI

Add the following content into your spreadsheet:

Cell	Content
A2	`https://httpbin.org/post`
C2	name
C3	description
D2	A spreadsheet playlist
D3	Let’s make some spreadsheets.

In cell B2, use PAIR2JSON() to create the JSON body from the keys (C2, C3) and values (C3, D3).
In cell E2, we'll write in our POST() function. Have a look at the syntax below and try writing it in yourself (you won't need the headers or response_type parameters).

kb-function-post

If you put it all in correctly, you'll see a {data} cell in E2. Open the Data Explorer, you should see a {data} cell and when you open up the Data Explorer, you should see that the data object contains the information that you sent through:

kb-post-httpbin-newUI

If you did it with the actual Spotify API, you would now see that playlist in your account!

Using PUT

We use PUT() when we want to update some data that already exists in an application. The function syntax is identical to POST().

kb-function-put

Often, you’ll need to know the ID of an entry to update it - and you can do that just by using an appropriate GET() call.

Be careful with PUT

You need to be careful when using this request with APIs. Depending on the API, it can just update the elements you want to update and leave the rest of the data in the entry alone. In others, it’ll update those elements, but clear the rest of the information for that entry (sometimes referred to as a destructive update).

Try it out! In the table you already created, change A2 to https://httpbin.org/put and then just replace your POST() with PUT().

Using RANGE2JSON()

Sometimes you have to send a lot of information to an API. In that case, when you use PAIR2JSON() you have to do a lot of clicking.

kb-RANGE2JSON-newUI

For example, to get all this into a JSON object, our PAIR2JSON() formula would look like this:

kb-PAIR2JSON-formula-example

Now, instead of doing that, we can use RANGE2JSON(), sprinkled with a little magic.

kb-function-range2json

Converting ranges to objects

RANGE2JSON() creates JSON arrays. So in order to turn an array into an object, we need to remove the square brackets.

=SUBSTITUTE(SUBSTITUTE(RANGE2JSON(A1:G1,A2:G2),"[",""),"]","")

Which, if we then copy and paste as values into another cell, will give us a JSON object!

Don’t worry if you’re not sure about what just happened in that last step - we’re going to go over that in the next lesson! :) Oh, and if you want to read a bit more about RANGE2JSON(), check out our platform article on the function.