How to Use an API in Google Sheets – RapidAPI for Google Sheets

The RapidAPI Google Sheets Add-on supports two different types of functions

=GET(url, selectPaths, rapidApiKey)
=GETARR(url, arrPath, selectPaths, rapidApiKey)

The main difference between the two functions is how the response of the data is displayed. If an API has a single object as the response, then you will use the GET function. If the API returns an object that contains an array of values, then you will use the GETARR function.
For this tutorial, we’ll be using the GETARR functionality to search the Movie Database (IMDB Alternative) API to get information about a movie search term.

Before being able to make a request to the API, you’ll first need to subscribe to an API plan. To do that, use the ‘Pricing’ tab. There are four different pricing plans currently available for the API we’re using for this example. The most affordable plan is a free BASIC plan, which allows for 1,000 calls per month and $0.01 for each additional request over 1,000. The plan that allows you the highest amount of requests is the $10 per month MEGA plan, which enables you to make unlimited API calls.

If the API you are using does have an overage fee associated, please be sure to be careful about the number of requests that your account is making. You will be charged for any additional usage. You can keep track of your quota in your RapidAPI Developer Dashboard.

For the GETARR function, at the bare minimum requires the url, arrPath, selectPaths, and rapidApiKey. You are also able to provide optional and required query string parameters too. All of this information can easily be found on the APIs listing page from RapidAPI. Here’s where you can locate the information:

URL: "https://rapidapi.com/rapidapi/api/movie-database-imdb-alternative"
arrPath: "Search"
selectPath: "imdbID,Title,Year,Poster"
rapidApiKey: "*****************************"
requiredParameters: "s",{Cell Containing Search Term}
optionalParameters: "page","1","r","json"

With this information in hand, you’re ready to start forming your request! Here’s what the full request will look like:

=GETARR("https://movie-database-imdb-alternative.p.rapidapi.com/","Search","imdbID,Title,Year,Poster","*********************","page","1","r","json","s",B3)

One thing to note here is that after entering all of the required values [url, arrPath, selectPaths, rapidApiKey], you are able to provide the optional and required query string parameters in the following format: ,“key”,”value”,”key2”,”value2”,etc.

When I’m utilizing this tool, I like to format the Spreadsheet by using a cell for the search term since it is the dynamic parameter. This makes it easy to update that particular cell and get a new search result.

The API request is what fills in the IMDB ID, TITLE, Release Year, and Link columns of this spreadsheet. Then as an added bonus, I use the =IMG() function, that is provided by default in Google Sheets, to add in the image of the movie posters based on the link the API returned!

I went ahead and created a template for this specific example to help you get started. The template Google Sheet can be found here.
With this spreadsheet, you can select ‘Add to Drive’ and follow the instructions in this blog post to help you get started. Once you subscribe and have your API key, just add it to the spreadsheet to see your results. For more example use cases, you can take a look at this example spreadsheet that shows you how you can validate a list of email addresses, find stock information, geocode an address.