How to get the current price of a cryptocurrency on google sheets from coinmarketcap

Question

On a google sheets cell I want to get only the current cryptocurrency price with this formula:

=ValueCrypto(A1)

I tried this function for coinmarketcap:

function ValueCrypto(crypto) { var url = "https://api.coinmarketcap.com/v1/ticker/" + crypto + "/?convert=EUR"; var response = UrlFetchApp.fetch(url); var data = JSON.parse(response.getContentText()); return data[0].price_eur; }

the function gives me the error "We no longer serving this endpoint here"

I also tried to change the endpoint and I added my API key into the function:

function ValueCrypto(crypto) { var url = "pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=" + crypto var requestOptions = { method: 'GET', uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest', qs: { start: 1, limit: 5000, convert: 'EUR' }, headers: { 'X-CMC_PRO_API_KEY': 'myapikey' }, json: true, gzip: true }; var response = UrlFetchApp.fetch(url); var data = JSON.parse(response.getContentText()); return data[0].price_eur; }

and the errors now are Exception: Request failed for http://pro-api.coinmarketcap.com returned code 401. Truncated server response:

{ "status": { "timestamp": "2021-01-02T11:31:39.880Z", "error_code": 1002, "error_message": "API key missing.", ... 

Answer 1

Your code does not appear to use requestOptions which is where the API key is defined. Try passing it to UrlFetchApp like this:

var response = UrlFetchApp.fetch(url, requestOptions);