CoinMarketCap API Google Sheets

gistfile1.js

function coin_price() {
  const myGoogleSheetName =
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Coins')
  const coinMarketCapAPICall = {
    method: 'GET',
    uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
    qs: {
      start: '1',
      limit: '5000',
      convert: 'USD',
    },
    headers: { 'X-CMC_PRO_API_KEY': 'YOUR_API_KEY_GOES_HERE' },
    json: true,
    gzip: true,
  }

  // Get the coins you follow from your spreadsheet
  let myCoinSymbols = []
  const getValues = SpreadsheetApp.getActiveSheet().getDataRange().getValues()
  for (let i = 0; i < getValues.length; i++) {
    // 9 = Column J in the spreadsheet. You can change this to the column where you enter your ticker symbols.
    const coinSymbol = getValues[i][9]
    if (coinSymbol) {
      myCoinSymbols.push(coinSymbol)
    }
  }

  for (let i = 0; i < myCoinSymbols.length; i++) {
    const ticker = myCoinSymbols[i]
    const coinMarketCapUrl = `https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=${ticker}`
    const result = UrlFetchApp.fetch(coinMarketCapUrl, coinMarketCapAPICall)
    const txt = result.getContentText()
    const d = JSON.parse(txt)
    const row = i + 2
    myGoogleSheetName.getRange(row, 12).setValue(d.data[ticker].quote.USD.price)
  }
}

Hey @craigtockman , thanks for this! If instead of pre-defining a set of coin symbols I wanted to retrieve the price data for an entire batch of coins (i.e. using the cryptocurrency/listings/latest endpoint) what changes should be made to the code here? I'm trying to automate things in my portfolio tracking sheet but it's the first time I'm touching Apps Script (or coding for that matter). Thanks in advance!

@craigtockman Hi guys, thanks for writing this script. I'm trying to solve a similar issue as @brunoigle I have a list of coins defined in a column, instead of hardcoding it like we did at line 21, what function in the API will retrieve a list of coins defined under a column? I've looked in the documentation but it's extremely vast. Thanks!

Hey guys, amazing code!

I made a change to get the individual price. Enjoy!

function DaveFinance(ticker) {
//var ticker = "BTC"
var ticker = ticker.toString()

// Call CoinMarketCap and let them know who you are.
const coinMarketCapAPICall = {
method: 'GET',
uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
qs: {
start: '1',
limit: '5000',
convert: 'USD',
},
headers: { 'X-CMC_PRO_API_KEY': 'YOUR_API_KEY' },
json: true,
gzip: true,
}

const coinMarketCapUrl = https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=${ticker}
const result = UrlFetchApp.fetch(coinMarketCapUrl, coinMarketCapAPICall)
const txt = result.getContentText()
const d = JSON.parse(txt)
return d.data[ticker].quote.USD.price
}

@brunoigle @akiraah @stiga66 what's up neighbors. I updated the code so you can enter symbols in Column J in your spreadsheet instead of hard coding them into the script. Thanks for the feedback.

@craigtockman, thank you for sharing this, much appreciated. If you have the time, I am stuck somewhere... I would need to identify the tokens by ID instead of ticker symbol, as there are conflicting tickers (see the CMC API docs Best Practices section / Use CoinMarketCap ID Instead of Cryptocurrency Symbol). Any chance you could share the code updated for calling IDs? Thanks a lot for your time and efforts.

I've made a change to fetch all coins in one API call to not reach the limit for the free version.

`function coin_price() {
const myGoogleSheetName =
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Coins')
const coinMarketCapAPICall = {
method: 'GET',
uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
qs: {
start: '1',
limit: '5000',
convert: 'USD,EUR',
},
headers: { 'X-CMC_PRO_API_KEY': 'YOUR_API_KEY_GOES_HERE' },
json: true,
gzip: true,
}

// Get the coins you follow from your spreadsheet
let myCoinSymbols = []
let myCoins = ""
const getValues = myGoogleSheetName.getDataRange().getValues()
for (let i = 0; i < getValues.length; i++) {
// 9 = Column J in the spreadsheet. You can change this to the column where you enter your ticker symbols.
const coinSymbol = getValues[i][0]
if (coinSymbol) {
if (myCoins != "") {
myCoins += ","
}
myCoins += coinSymbol
myCoinSymbols.push(coinSymbol)
}
}

const coinMarketCapUrl = https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=${myCoins}
const result = UrlFetchApp.fetch(coinMarketCapUrl, coinMarketCapAPICall)
const txt = result.getContentText()
const d = JSON.parse(txt)

for (let i = 0; i < myCoinSymbols.length; i++) {
const ticker = myCoinSymbols[i]
const row = i + 2
myGoogleSheetName.getRange(row, 2).setValue(d.data[ticker].quote.EUR.price)
myGoogleSheetName.getRange(row, 4).setValue(d.data[ticker].last_updated)
}
}`

