IMPORTAPI() Custom Sheets Function

The API Connector add-on for Google Sheets activates a custom function called IMPORTAPI(). This function works similarly to Google Sheets functions like IMPORTDATA(), IMPORTJSON(), etc. It enables you to call your APIs (in JSON, XML, or CSV format) from directly within a Google Sheets cell, in addition to the standard method of requesting APIs via the add-on sidebar.

Example using the IMPORTAPI() function with the Genderize API to predict gender for a list of names:
import-api-genderize

CONTENTS

WHY USE THE IMPORTAPI() FUNCTION?

  • copy down an API request to run it against a list of input cells,
  • refresh your requests by changing cells instead of using the add-on menu, and
  • get a faster potential data refresh rate than you can achieve through scheduling.
❓ Note that you can’t use scheduling, or create append or stacked requests with the IMPORTAPI() function, as functions can’t change adjacent cells. If you need to schedule or append data, please use the standard sidebar method to construct and run your requests.

IMPORTAPI() SYNTAX

The syntax for this feature is =IMPORTAPI(requestName, url, cell).

  • requestName Required. Refers to the API request name you’ve saved within API Connector. By referencing the request name, the IMPORTAPI() function will automatically make an API call that includes that request name’s headers, report style, and other saved options.
  • url Optional. Allows you to specify a request URL. If you don’t include this argument in your function, your request will use the request URL from your saved request.
  • cell Optional. Refers to any cell in your sheet, e.g. $A$1. Google Sheets will refresh data whenever a function changes, so you can force API refreshes by changing the value in $A$1. This needs to be the third argument, so if you apply it without including the optional URL in your function, you should add an empty string into the second position, e.g. =IMPORTAPI("Binance","",$A$1).

BASIC EXAMPLE

If you’ve saved an API request called Binance, you can now reference this request with the following custom function:

=IMPORTAPI("Binance")

EXAMPLE WITH URL PARAMETER

You can include a request URL parameter, so the request will use this URL instead of the URL in your saved Binance request. All other settings will be retained:

=IMPORTAPI("Binance","https://api.binance.com/api/v1/trades?symbol=BTCUSDT")

EXAMPLE WITH URL PARAMETER AND FAST CELL-BASED REFRESH

This example function will allow you to refresh your data every time the value in cell $A$1 changes.

=IMPORTAPI("Binance","https://api.binance.com/api/v1/trades?symbol=BTCUSDT",$A$1)

Tips for refreshing data

  • Fast cell-based refresh: Populate cell $A$1 using one of Google’s automatically-updating functions, e.g. =GOOGLEFINANCE("CURRENCY:USDEUR"), to force your API request to refresh every time the Google Finance function runs (about every 2 minutes). Just beware of Google’s quotas and limits as your data will stop refreshing if you run too many requests.
  • Checkbox-based refresh: Populate cell $A$1 with a checkbox (Insert > Checkbox) so you can update your sheet each time you check or uncheck the box.

EXAMPLE REFERENCING A CELL

Referencing a cell enables you to base your API calls on changing URL inputs. For example, you can enter BTCUSDT into cell A1, and then construct your custom function like this:

=IMPORTAPI("Binance","https://api.binance.com/api/v1/trades?symbol="&A1)

Now, every time you edit cell A1 with a new currency pair, your data will automatically refresh.

import-api-function-gif

USAGE NOTES & CAVEATS

  • Google runs custom functions under the Sheet owner’s account, regardless of who is actually running the function. This means, if you’re combining IMPORTAPI() with pro features like JMESPath filtering, you might receive an authorization error if you’re not the sheet owner. To resolve, you will need to transfer sheet ownership to yourself, or make a copy of the sheet where you are the owner.
  • Google allows custom functions to run for only 30 seconds, while sidebar requests can run for up to 6 minutes (documentation). Therefore, IMPORTAPI() is not generally suitable for very large or time-consuming requests.

5 thoughts on “IMPORTAPI() Custom Sheets Function”

  1. This is great but I have one question. In the animated example on this site showing when you change cell a1 to another value the data is then refreshed. The formula is actually located in cell c1. I have essentially the same setup on my spread sheet and it works great! BUT when the data comes in after a couple runs it overwrites the formula that is in the cell and then stops the whole auto refresh on change function. How can I fix this?

    Reply
    • I’ve tried and can’t replicate this, in my tests the formula remains a formula and doesn’t get overwritten. Would it be possible for you to share your sheet so I can check it out?

      Reply
  2. Hello Ana! I’ve said it before, I really like what you’ve built, congrats!
    I have a small problem though. Sometimes the IMPORTAPI() function does not load when I create a new GSheet. I can run the request normally but when I try to do it with the IMPORTAPI() function it does not run because it does not find the function. Would love your help here. I tried refreshing the GSheet sometimes and it still doesn’t load the function. Thank you!

    Reply
    • Thank you! And sorry you’re having trouble with the custom function. I think this is an issue with Google Sheets, related to caching or authorization. Two suggestions:
      1) When you create a new sheet, can you please try opening up the sidebar before running ImportAPI() in the sheet? That should initiate the API Connector code, including the custom function.
      2) Clear your cache and do a hard refresh. You can do this by opening Dev Tools (click Ctrl-Shift-J or Cmd-Option-J), then long-press the Refresh symbol and choose “Empty cache and hard reload.”

      Reply

Leave a Comment