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.
City | Latitude | Longitude |
San Francisco | 37.773972 | -122.431297 |
New York | 40.730610 | -73.935242 |
Los Angeles | 34.052235 | -118.243683 |
Houston | 29.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.