Thanks @codeskraps for a great trick with only one API call.
Here's a little modification by which you can get IDs, Names, CoinMarketCap Rank, Price, 24h Volume, Percentage Change, Market Cap and so on.
( add symbols you want to display in column A in sheet "Coins" )

function coinPrice() {
  const myGoogleSheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Coins")
  const coinMarketCapAPICall = {
    method: 'GET',
    uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?aux=cmc_rank',
    qs: {
      start: '1',
      limit: '5000',
      convert: 'USD',
    },
    headers: { 'X-CMC_PRO_API_KEY': 'YOUR_API_KEY_GOES_HERE' },
    json: true,
    gzip: true,
  }

  // Get the coins you follow from your spreadsheet
  let myCoinSymbols = []
  let myCoins = ""
  const getValues = myGoogleSheetName.getDataRange().getValues()
  for (let i = 0; i < getValues.length; i++) {
    // 0 = Column A in the spreadsheet. You can change this to the column where you enter your ticker symbols.
    const coinSymbol = getValues[i][0]
    if (coinSymbol) {
      if (myCoins != "") {
        myCoins += ","
      }
      myCoins += coinSymbol
      myCoinSymbols.push(coinSymbol)
    }
  }

  const coinMarketCapUrl = `https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=${myCoins}`
  const result = UrlFetchApp.fetch(coinMarketCapUrl, coinMarketCapAPICall)
  const txt = result.getContentText()
  const d = JSON.parse(txt)
  for (let i = 0; i < myCoinSymbols.length; i++) {
    const ticker = myCoinSymbols[i]
    const row = i + 2
    myGoogleSheetName.getRange(row, 2).setValue(d.data[ticker].id)
    myGoogleSheetName.getRange(row, 3).setValue(d.data[ticker].name)
    myGoogleSheetName.getRange(row, 4).setValue(d.data[ticker].slug)
    myGoogleSheetName.getRange(row, 5).setValue(d.data[ticker].date_added)
    myGoogleSheetName.getRange(row, 6).setValue(d.data[ticker].is_active)
    myGoogleSheetName.getRange(row, 7).setValue(d.data[ticker].is_fiat)
    myGoogleSheetName.getRange(row, 8).setValue(d.data[ticker].num_market_pairs)
    myGoogleSheetName.getRange(row, 9).setValue(d.data[ticker].circulating_supply)
    myGoogleSheetName.getRange(row, 10).setValue(d.data[ticker].total_supply)
    myGoogleSheetName.getRange(row, 11).setValue(d.data[ticker].max_supply)
    myGoogleSheetName.getRange(row, 12).setValue(d.data[ticker].cmc_rank)
    myGoogleSheetName.getRange(row, 13).setValue(d.data[ticker].quote.USD.price)
    myGoogleSheetName.getRange(row, 14).setValue(d.data[ticker].quote.USD.volume_24h)
    myGoogleSheetName.getRange(row, 15).setValue(d.data[ticker].quote.USD.percent_change_1h)
    myGoogleSheetName.getRange(row, 16).setValue(d.data[ticker].quote.USD.percent_change_24h)
    myGoogleSheetName.getRange(row, 17).setValue(d.data[ticker].quote.USD.percent_change_7d)
    myGoogleSheetName.getRange(row, 18).setValue(d.data[ticker].quote.USD.percent_change_30d)
    myGoogleSheetName.getRange(row, 19).setValue(d.data[ticker].quote.USD.percent_change_60d)
    myGoogleSheetName.getRange(row, 20).setValue(d.data[ticker].quote.USD.percent_change_90d)
    myGoogleSheetName.getRange(row, 21).setValue(d.data[ticker].quote.USD.market_cap)
    myGoogleSheetName.getRange(row, 22).setValue(d.data[ticker].last_updated)
  }
}

Result ( bold text is added manually ):

Is there anyway I fetch not the latest price? Or is there a way to fetch the price in euros?