API Connector Documentation

Print

ImportAPI Function

Last Updated OnMarch 19, 2024
byAna
< Knowledge Base

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:
import-api-genderize

Contents

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.
Note that you can't use scheduling with the IMPORTAPI function. 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.

Standard
Sidebar		ImportAPI
Custom
Function
Run API requests✓✓
Set headers, authentication, report styles, etc.✓✓
Use cell values in requests✓✓
Apply formulas through the field editor✓
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.

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

Example 1: Basic Request

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

=IMPORTAPI("Binance")

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:

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

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.

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

If you want to use the same URL from your saved API request, you can leave that parameter out, like this:

=IMPORTAPI("Binance","",$A$1)

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.
import-api-img6

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:

=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


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:

=IMPORTAPI("Binance",A1)

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:

=IMPORTAPI("myRequest","https://api.com?startDate="&A1&"&endDate="&B1)

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:

=IMPORTAPI("Genderize","https://api.genderize.io?name="&A2)

In order to accomplish this stacked API output, you will need to go to Output options and choose the Remove header row option to avoid printing out headers.
importapi-removeheaders

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.

Excess Recalculation

To mitigate issues related to excess recalculation, API Connector applies a limit of 7K IMPORTAPI calls a day.

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.
The "Service invoked too many times for one day: urlfetch" and similar error messages are often associated with IMPORTAPI excessive recalculation, but could be due to other scripts or functions. To see which services are executing requests, check https://script.google.com/home/executions.

Error Messages

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.

#NAME?
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 and then Add-ons and then Manage add-ons. Next to the add-on, click Optionsand thenUse 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

To help prevent recurring issues with excess recalculation and Google quotas, API Connector limits IMPORTAPI requests to 7000 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.

Loading...

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.

OAuth2 connections are not available on this plan
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 features that the sheet owner doesn't have access to, 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: =IMPORTAPI("saved request","",$A$1)

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.

Usage Notes

  • Standard API Connector request limits do not apply, but the IMPORTAPI function is limited to 7K 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.

