Demo

In this Demo we’ll import some interesting data.

The International Space Station (ISS) is moving at about five miles per second(!) Where is it right now? When will the ISS pass above me? We’ll import this data and find out.

Create a new request

Click Add-ons -> REST API Connector -> Create New Request

Configure the request

The REST API Connector dashboard will appear. Enter the URL below under URL Path. The Headers and Advanced tabs we will ignore right now.

http://api.open-notify.org/iss-now.json

Pick a Destination Sheet.

Test

Just click on ‘Test’ to test your request. If you also want to save it first give it a name and then click ‘Save’.

After clicking ‘Test’ you should be able to see the current location(latitude and longitude) of the ISS.

The “iss-now” API we just used also has an endpoint for us to find out when the ISS will pas over a certain position.

The endpoint for this is: http://api.open-notify.org/iss-pass.json?lat=LAT&lon=LON

We just need to provide some valid values for the LAT and LON parameters. For this, copy and paste the following list of coordinates into another spreadsheet.

CityLatitudeLongitude
San Francisco37.773972-122.431297
New York40.730610-73.935242
Los Angeles34.052235-118.243683
Houston29.749907-95.358421

We will configure our next request to use cell references so that we do not have to hard code the values.

Create a new request and configure the URL by replacing the LAT and LON values with the reference to your Sheet and the desired cell. In my example, for San Francisco the request will look like this:

http://api.open-notify.org/iss-pass.json?lat=+++Sheet2!B2+++&lon=+++Sheet2!C2+++

The reference format as you noticed is +++SheetName!Cell+++

Go ahead and test this request, be sure to output the result in a different sheet than the one holding the list of coordinates so they don’t get overwritten. You should get something like this:

This was your first request with cell references. Cell references can also be used in the headers and body of your request.

Another cool feature is the IMPORTJSONAPI function you can use directly in your cells. It requires as parameters the request name, url and optionally a cell reference(for automatic refresh).

For the example above you can copy the following function to get the same results in any cell.

=IMPORTJSONAPI("Get ISS Current Location", "http://api.open-notify.org/iss-pass.json?lat=+++Sheet2!B2+++&lon=+++Sheet2!C2+++")

This feature along with the Filter option, scheduled requests and request chaining open up a lot of different ways to import your needed data, making this tool very powerful and easy to use.

Leave a Reply

Your email address will not be published. Required fields are marked *