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

I found that the accepted answer isn't properly formated as per the latest API docs. Heres my solution which seems to work well for me (Be sure to place the API key and quote currency):

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

Answer 2

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 3

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 4

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("")
  }
}

Answer 5

You can skip the API and use this formula in the cell where you want the price:

=IFERROR(INDEX(IMPORTXML(CONCATENATE("https://coinmarketcap.com/currencies/",CELL("contents",A2),"?update="),"//div[contains(@class, 'priceTitle')]"), 1, 1), INDEX(IMPORTXML(CONCATENATE("https://www.coingecko.com/en/coins/",CELL("contents",A2),"?update="),"//span[@data-coin-symbol]//text()"), 1, 1))

Cell A2 would contain 'bitcoin' or some other crypto slug. The formula uses coinmarketcap as it's primary source, and coingecko as a backup source. If you want it to auto-refresh, you can look here: https://stackoverflow.com/a/44170267/10642485