importJSON returns error code 429 (rate limited) from the server while I'm not

user_330271 0
Viewed 3409

I am using an unmodified version of the ImportJSON library by Brad Jasper and Trevor Lohrbeer (https://github.com/bradjasper/ImportJSON)

With this library, I am trying to import cryptocurrency prices into a Google Sheet, as follows:

importJSON("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin",
"0.current_price")

For your reference, this is the JSON response at the endpoint given:

[
    {
        "id":"bitcoin",
        "symbol":"btc",
        "name":"Bitcoin",
        "image":"https://assets.coingecko.com/coins/images/1/large/bitcoin.png?1547033579",
        "current_price":49101,
        "market_cap":917650396614,
        "market_cap_rank":1,
        "fully_diluted_valuation":1029890733176,
        "total_volume":64288903539,
        "high_24h":49826,
        "low_24h":46682,
        "price_change_24h":289.93,
        "price_change_percentage_24h":0.59398,
        "market_cap_change_24h":2244251078,
        "market_cap_change_percentage_24h":0.24516,
        "circulating_supply":18711362.0,
        "total_supply":21000000.0,
        "max_supply":21000000.0,
        "ath":64805,
        "ath_change_percentage":-24.58992,
        "ath_date":"2021-04-14T11:54:46.763Z",
        "atl":67.81,
        "atl_change_percentage":71969.04088,
        "atl_date":"2013-07-06T00:00:00.000Z",
        "roi":null,
        "last_updated":"2021-05-16T07:57:37.155Z"
    }
]

I am expecting a return of 49101, but instead get an error:

Exception: Request failed for https://api.coingecko.com returned code 429. 
Truncated server response: error code: 1015 (use muteHttpExceptions option to 
examine full response (line 220).

I did some Googling and error 429 seems to mean that you have queried the API too many times from your IP. From the Coingecko website:

4.2 Rate limit for the CoinGecko API is 10 calls each second per Internet Protocol 
(“IP”) address, although such rate limit may be varied by CoinGecko at any time 
in its sole discretion without notice or reference to you or any Users

But this does not apply to me. First, I only did about 50 API calls (trying to get this to work) in the past hour and second, if I go to the endpoint in my browser, I'm getting this JSON response there without issue, telling me that I'm really not rate limited.

I would like to debug further, but do not understand how I can "use muteHttpExceptions option to examine full response". What would my importJSON call then look like? I tried below, but it changed nothing to the returned error:

=importJSON("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin","0.current_price", "muteHttpExceptions=true")

Does anyone know what I'm doing wrong to invoke muteHttpExceptions or why at all I get this this error returned? Could someone please run the same formula, and see if they get the same error?

google-sheets json javascript
2 Answers

Simplier

=currentPrice("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin")

with this two-line-script

function currentPrice(url) {
  var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  return data[0].current_price
}

after you have recovered the right to fetch data within coingecko. “Simplicity is the ultimate sophistication.” (Steve Jobs)

user_150193800

First of all: I have repeatedly called your URL from the browser and after around 30 refreshes I get the 429 too. So your script might have done to many requests in a time.

Second: For me it makes no sense to fetch the JSON directly with simpleJSON. Taking a quick look I cannot find any error handling in the documentation. Also I am not a friend of synchron Request.

So I would suggest fetching the data with a standard XMLHttpRequest and parse the result if the response code is 200

function httpGetAsync(theUrl, callback)
{
    var xmlHttp = new XMLHttpRequest();
    xmlHttp.onreadystatechange = function() { 
        if (xmlHttp.readyState == 4 && xmlHttp.status == 200)
        {
            let response = JSON.parse(xmlHttp.responseText);
            callback(response);
        }
        else 
            handleError();
    }
    xmlHttp.open("GET", theUrl, true); // true for asynchronous 
    xmlHttp.send(null);
}
user_6346980
Related