How to Use an API in Google Sheets — RapidAPI for 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.

Installing the Plugin

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.
Installation page - RapidAPI for Google Sheets
Installation page - RapidAPI for Google Sheets

Forming the Request

=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.

Subscribing to the API

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.

Gathering the Required Information

Diagram showing where required fields are located
Diagram showing where required fields are located
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.

Results of querying for Lord of the Rings
Results of querying for Lord of the Rings
Results from querying for Avengers
Results from querying for Avengers

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.

Current: Head of Sales Engineering RapidAPI. Former: Head of Developer Relations RapidAPI, Founder of HackCU. Part-time adventurer, full-time hooligan

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store