Search API Connector Documentation


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, 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:



If you haven’t already, click here to install the API Connector add-on from the Google Marketplace.


  • 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.
Note that you can’t use scheduling or run multi-query requests with the IMPORTAPI() function. If you need to schedule or stack requests, please use the standard sidebar method.


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

  • 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).


If you’ve saved an API request called Binance, you can now run 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.


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.
These tips for using fast cell-based refresh in your sheet are provided as a convenient, yet hack-y workaround to Google’s scheduling limits. They can only be used when your sheet is open, and may not run reliably even then. For critical reports, please use the standard sidebar method to construct and schedule your 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.



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.


  • 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:
  • 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.

24 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?

    • 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?

  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!

    • 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.”

  3. Hello Ana,

    I am new here and I am trying to discover the potential of this tool by myself and of course I’ve found so much doubts. I am trying to create an API that changes based on a cell on the spreedshet. My request has 2 variables as begining and end that should change accordingly to the cell.

    Is it possible?
    ps: is a date here that should change based on a cell)&end=”(there is a date here that should change based on a cell)

    Kindly regards;

    • Hi Allan! We sent you a support email separately to work through this, but just to leave some info here as well, there are 2 potential ways to do this:
      1) if you’re using the IMPORTAPI() function, you’d basically follow the instructions shown in the “Example Referencing a Cell” section above. Your cell would include a formula like =IMPORTAPI("xyz",""&A1"&"&end="&B1), where cells A1 and B1 contain your start and end dates.

      2) Alternatively, you can use the standard sidebar method and reference cells that way. In that case, if your dates were in cells A1 and B1, your request would look like this:!A1+++&end=+++Sheet1!B1+++

  4. When I run the formula, to an entire column, only the last row shows data, because all above cells show in each of them: “The result in this cell is now showed because it would override the data from the bottom cell.”
    This happens mainly because the Call I’m using gives me 2 rows but one of them is the Header.
    If there is a way that I could drop the Header row for my methods this would be solved.
    Is there any way to do this?

    • Hi there, please try changing to “Append” mode (located under Output Options) and re-saving your request. Append mode will drop the headers.

  5. Hello. Great feature.
    Is there any possibility to add auto refresh like on dally basis for those with a IMPORTAPI?
    And is there any extra limitations from Google Sheets on the number of requests run daily?

    • Hey Glib, there’s no way to set up daily auto-refresh for IMPORTAPI(). It’s basically a function just like SUM() or any other function, so it doesn’t support scheduling, and runs only when the sheet is open. If you want daily refreshes, you can use the standard sidebar method and set up scheduling.

      Google Sheets does have limits on the number of times you’re allowed to run queries, as described here. The most relevant limit is that you may only make 20,000 fetch calls a day.  Normally it’s very hard to run so many requests, but I have seen some people use IMPORTAPI() a little carelessly, and inadvertently hit those limits.

  6. Hi Ana,

    I’d love to use IMPORTAPI in ‘Overwrite’ mode but without headers included, e.g. just like in your example “EXAMPLE REFERENCING MULTIPLE CELLS,” but when I toggle the fast cell based refresh the response data is overwritten. I was thinking about the way QUERY works, where you can just put a ‘0’ in at the end in order to not have headers returned. Is something like that possible with IMPORTAPI?


  7. What would my ImportAPI() look like if I wanted to refresh every 30 minutes. Could I use an if statement combined with some type of now()= etc. or is there an easier way to do this using this function? I pretty much want to be able to have this run every 30 minutes so that when I connect it to a tool, it will be up to date. (side note: the reason I don’t connect the api directly to the tool is because it will not allow me to append results to store long term like this add-in will).


    • Also it appears that the “append” option moves columns around every other time (or at least it does for me). I ran it 4 times and every other time 2 or so of the columns were flipped. This makes it really hard to dedupe. Not sure if anyone else has had this problem too.

      • This isn’t directly related to append mode, it’s related to how the API sends back the data, so you’d see the same column order whether in overwrite or append mode. API Connector displays the data as it gets returned from the API, and some APIs switch the order or leave out fields depending on what fields are populated. You can see some suggestions for remedying this in the troubleshooting guide (check the section called ‘Columns Shifting’).

    • Sorry, there’s no way to set IMPORTAPI() to refresh every 30 minutes. Custom functions behave like any other function in sheets, and can’t be scheduled. If you want to have scheduled refreshes you’d need to use the scheduling function. Google limits all add-ons to 1x an hour scheduling, though, though, so there’s no way to refresh every 30 minutes unless you make your own custom script.

    • IMPORTAPI is just a function to call your sidebar requests, so you put it in the sidebar as usual (generally in the headers or URL query strings, depending on the API).

    • Hi there, IMPORTAPI returns all the data from the API response. If you’d like to limit your data please check the documentation of the API you’re working with. Most APIs have some way to limit or filter the data that they return, or you can check through the docs for an endpoint that returns current data only.

  8. Hi Ana, great work, thanks!

    I’m really confused about how to use this. If i remove the api call in the sidebar, there is an error, so i have to leave it in. I can add the API call in a cell in the sheet that i’ve set it too, this works. But then i I run the sidebar script, it overwrites the entire page right?

    I want to design the page with various date coming in from multiple calls from CryptoCompare. I’m trialling the Pro version of the app so I am able to do that. But if it keeps overwriting the IMPORTAPI cells than how do you even use it?

    I think I’m missing something very obvious, maybe it’s something that you can include in the tutorial above.

    Thanks again,

    • Hi Marc! Thanks for the message and sorry for the confusion. I think the key point here is that IMPORTAPI is generally used as an alternative method of running requests. You store your request details via the sidebar, but then you can choose whether to run them the standard way (by clicking Run), or by using IMPORTAPI. They are 2 different methods, so that’s why you won’t normally have this type of conflict.

      With that said, it is still possible to use them together. In that case you would just set your request output to a different tab from the one you’re using IMPORTAPI in and then the results won’t get overwritten.

      I hope that clarifies things, if not, just let me know and I’ll be happy to explain further.


Leave a Comment