CoinMarketCap API Google Sheets

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

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

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

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