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 give me the error "We no longer serving this endpoint here"

I also tried to change the endpoint and I added my apy 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.", ... (use muteHttpExceptions option to examine full response)

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

See documentation for UriFetchApp.

Answer 2

Modification points:

• When I saw the official document of Quick Start Guide of CoinMarketCap API, as the sample curl command, I found the following sample curl command.

    curl -H "X-CMC_PRO_API_KEY: b54bcf4d-1bca-4e8e-9a24-22ff2c3d462c" -H "Accept: application/json" -d "start=1&limit=5000&convert=USD" -G https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest
  

• In this case, the data of start=1&limit=5000&convert=USD is required to be used as the query parameter.

• When I saw your script, it seems that the values of symbol, start, limit, convert are used. In this case, please use the values like symbol=${crypto}&start=1&limit=5000&convert=EUR. And, headers: {'X-CMC_PRO_API_KEY': 'myapikey'} can be used at params of fetch(url, params).

• It seems that the URL is https://###.

When above points are reflected to your script, it becomes as follows.

Modified script:

Before you use this, please set 'X-CMC_PRO_API_KEY': 'myapikey' using your API key.

function ValueCrypto(crypto) {
  // This is from https://gist.github.com/tanaikech/70503e0ea6998083fcb05c6d2a857107
  String.prototype.addQuery = function(obj) {
    return this + Object.keys(obj).reduce(function(p, e, i) {
      return p + (i == 0 ? "?" : "&") +
        (Array.isArray(obj[e]) ? obj[e].reduce(function(str, f, j) {
          return str + e + "=" + encodeURIComponent(f) + (j != obj[e].length - 1 ? "&" : "")
        },"") : e + "=" + encodeURIComponent(obj[e]));
    },"");
  }

  var url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest"; // or var url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest";
  var query = {
    symbol: crypto,
    start: 1,
    limit: 5000,
    convert: 'EUR'
  };
  var endpoint = url.addQuery(query); // <--- https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?symbol=###&start=1&limit=5000&convert=EUR

  var response = UrlFetchApp.fetch(endpoint, {headers: {'X-CMC_PRO_API_KEY': 'myapikey', 'Accept': 'application/json'}});
  return response.getContentText();
}

• In this modified script, Listings Latest is used. When you want to use Quotes Latest, please modify the URL to var url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest";.

Note:

• From the official document, I thought that data[0].price_eur might be undefined. Because data[0].price_eur is usef for api.coinmarketcap.com/v1/ticker. So in this sample script, I returned response.getContentText(). When you put =ValueCrypto(A1) to a cell, the returned value can be seen. From this value, can you show the sample result value and the output value you want? By this, I would like to modify it.

• When your API key is invalid, an error occurs. Please be careful this.

References:

• Quick Start Guide
• Listings Latest
• Quotes Latest
• UrlFetchApp.fetch(url, params)

Answer 3

These work for me

  function fetchAll() {
  const apiKey = 'xxxxxxx-xxxxxxx-xxxxxxx-xxxxxxx-xxx'
  fetchCoin({ crypto: "SAFEMOON", fiat: "CAD", firstCell: "B9", apiKey })
  fetchCoin({ crypto: "SAFEMOON", fiat: "USD", firstCell: "B8", apiKey })

}

function fetchCoin({ crypto, fiat, firstCell, apiKey }) {
      const ascii = firstCell[0].toLowerCase().charCodeAt(0)
  try {
    var options = {
      headers: { 'X-CMC_PRO_API_KEY': apiKey }
    }
    var url = `https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=${crypto}&convert=${fiat}`
    var response = UrlFetchApp.fetch(url, options);
    var res = JSON.parse(response);
    SpreadsheetApp.getActiveSpreadsheet().getRange(`${String.fromCharCode(ascii - 1)}${firstCell[1]}`).setValue(fiat)
    SpreadsheetApp.getActiveSpreadsheet().getRange(`${String.fromCharCode(ascii + 0)}${firstCell[1]}`).setValue(res.data[crypto].quote[fiat].price)
    SpreadsheetApp.getActiveSpreadsheet().getRange(`${String.fromCharCode(ascii + 1)}${firstCell[1]}`).setValue(res.data[crypto].quote[fiat].percent_change_1h)
    SpreadsheetApp.getActiveSpreadsheet().getRange(`${String.fromCharCode(ascii + 2)}${firstCell[1]}`).setValue(res.data[crypto].quote[fiat].percent_change_24h)
    SpreadsheetApp.getActiveSpreadsheet().getRange(`${String.fromCharCode(ascii + 3)}${firstCell[1]}`).setValue(res.data[crypto].quote[fiat].percent_change_7d)
    SpreadsheetApp.getActiveSpreadsheet().getRange(`${String.fromCharCode(ascii + 4)}${firstCell[1]}`).setValue(res.data[crypto].quote[fiat].percent_change_30d)
    SpreadsheetApp.getActiveSpreadsheet().getRange(`${String.fromCharCode(ascii + 5)}${firstCell[1]}`).setValue(Utilities.formatDate(new Date(res.data[crypto].quote[fiat].last_updated), 'America/New_York', 'MMMM dd, yyyy HH:mm:ss Z'))


    Logger.log({ url, ascii,res })
  } catch (e) {
        SpreadsheetApp.getActiveSpreadsheet().getRange(`${String.fromCharCode(ascii - 1)}${firstCell[1]}`).setValue("")
    SpreadsheetApp.getActiveSpreadsheet().getRange(`${String.fromCharCode(ascii + 0)}${firstCell[1]}`).setValue(`Something is broke... ${e.message}`)
    SpreadsheetApp.getActiveSpreadsheet().getRange(`${String.fromCharCode(ascii + 1)}${firstCell[1]}`).setValue("")
    SpreadsheetApp.getActiveSpreadsheet().getRange(`${String.fromCharCode(ascii + 2)}${firstCell[1]}`).setValue("")
    SpreadsheetApp.getActiveSpreadsheet().getRange(`${String.fromCharCode(ascii + 3)}${firstCell[1]}`).setValue("")
    SpreadsheetApp.getActiveSpreadsheet().getRange(`${String.fromCharCode(ascii + 4)}${firstCell[1]}`).setValue("")
    SpreadsheetApp.getActiveSpreadsheet().getRange(`${String.fromCharCode(ascii + 5)}${firstCell[1]}`).setValue("")
  }
}