-
-
Save craigtockman/d74c3b6cf075ae85776575c451634b5d to your computer and use it in GitHub Desktop.
| 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! 
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" )
Result ( bold text is added manually ):