80 thoughts on “ImportAPI Function”

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

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

  4. 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: https://api.xxxxxxx.com.br/reports/l1281?page=1&limit=1000&begining=(there 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;

    Reply

    • 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","https://api.xxxxxxx.com.br/reports/l1281?page=1&limit=1000&beginning="&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: https://api.xxxxxxx.com.br/reports/l1281?page=1&limit=1000&beginning=+++Sheet1!A1+++&end=+++Sheet1!B1+++

      Reply

  5. 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?

    Reply

    • Hi there, please use the "Remove header row" or "Append" mode options (located under Output Options) and re-save your request. Those options will drop the headers.

      Reply

  6. 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?

    Reply

    • 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 it's possible to use IMPORTAPI() a little carelessly and inadvertently hit those limits.

      Reply

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

    Thanks!

    Reply

    • Hey Fred, you can just save your query in append mode, then when you call it with ImportAPI() it won't print out any headers. Update: there's also an option now called "Remove header row"

      Reply

  8. Hi,
    I have a question regarding to the autorefresh function with import api. I am referencing the $A$1 cell to the googlefinance currency so it updates every 2 minutes as you suggested. I am referencing the importapi to the saved request that pulls data. Therefore, the importapi looks like =importapi("saved request", $a$1) - when i do it i see this error "Request failed: Cannot find function split in object 0.842075.". What should I do in that case?

    Reply

    • Hi Wojtek, I think the problem is that you're missing the middle argument in your request. Can you please try the following instead?
      =IMPORTAPI("saved request","",$A$1)

      Reply

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

    Thanks!

    Reply

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

      Reply

      • 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 suggestions for remedying this in the troubleshooting guide.

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

      Reply

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

      Reply

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

      Reply

  12. 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,
    Marc

    Reply

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

      Reply

  13. Hello! An incredibly helpful article! You explained the sequence of actions very easily, thank you!
    I have a few questions:
    1) is it possible to make the data updated? Perhaps by a button that will start the update. Because it helped me only to restart the API (that is, to do everything again according to your instructions). I need to update such a table two or three times a day.
    2) I also need to save the previous data of this table so that I can compare the "updated data" and "previous data".
    I'm only interested in the price of the cryptocurrency to USDT

    Reply

    • 1) when I try to update the table via the "Refresh All Now" button, the system shows me the error "ERROR can't find sheet undefined"

      Reply

      • 'ERROR can't find sheet' generally means you've deleted or renamed your output sheet. Just open up your request and choose a new output sheet, that should resolve the issue.

    • 1) Hi Renne, the data will update any time you make a change in the sheet. I also listed some other ways to refresh your data in this article, like through a checkbox. Or if you're using the sidebar, you can click Run.
      2) Some APIs let you choose a date for your data pull, or you can try my other add-on Archive Data for this.

      Reply

  14. Hello Ana,

    I'm trying to install the checkbox trick:

    - When I uncheck the box, it works : the api is working properly

    - When I check the box I get this error: "Request failed, cannot find function split in object true"

    Is there a solution?

    Thank you very much for your help!

    Have a nice day

    Reply

  15. Hello, thanks for this great article but I have a question regarding the checkbox refresh method.

    I did the exact same as displayed in the article but I have this error : Formula analysis error (translated from French)

    =IMPORTAPI("SOL/EUR","",$Q$3) is the content of this cell and Q3 is the cell with the checkbox.

    I wonder what I did wrong because if I only do =IMPORTAPI("SOL/EUR"), it works fine.

    Thanks in advance!

    Reply

  16. Hello. Just to start API Connector is maybe the best thing to ever happen to Google Sheets so thank you very much!

    One question I have and I guess it's more of a feature request is to be able to launch a request in Google AppScripts. I'm currently using a script that inserts the =IMPORTAPI("PostTest") into a cell to trigger the request and then removes it after a set time to avoid the unwanted extra triggering.

    But I would also like to keep the response data which currently I'm using another Script to copy to another sheet before the timer to delete IMPORTAPI Function runs out.

    My use case is with a Google AppSheet app and Sheets as the database. with Appsheet triggering scripts in Sheets to do certain tasks.

    Just an idea as with AppSheet/Sheets + API Connector this has opened up a massive low code environment for Apps to be made and being able to use API Connector in AppScripts would be amazing!

    Thanks, Jo

    Reply

    • Wow, this is a very original/unusual process, I haven't thought of placing and removing a request through a script. I don't think it's possible for an outside script to access any of the code within API Connector, so I'm not sure how to add in a hook as you suggest, but if you can write a script to add/remove functions, then you can probably write a script to run an API request, right? 😀 I'm happy to chat more about what you have in mind, so feel free to send me a message.
      Btw, you might also want to check out my other (totally free) add-on Archive Data as it lets you copy/paste data on a schedule.

      Reply

  17. Hello, I am using the =IMPORTAPI formula and it seems to be working great. However whenever I try to use the results from the API in a formula the numbers are formatted as text. I confirmed this by using the =ISNUMBER() formula, which returns a false value. The issue is that I cannot seem to change the format (to number) of the cells the API returns. Any help is appreciated. Thanks!

    Reply

    • Hey Danny, IMPORTAPI doesn't convert numbers to text, or actually do anything at all to the format. You can definitely get numbers back as numbers with IMPORTAPI, so in this case it seems like the API response is actually text, or at least something about it causes Sheets to interpret the value as text.
      To resolve, you'll need to manipulate the value in some way to ensure that Sheets see it as a number.
      Update: You can now apply a Sheets formula through the field editor to force data into a specific format.

      Reply

  18. Hi Ana,

    nice API. I have trouble to get the API Refresh running.

    I tried this command: =IMPORTAPI("Binance","",$A$1)

    The tablesheet where I want to write the data from binance is called: symbols and I want to run the refresh command from another tablesheet called "myOrders".

    =IMPORTAPI("symbols","",L23)

    At L23 (myOrders sheet) I have the checkbox. Behind this checkbox I write this string above but I got an error "Error wiht parsing of this formula."

    What I want to do, is to update the data which are linked from the main sheet (symbols) to the (myOrders) sheet. Hope you can help me with this. Many thanks.

    Reply

    • I think I need to see your sheet to follow what you're trying to do, but in general I'd start simple and add complexity from there. It sounds like the basic request still isn't working, so I'd start with a simple request like =IMPORTAPI("Binance"), and then add cell references, checkboxes, and second sheets one by one so you can see which step is producing the error. If you get an error as soon as you add the cell reference it could be related to Google Sheets localization, in which case your function needs to use semi colons instead of commas, like =IMPORTAPI("Binance";"";$A$1)

      Reply

  19. Now I have managed to get it to start "Loading" when I activate or deactivate the checkbox. I have installed the call on the myOrders worksheet in M26 and the checkbox is in L26.

    But it ends with the error #REF! The description is:
    Error
    Array result was not expanded because this would overwrite data in N26.

    I have two spreadsheets.
    - myOrders
    - Binance

    In myOrders you only see the prices linked to Binance that interest me.
    In Binance you see all the quotes that the API imports.

    Example - continuous:
    A B
    1 symbol price
    2 ETHBTC 7.649.300
    3 ETHBTC 7.649.300
    4 LTCBTC 315.100
    ...

    I think I would have to call the call in the Binance spreadsheet and then link the checkbox, right? How does that work?

    If I put the function on the Binance spreadsheet on C1 and run it, it doesn't write the values in A and B, but in C and D. Afterwards, however, the formula is overwritten and C says symbol and D says price.

    Reply

    • If you get an “Array result was not expanded because this would overwrite data in N26” it means you have a value in cell N26 that you need to delete, so make sure your checkbox is out of the way. It sounds like you’re overwriting your functions somehow but I can’t really say anything without seeing your sheet. If you’d like me to take a look then please feel free to message me via support.

      Reply

  20. Perhaps to put it in a nutshell again. When I press the "Refresh all now" button in the API Connetor menu, it refreshes everything. So I'm just looking for a way to not always have to go into this menu, but to be able to trigger this from the sheet via a button or checkbox.

    Reply

    • Yep, you can definitely do this using the method shown above. I think you just need to move your references around so you're not overwriting data when you run your requests.

      Reply

  21. Hello.

    The following error occurs:
    IMPORTAPI Error: Request 'requestName' doesn't exist!

    I ran this Request in the sidebar Manage Api Requests, and the result is positive.

    Reply

    • Is your request actually named requestName? The name you use in the IMPORTAPI function needs to match the name you've saved the request as.

      Reply

  22. Yes. My request in the sidebar is saved under the name 'order':
    =IMPORTAPI("order";"URL/api/v1/supplier/orders?dateFrom={{sheet!X1}}&flag=0&key=ZWI1YTg3YWYtZmRjMi00NTMwLWI5ZWQtMTg2NTM1MzgwZTk0";$X$1)

    Reply

  23. Hey Ana,
    =IMPORTAPI("CarData2","https://xxxxxxx/api/3/action/datastore_search?resource_id=142afde2-6228-49f9-8a29-9b6c3a0cbe40&q="&A1)

    How can I add another cell Example:
    =IMPORTAPI("CarData2","https://data.gov.il/api/3/action/datastore_search?resource_id=142afde2-6228-49f9-8a29-9b6c3a0cbe40&q="&A1+&B1)

    I need it to look for data by 2 parameters.
    Thanks!

    Reply

    • Hey Daniel, it's no different from concatenating data in Sheets anywhere else. When you join data, you use the & symbol, like this: =importapi("CarData2","https://data.gov.il/api/3/action/datastore_search?resource_id=142afde2-6228-49f9-8a29-9b6c3a0cbe40&q="&A1&B1)

      Reply

      • Hey Ana, thanks for the reply!
        Iv'e tried to narrow the results that I'm getting by using 2 parameters. Unfortunately, I can't make it to work.
        The only thing that this code does, only adding one cell to another in the search, but not looking for them separately.

      • It sounds like your URL is incorrect. Instead of referencing multiple cells in your URL, can you start off simply with a full working URL (no references)? Once that works as expected, you can figure out the cell reference piece. If you can share your full working URL I can help you recreate it with cell references.

  24. Thanks again Ana!
    The full ULR API - https://data.gov.il/api/3/action/datastore_search?resource_id=142afde2-6228-49f9-8a29-9b6c3a0cbe40&q=jones

    I'm trying to narrow it, by the 2 parameters there:
    1. result.records.degem_nm
    2. result.records.shnat_yitzur

    I'm trying to do "filter" by these 2 cells. (A1,B1).

    Thank you very much for your help! Really appreciate it.

    Reply

    • IMPORTAPI doesn't contain any filtering function, it's just a function to run API requests from your sheet, so that's why adding these parameters to the end of the URL has no effect. To filter data, you would need to check the API documentation to see how this API accepts filters (if it does), and then add them to the URL in their required format. Alternatively, you could use our JMESPath filtering syntax. In that case you'd paste the following into the JMESPath expression field: result.fields[?id=='degem_nm'||id=='shnat_yitzur'] Please check if that gets you what you're looking for.

      Reply

  25. Hi Ana, I receive this error message when trying to use the ImportAPI function. "ERROR: You do not have permission to call insertSheetERROR: Destination sheet doesn't exist", it works normally when using the sidebar but doesn't work when using the function, any help is greatly appreciated.

    Reply

    • Hmm, it sounds like it's looking for a sheet that doesn't exist. This sounds like a bug on our side, but for now can you make sure the destination sheet saved in your request exists (even if it's empty)? Let me know if that resolves it.

      Reply

  26. It's been a few weeks, no matter what I do the formula doesn't update for days. Even trying to use NOW or other ways... Anything changed recently causing it?

    Reply

    • Nothing has changed recently but custom functions generally don't update unless there's a change to one of their inputs, and can't be triggered by NOW (info).
      What happens when you use a checkbox to trigger the function, do you get an error?

      Reply

  27. I want to perform math operations on the amount resulting from =IMPORTAPI("Binance" ; "https://api.binance.com/api/v3/ticker/price?symbol=BTCUSDT" ; $A$1), but it says that the data format is text and cannot be changed to a number. How to do it? Tell me please

    Reply

    • The Parameter 1 argument in the MULTIPLY function only supports numeric values. The value type "23748.32000000" is text, so it cannot be cast to a "number" type.

      Reply

      • Just to clarify, you mean the price is coming back as text so you can't perform a calculation on it? I just tested and in my sheet it's automatically recognized as a number, so I'm not sure why it's not in your sheet, but in any case you should be able to convert text to numbers (and vice versa) using Sheets formulas. Usually the =VALUE(A1) function works, or you can check this article for some other ideas.

  28. Following up on Pavlo's text to number issue. My importapi custom requests are also coming in as text. But when I use sidebar, it comes in as number. Any idea what could be wrong? I could use the value function, but I'd need to duplicate all my results to a new sheet and I was trying to keep this more clean. Any help appreciated. Thank you!

    Reply

    • We print the data as it comes back from the API, but Sheets applies its own best guess of what the format is and uses that. I'm not sure why it's different when you use IMPORTAPI vs the sidebar, perhaps it's related to whatever data was in those sheets previously. Can you try pre-formatting the cells as number cells? Or if the output is numbers only, you could try wrapping the function around your IMPORTAPI function, e.g. =VALUE(IMPORTAPI("Request").

      Reply

  29. Hello,

    We are trying to make this work with a URL that takes dynamic values from 3 cells and an authorization token which we hard coded in the sidebar under the Headers section but, no matter what we do, the API wouldn't pull any data, and return the following error: "Request processed, no records found".

    Any idea on what might be causing this?

    Reply

    • I can’t really say anything specific without seeing the request, could you please contact support with the details of your request setup?
      In general, though, I’d say to keep it simple and then add complexity, that way you’ll be better able to determine which step produces the issue. For example, first run your request in the sidebar, then add cell references, then switch to IMPORTAPI, etc.

      Reply

      • IMPORTAPI requests run exactly the same as sidebar requests so I suspect it's related to how your URL takes dynamic values. Can you please share your IMPORTAPI function?

  30. hello, great article
    I set this formula : =IMPORTAPI("domainrating") to show domain rating (from ahrefs) and it works.

    in "target" I set just one website:chescelta.it

    Is it possibile to authomatically show domain rating for every website listed in a list of cells (not only chescelta.it)

    thanks

    Reply

  31. I am unable to get ImportAPI to work. I tried the following command: =importapi("KuCoin","https://api-futures.kucoin.com/api/v1/position?symbol=XBTUSDM") and I get the error message Request Kucoin doesn't exist. When I run this from the side panel it works just fine.

    Reply

    • IMPORTAPI is case sensitive so please make sure the request name is exactly the same as what you've saved in the sidebar. Let me know if that resolves the issue, if not please contact support so I can take a look there.

      Reply

  32. I'm having a issue with the IMPORTAPI, a lot of values are returning as "#REF" saying that it would replace the cell below, but the request returns a single cell, and i have both erased the header in the Edit Fields and selected append in the request config.

    Reply

    • Hey Eduardo, can you please run one of the requests causing the #REF error in the sidebar and check what the API returns for that specific request? To see what the API returns click Edit fields > Show raw response (info). I'm wondering if the API is returning more than 1 line for certain request URLs, causing it to "bump into" the next line in the sheet.

      Reply

  33. Hi - thanks for the helpful writeup here. One question: is it the case that APPEND mode works differently when called by the API vs the RUN button on the sidebar? When using the RUN button, each new data pull is added to the next free row, which preserves the previous requests (this is what I want). When using the API, new data overwrites the old row, even though APPEND mode is still selected. Is there any way to use the API and also have new data written to a new row?
    Thank you.

    Reply

    • Ah, I see what you're saying. Yes, it does work differently in that sense. IMPORTAPI only works on the cell it's being run from, it can't take into account the location of data in other cells. That means, if you want to preserve existing data, you would need to run new requests in subsequent rows rather than re-using the same request at the top.

      Reply

  34. This is a fantastic function, thanks. Exactly what I needed. API Connector is way better than the other API add-ins that I have tried.

    Reply

Leave a Comment

Jump To