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
IMPORTJSON(), etc. It enables you to call your APIs (in JSON, XML, or CSV format) from directly within a Google Sheets cell, instead of 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:
- Before You Begin
- Why Use ImportAPI()?
- Comparison: Standard Sidebar vs. ImportAPI()
- ImportAPI() Syntax
- Example 1: Basic Request
- Example 2: With URL Parameter
- Example 3: Fast Cell-Based Refresh
- Example 4: Reference a Cell
- Example 5: Reference Multiple Cells
- Usage Notes & Caveats
BEFORE YOU BEGIN
If you haven’t already, click here to install the API Connector add-on from the Google Marketplace.
WHY USE THE IMPORTAPI() FUNCTION?
- copy down an API request to run it against a list of input cells,
- run requests by changing cells instead of going through the sidebar, and
- get a faster potential data refresh rate than you can achieve through scheduling.
IMPORTAPI()function. If you need to schedule or stack requests, please use the standard sidebar method.
COMPARISON: IMPORTAPI() FUNCTION VS. SIDEBAR
Begin by configuring and saving your request in the sidebar. You can then choose whether to run your API requests through that sidebar or through your sheet using the IMPORTAPI() custom function. The following table identifies the key differences between the two methods.
|Run API requests||✓||✓|
|Include headers, JMESPath, and other options||✓||✓|
|Reference cells in requests||✓||✓|
|Schedule data refreshes at hourly, daily, or monthly intervals||✓|
|Run requests while the sheet is closed||✓|
|Process API requests for up to 6 minutes||✓|
|Process API requests for up to 30 seconds only||✓|
|Trigger requests by clicking a checkbox or changing cells||✓|
|Refresh data every few minutes||✓|
|Quick, dynamic, and a bit hack-y 🙂||✓|
In general, use the standard sidebar for regular, reliable, scheduled reporting. ImportAPI is designed for faster, dynamic API pulls that you use while active in your sheet.
The syntax for this feature is
=IMPORTAPI(requestName, url, cell).
requestNameRequired. 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.
urlOptional. 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.
cellOptional. 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.
EXAMPLE 1: BASIC REQUEST
If you’ve saved an API request called Binance, you can now run this request with the following custom function:
EXAMPLE 2: 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:
EXAMPLE 3: FAST CELL-BASED REFRESH
This example function will allow you to refresh your data every time the value in cell $A$1 changes.
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).
Please beware of Google’s quotas and limits as your data will stop refreshing if you run too many requests. It is highly recommended that you wrap any automatically updating IMPORTAPI() calls in a SWITCH() or IF() statement to prevent excess recalculation (info). For example, you could make a function like this:
=if(B1="on", IMPORTAPI("Binance"),"switched off"), so the function only runs when you enter the text “on” in cell B1.
- 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 4: REFERENCE 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:
Now, every time you edit cell A1 with a new currency pair, your data will automatically refresh.
EXAMPLE 5: REFERENCE MULTIPLE CELLS
Like shown in the first GIF, there are some situations where you may want to “zip” through a list of names or values and perform an API call on each one. Let’s use the genderize API as an example:
In order to accomplish this stacked API output, you will need to go to Output options and then Output Mode. Choose the Append radio button (this removes the headers).
Setup your API call in the adjacent cell and just drag it down to run it for each respective cell. Bam!
Note that “Append” mode does not return the column names, so you will need to manually enter them yourself.
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.
- Google runs custom functions when the sheet is opened and when the inputs to the function have changed. If you have a large number of IMPORTAPI() calls, it’s possible to quickly rack up thousands of requests, thus hitting Google’s 20k/day limit and producing an error message like “Service invoked too many times for one day: urlfetch”. If you are experiencing this issue, move requests to the standard sidebar wherever possible, and try wrapping IMPORTAPI() calls in a SWITCH() or IF() statement to prevent recalculation (source: https://stackoverflow.com/questions/63431445/how-to-make-a-custom-function-not-recalculate-in-google-sheet)
- In some cases, Google doesn’t recognize the IMPORTAPI custom function and will instead return ‘#NAME?’ along with the error message “Unknown function: ‘ImportAPI’. This is an issue on Google’s side associated with all custom functions, not just this one (info). As of now there’s no reliable fix for this, but the following tips usually help: open the add-on sidebar if you haven’t already, try refreshing the sheet a few times, try another sheet, log out of your google accounts or open the add-on in incognito mode (it seems to happen more frequently when you have multiple Google accounts open), wait a few minutes and try again. Usually Google recognizes IMPORTAPI after a few of these efforts.
- ImportAPI is a quick and convenient way to run dynamic API requests while you are active in the sheet, but as shown above, is subject to a number of limitations. If you need reliable, scheduled reporting, please use the standard sidebar and the scheduling feature.