IMPORTAPI() Custom Sheets Function
***Please note that this feature is still in beta and subject to change. Your feedback is appreciated.***
GET APIS IN GOOGLE SHEETS
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 CSV and JSON APIs from directly within a Google Sheets cell, in addition to the standard method of requesting APIs via the add-on sidebar.
Reasons to use the
- it lets you copy down an API request to run it against a list of input cells,
- it lets you refresh your requests by changing cells instead of using the add-on menu, and
- it provides a faster potential data refresh rate than you can achieve through scheduling.
Note that you can't create append or stacked requests with the
IMPORTAPI() function, as functions can't change adjacent cells. If you need to append data, please use the standard sidebar method to construct and run your requests.
- ImportAPI() Syntax
- Basic Example
- Example with URL Parameter
- Example with URL Parameter and Fast Cell-Based Refresh
- Example Referencing a Cell
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.
If you've saved an API request called Binance, you can now reference this request with the following custom function:
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:
This example function will allow you to refresh your data every time the value in cell $A$1 changes.
Note that if you populate cell $A$1 using one of Google's automatically-updating functions, e.g.
=GOOGLEFINANCE("CURRENCY:USDEUR"), every time the Google Finance function runs (about every 2 minutes), it will force your API request to refresh at the same time. Just beware of Google's quotas and limits as your data will stop refreshing if you run too many requests.
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.