Consume custom APIs with dashdash?

New to APIs? Never used web requests in your life? You think only developers can do this? :grimacing: :persevere:
Wrong! You shouldn’t be afraid any longer. With dashdash, it’s simple. :fireworks: Here’s how:

What is an API?
API is the acronym for Application Programming Interface. Essentially, these are interfaces through which online services exchange data. APIs have endpoints that structure data in a particular way. If you ask the API for data or if you send it data in this particular way, you can interact with it. This is done by web requests, such as GET and POST. Most APIs have good documentation that explain you how to interact with it.

If you need to grab or send data, and you think there should be an online service to do it, there probably is. Just try googling API for X and you likely find what you need and you can click to read through the API documentation. Again, this might be hosted on developers.xxx.com, but this is not only for devs. Now, it’s also for you! :raised_hands:

How can I use APIs with dashdash?
dashdash currently supports GET and POST as formulas for generic web requests.

You need to find out:

  1. What API you want
  2. How to send the request
  3. What the response looks like
  4. How to authenticate your request
  5. How to copy everything into the dashdash formula

Let’s find out using an example

1. What API you want
Let’s say we want to find the address of businesses in our city. So we google API for finding a business address. Among the first hits are the Google Places and Google Maps Geocoding APIs. There are many more, but let’s try those (you actually find out after one click that the Google Places API is just an umbrella for many different ones).

2. How to send the request
So we are in the Google Maps Geocoding API documentation, and we are expecting that we can request something like Apple NYC and get an address of a business called “Apple” in “NYC” back from the API server.
In this part of the documentation, you find how to send the request. It looks like this:

https://maps.googleapis.com/maps/api/geocode/json?address=1600+Amphitheatre+Parkway,+Mountain+View,+CA&key=YOUR_API_KEY

3. What the response looks like
If we send such a request to this API, it will send back a response as a JSON file. You find the example right below the request example. It looks like this:


{
   "results" : [
      {
         "address_components" : [
            {
               "long_name" : "1600",
               "short_name" : "1600",
               "types" : [ "street_number" ]
            },
            {
               "long_name" : "Amphitheatre Pkwy",
               "short_name" : "Amphitheatre Pkwy",
               "types" : [ "route" ]
            },
            {
               "long_name" : "Mountain View",
               "short_name" : "Mountain View",
               "types" : [ "locality", "political" ]
            },
            {
               "long_name" : "Santa Clara County",
               "short_name" : "Santa Clara County",
               "types" : [ "administrative_area_level_2", "political" ]
            },
            {
               "long_name" : "California",
               "short_name" : "CA",
               "types" : [ "administrative_area_level_1", "political" ]
            },
            {
               "long_name" : "United States",
               "short_name" : "US",
               "types" : [ "country", "political" ]
            },
            {
               "long_name" : "94043",
               "short_name" : "94043",
               "types" : [ "postal_code" ]
            }
         ],
         "formatted_address" : "1600 Amphitheatre Parkway, Mountain View, CA 94043, USA",
         "geometry" : {
            "location" : {
               "lat" : 37.4224764,
               "lng" : -122.0842499
            },
            "location_type" : "ROOFTOP",
            "viewport" : {
               "northeast" : {
                  "lat" : 37.4238253802915,
                  "lng" : -122.0829009197085
               },
               "southwest" : {
                  "lat" : 37.4211274197085,
                  "lng" : -122.0855988802915
               }
            }
         },
         "place_id" : "ChIJ2eUgeAK6j4ARbn5u_wAGqWA",
         "types" : [ "street_address" ]
      }
   ],
   "status" : "OK"
}

Learn how to organize this neatly into cells with PARSE here.

4. How to authenticate your request
You probably noticed that last part of the request example: ...key=YOUR_API_KEY. This is because of API authentication. Some APIs don’t require any authentication but most do. You can simply look for Authentication or Get API Key in the documentation or in your account. In this example, there’s a Get API Key link on the left-side menu. You can simply get yours there.

5. How to copy everything into the dashdash formula
Finally, you can copy everything together in your formula. It will look like this:

=GET("https://maps.googleapis.com/maps/api/geocode/json?address=Apple+NYC&key=AIzaSyCxAbPzVrPCNG8X2pDaXXXXXX-XXXXXXXX")

Of course, you can now decide to write your business search term in another cell, say in A1, and you reference the request dynamically to it in the way you already know how to do it with spreadsheets.

=GET("https://maps.googleapis.com/maps/api/geocode/json?address="&A1&"&key=AIzaSyCxAbPzVrPCNG8X2pDaXXXXXX-XXXXXXXX")

The content of your cell will now show the JSON. And if you know how to use PARSE it, you can easily grab the address, e.g. in this case with

=PARSE(B1,"results.[0].formatted_address")

Congratulations! That’s it! You now learned how to interact with APIs using dashdash! Get business addresses, company information, stock prices, send emails or SMS? You name it. There are thousands and thousands of APIs waiting for you on the web. :raised_hands::raised_hands::raised_hands:

And we are working hard to make this easier and easier. Stay tuned!

2 Likes

Hi Torben,

Custom API’s is the feature I’m most interested in. However, I always seem to hit a roadblock in one form or another. The majority of the time this comes from step 4 in your post. Authentication.

It’s very easy to understand when its just a parameter attached the the url as in your example but anyhting more than that and wokring it out becomes a bit trickier (for me at least).

I would like to give Twitch as an example.

When an API request requires authentication, send the access token as a header. The header differs, depending on which API you use:

In the new Twitch API:
curl -H "Authorization: Bearer <access token>" https://api.twitch.tv/helix/

I haven’t the slightest clue how to achieve this in dashdash. Any assistance in this regard would be greatly appreciated.

The whole topic on “headers”, “bearer” etc and other lingo should be addressed to help us understand how to use them. A tutorial on the subject would be HUGELY appreciated! :slight_smile:

2 Likes

Hey Colin,

Thank you for reaching out. That is a great question!
So in order to use headers in a GET, POST, or PUT request, it needs to be a JSON object. The easiest way to do that would be to use PAIR2JSON() or RANGE2JSON().

The PAIR2JSON function allows you to create a JSON from data pairs where each pair consists of an attribute and a value. You just select the objects one by one to be converted to JSON.
Similarly, RANGE2JSON allows you to create a JSON consisting of an array. All you have to do is define the headers containing the attributes as well as the range consisting of the values, and it will take care of the rest.
When sending your GET, POST, or PUT request, you can simply refer to the JSON objects you created as header or body.

In your case, let’s assume:

A1: https://api.twitch.tv/helix/
A2: Authorization
A3: Bearer <access token> (replace <access token> with the token)

You can create the header as a JSON object using:

B1: =PAIR2JSON(A2,A3)

Then just pass the API URL and the header to the GET function:

B2: =GET(A1,B1)

Make sure to check out the Youtube tutorials below for more information about interacting with APIs on dashdash.

Please let me know if you have any other questions or if you need any further help.

Have a great day!

3 Likes

Hi @hadyelhady,

Thanks for the extensive reply! I will review this as soon as possible and I am sure I will have more questions.

Thanks again for your help!

1 Like