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

In this article, you'll learn how to use an API from RapidAPI right in Google Sheets.

Google Sheets can be a very powerful tool for storing, managing and transforming all kinds of data. I use it all the time for tracking my monthly spending, potential conferences RapidAPI might sponsor, emails of partnership leads, my yearly goals, and much more! With Google Sheets being such a useful tool already, we wanted to help enable anyone to supercharge their sheets through the power of APIs.

The RapidAPI add-on for Google Sheets allows you to make API requests to any API on RapidAPI from within a Google Sheet, and embed the data you get back into the sheet - all through a single function that can be dragged down across many rows

This tutorial will walk you through the basics of using the add-on.

1) Installing the Plugin

To install the plugin, simply head to the Google Sheets add-on store and install the free RapidAPI for Google Sheets add-on. During the installation process, there will be a prompt to accept permissions to run - which are required because this add-on makes requests to external APIs.
By default, the add-on will be disabled in any new spreadsheets that you create. Before using it in a new spreadsheet, you must enable it for that document. To do so:

1. Open the sheet you want to use the add-on in.
2. Open the “Add-ons” menu at the top of the window.
3. Click “Manage Add-ons”
4. You should see the RapidAPI Add-on there - click the green “Manage” button next to it and select “Use in this document”. Once selected it should have a check next to it.

2) Forming the Request

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.

3) Subscribing to the API

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.

Please note. Every time the Google Sheet is opened, the Google Script request is run. What this means is another API request will be made. Make sure you are aware of this when implementing RapidAPI into your Google Sheet.

4) Gathering the Required Information

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 API's listing page from RapidAPI. Here’s where you can locate the information:

URL: "https://rapidapi.com/imdb/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.

We’re really excited to see the ways in which you will use the RapidAPI Google Sheets plugin! Shoot us a Tweet at @rapid_api to let us know what you end up building.