Skip to content

Instantly share code, notes, and snippets.

@craigtockman

craigtockman/gist:d74c3b6cf075ae85776575c451634b5d

Last active July 4, 2024 20:54
Show Gist options
  • Save craigtockman/d74c3b6cf075ae85776575c451634b5d to your computer and use it in GitHub Desktop.
Save craigtockman/d74c3b6cf075ae85776575c451634b5d to your computer and use it in GitHub Desktop.
Download ZIP
CoinMarketCap API Google Sheets
Raw
gistfile1.js
function coinPrice() {
const myGoogleSheetName =
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Coins2");
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,
};
// Set columnHeaders. CoinMarketCap data value / Google sheet column header name.
const columnHeaders = [
{ cmcValue: "id", headerName: "ID" },
{ cmcValue: "name", headerName: "Name" },
{ cmcValue: "slug", headerName: "Slug" },
{ cmcValue: "date_added", headerName: "Date Added" },
{ cmcValue: "is_active", headerName: "Is Active" },
{ cmcValue: "is_fiat", headerName: "Is Fiat" },
{ cmcValue: "num_market_pairs", headerName: "Num Market Pairs" },
{ cmcValue: "circulating_supply", headerName: "Circulating Supply" },
{ cmcValue: "total_supply", headerName: "Total Supply" },
{ cmcValue: "max_supply", headerName: "Max Supply" },
{ cmcValue: "cmc_rank", headerName: "CMC Rank" },
{ cmcValue: "price", currency: "USD", headerName: "Price" },
{
cmcValue: "volume_24h",
currency: "USD",
headerName: "Volume (24h)",
},
{
cmcValue: "percent_change_1h",
currency: "USD",
headerName: "Percent Change (1h)",
},
{
cmcValue: "percent_change_24h",
currency: "USD",
headerName: "Percent Change (24h)",
},
{
cmcValue: "percent_change_7d",
currency: "USD",
headerName: "Percent Change (7d)",
},
{
cmcValue: "percent_change_30d",
currency: "USD",
headerName: "Percent Change (30d)",
},
{
cmcValue: "percent_change_60d",
currency: "USD",
headerName: "Percent Change (60d)",
},
{
cmcValue: "percent_change_90d",
currency: "USD",
headerName: "Percent Change (90d)",
},
{
cmcValue: "market_cap",
currency: "USD",
headerName: "Market Cap",
},
{ cmcValue: "last_updated", headerName: "Last Updated" },
];
myGoogleSheetName
.getRange(1, 2, 1, columnHeaders.length)
.setValues([columnHeaders.map((header) => header.headerName)]);
// Get the coins you follow from your spreadsheet in column A.
let myCoinSymbols = [];
let myCoins = "";
const getValues = myGoogleSheetName.getDataRange().getValues();
for (let i = 1; i < getValues.length; i++) {
// Start from row 1 to skip header row
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);
const getRowData = (row, col, coinSymbol, cmcData) => {
const getCMCData = (data) => {
if (data.currency) {
return d.data[coinSymbol].quote[data.currency][data.cmcValue];
}
return d.data[coinSymbol][data.cmcValue];
};
return myGoogleSheetName.getRange(row, col).setValue(getCMCData(cmcData));
};
for (let i = 0; i < myCoinSymbols.length; i++) {
const coinSymbol = myCoinSymbols[i];
const row = i + 2; // Start from row 2, since row 1 is for the generated columnHeaders.
for (let i = 0; i < columnHeaders.length; i++) {
const col = i + 2; // Start from column 2 since column 1 is reserved for your coin symbols. IE "BTC"
getRowData(row, col, coinSymbol, columnHeaders[i]);
}
}
}
@brunoigle
Copy link

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!

@akiraah
Copy link

akiraah commented Dec 31, 2021

@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!

@stiga66
Copy link

stiga66 commented Feb 1, 2022

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
}

@craigtockman
Copy link
Author

@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.

@sh1msh0n
Copy link

@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.

@codeskraps
Copy link

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)
}
}`

@kico47
Copy link

kico47 commented May 24, 2022

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 ):
preview

Copy link

ghost commented Jan 3, 2023

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

@AbundanceBot
Copy link

Thanks so much posting this!!

Updated the script above to populate the headers in the sheet.

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,
};

// Set column headers
const headers = [
"Symbol", "ID", "Name", "Slug", "Date Added", "Is Active", "Is Fiat",
"Num Market Pairs", "Circulating Supply", "Total Supply", "Max Supply",
"CMC Rank", "Price", "Volume (24h)", "Percent Change (1h)",
"Percent Change (24h)", "Percent Change (7d)", "Percent Change (30d)",
"Percent Change (60d)", "Percent Change (90d)", "Market Cap", "Last Updated"
];
myGoogleSheetName.getRange(1, 1, 1, headers.length).setValues([headers]);

// Get the coins you follow from your spreadsheet
let myCoinSymbols = [];
let myCoins = "";
const getValues = myGoogleSheetName.getDataRange().getValues();
for (let i = 1; i < getValues.length; i++) { // Start from 1 to skip header row
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; // Start from the second row, as the first row is headers
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);
}
}

@craigtockman
Copy link
Author

@sh1msh0n @codeskraps @kico47 @AbundanceBot Thanks for all the suggestions on CoinMarketCap API Google Sheets gist.
Code updated and optimized! :shipit:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment