Search API Connector Documentation
ImportAPI Custom Function
The API Connector add-on for Google Sheets activates a custom function called IMPORTAPI. This function works similarly to 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, 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
- Example 6: Apply ImportAPI to a List of Cells
- Excess Recalculation
- Error Messages
- Usage Notes
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 opening the sidebar,
- get a faster potential data refresh rate than you can achieve through scheduling.
IMPORTAPIfunction. If you need to schedule requests, please use the standard sidebar method.
Comparison: ImportAPI vs. Sidebar
Either way, begin by configuring and saving your request in the API Connector sidebar. You can then choose whether to run your API requests through the sidebar (by clicking Run) or through your sheet using the IMPORTAPI custom function. The following table identifies the key differences between the two methods.
|Run API requests||✓||✓|
|Set headers, authentication, report styles, etc.||✓||✓|
|Use cell values in requests||✓||✓|
|Schedule background data refreshes at hourly, daily, or monthly intervals||✓|
|Process API requests for up to 6 minutes per execution||✓|
|Process API requests for up to 30 seconds only||✓|
|Trigger requests by clicking a checkbox or changing cells||✓|
|Quick, dynamic 🙂 and sometimes error-prone 🙁||✓|
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. If you encounter errors, please check the section on Error Messages for help resolving them.
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
IMPORTAPIfunction 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.
If you want to use the same URL from your saved API request, you can leave that parameter out, like this:
Add a checkbox: 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.
Note: You can reference part of the URL (as shown above) or the whole URL. To reference the whole URL, use a function like this:
Example 5: Reference Multiple Cells
IMPORTAPI works like any other Sheets function, so you can concatenate cells into your URL using multiple ampersands. For example:
Example 6: Apply ImportAPI To a List of 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 Remove header row option to avoid printing out headers.
Setup your API call in the adjacent cell and just drag it down to run it for each respective cell. Bam!
Since we've removed header rows, you will need to manually enter them yourself at the top.
Google runs custom functions when the sheet is opened, refreshed, or changed. It may also run functions after a period of inactivity as well.
Therefore, if you have a large number of IMPORTAPI calls, it's possible to quickly rack up thousands of requests. This can hit Google's limits, as well as the limits of the API you're calling. When you reach these limits, your data will usually stop refreshing altogether.
To limit how often custom functions run, try the following suggestions:
- If you're running requests to a paid API, don't use IMPORTAPI. Run requests through the sidebar instead for precise control over execution times (i.e. to avoid any surprise charges).
- Move your IMPORTAPI requests into a separate worksheet, to avoid inadvertently triggering API calls when you're working on unrelated data calculations.
- Under File > Spreadsheet Settings > Calculation, check that the Recalculation setting is "On change" and not "On change and every minute" or "On change and every hour".
- Wrap any automatically updating IMPORTAPI calls in a SWITCH() or IF() statement (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.
- If you need to run a large quantity of queries, move requests to the standard sidebar. Many APIs provide some sort of batched request feature, and/or you can use API Connector's multi-query function to cycle through a list of requests.
ImportAPI is a quick and convenient way to run dynamic API requests while you are active in the sheet, but is subject to a number of limitations. If you are unable to resolve the following errors, please run your requests through the sidebar instead of through the ImportAPI custom function.
- Error: Sheet owner is over the daily IMPORTAPI runs limit
- Internal error executing the custom function
- Pagination is only available for paid users
- Multiple request bodies available only for paid users
- OAuth2 connections are available only for Business plan users
- Request failed: Access not granted or expired
- Request failed, cannot find function split in object true
- Request failed: request.options.url.split is not a function
- Request 'XYZ' doesn't exist!
- Service invoked too many times for one day: urlfetch
- Too many errors, request paused
- You do not have permission to call insertSheet
- You do not have permission to call setValues
Unknown function: 'ImportAPI'
This error occurs because, in some cases, Google doesn't recognize the IMPORTAPI custom function at all. This is a bug on Google's side associated with custom functions (info). As of now there is no reliable fix for the above issue, but here are a few things you can try:
- Re-open the sidebar and run your saved request. This usually makes Google recognize the custom function.
- based on this this comment, click Extensions Add-ons Manage add-ons. Next to the add-on, click OptionsUse in this document. After toggling off and on it may work again.
- Another option is to copy your requests into a new sheet, it often works in one sheet but not another.
- Refresh your sheet, or wait and try again later.
Error: Sheet owner is over the daily IMPORTAPI runs limit
As of September 2023, to help prevent recurring issues with excess recalculation and Google quotas, API Connector limits IMPORTAPI requests to 5000 a day.
To see how many IMPORTAPI requests are running through your account, open the Account screen (click Account in the footer of API Connector) and navigate to the Sheets tab.
Internal error executing the custom function
This error comes from Google, and means that your custom function has taken too long (more than 30 seconds) to execute (documentation).
To address, reduce the size of your request or run your request through the sidebar rather than through IMPORTAPI.
You may see some cells get stuck with a "Loading..." error. See here and here for relevant discussions, or here to star this issue and drive more attention in Google's issue tracker. Generally the problem occurs when updating a lot of cells at once. You can usually fill in the missing values by changing the inputs of affected cells one by one to force a recalculation.
Pagination is only available for paid users
Multiple request bodies available only for paid users
OAuth2 connections are available only for Business plan users
Request failed: Access not granted or expired
Google associates custom functions with the Sheet owner's account, regardless of who is actually running the function. This means, if you're combining IMPORTAPI with pro features like pagination or OAuth2 connections, you might receive an authorization error where your access level isn't recognized, even though you are a paid user.
You can usually resolve this issue by transferring sheet ownership to yourself, or making a copy of the sheet where you are the owner.
Request failed, cannot find function split in object true
This error occurs when you are missing a middle argument in your function. To resolve, make sure you've included the middle argument, even if it's empty, like this:
Request failed: request.options.url.split is not a function
This error occurs when IMPORTAPI is wrapped inside an array formula. To resolve, copy down the function for each cell input instead of applying an array formula.
Request 'XYZ' doesn't exist!
This error means that the request name you've entered into your IMPORTAPI function doesn't match the request name saved in the sidebar. The IMPORTAPI function should look like
=IMPORTAPI("Get Data"). Substitute in your own saved request name where it says Get Data.
Service invoked too many times for one day: urlfetch
This error message is from Google. Google allows just 20k urlfetches a day, across all scripts, extensions, and functions you may be running. See here for more information and ideas to reduce your API calls.
Too many errors, request paused
API Connector will automatically stop running a request through automated functions (IMPORTAPI or triggers) if the request returns more than 100 errors in a row.
To reset the counter and enable the request to run automatically again, successfully run the request once, or simply open and re-save it.
You do not have permission to call insertSheet
This means that the saved sidebar request sets a destination sheet that doesn't exist. To resolve this error, make sure the destination sheet of the saved request exists (even if IMPORTAPI is used in a different sheet).
You do not have permission to call setValues
This error sometimes occurs when you wrap your IMPORTAPI in other formulas, such that the result would expand outside the immediate cell. As discussed here, the documentation says "if a custom function returns a double array, the results overflow the cell containing the function and fill the cells below and to the right of the cell containing the custom function". Custom functions are not permitted to set values in adjacent cells.
- Standard API Connector request limits do not apply, but the IMPORTAPI function is limited to 5K requests a day. This limit is to help prevent requests from hitting up against Google's limits and quotas, thus triggering "Service invoked too many times for one day" and other errors from Google.
- IMPORTAPI only works with single cell inputs (i.e. it doesn't work when combined with an array formula).
- The IMPORTAPI function (like all functions) is localized by Google. That means that if you're in Europe, you may need to replace the commas (
,) with semi-colons (
;) for the requests to work.