HowTo auto update crypto prices into google sheet’s 💁

If you keep your crypto balances in a google sheet, then here are the steps to pull in the crypto prices automatically and for free.

Source: unsplashed

Step 1: Get your API key

Your first step is to get an API key from coinbase. Don’t worry, its free. 😀

1. Head over to CoinMarketCap and sign up for the free plan.
2. Now go to the settings page and create your API key. You can click on copy key and keep it for later.

Step 2: Create sheet

Create a sheet and call it CoinMarketCap, now in extensions, go to Apps Script.

Step 3: Code

You can now copy and paste below code and be sure to enter your API key and change the coins to suite your needs.

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Crypto Menu')
.addItem('Refresh coinpanel','callCoinBase')
.addToUi();
}
function callCoinBase() {
var options = {
"async": true,
"crossDomain": true,
"method" : "GET",
"headers" : {
"X-CMC_PRO_API_KEY" : "{your API Key}",
"Accept": "application/json"
}
};
// Call the Numbers API for random math fact
var response = UrlFetchApp.fetch("https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BOBA,BTC,ETH,FTM,VET,THETA,ZIL,COTI,AR,SOL,MATIC,GRT,DOT,1INCH,ADA,LUNA,AVAX,TLM,RNDR,MANA,SRM,RIN,SBR,FTM,MOVR,LINK,AKT,ALGO,SUPER,RUNE,RMRK,CLV,SCLP,SOLR,UST,AIOZ,SFUND,INJ,TOMB,HERO,DON,XRP,BNB,RACEFI,TIME,BTC,STATIC,QRDO,GARI,WMEMO,TOMB,TSHARE,LOOP,PTP,KUJI,PRISM,TIME,BGS",options);
// Logger.log(response.getContentText());
var data = JSON.parse(response.getContentText()).data;
// Logger.log(data.SBR.quote.USD.price)
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheets = spreadsheet.getSheets();
for (sheetNum in sheets) {
if (spreadsheet.getSheets()[sheetNum].getSheetName() == "CoinMarketCap"){
spreadsheet.setActiveSheet(spreadsheet.getSheets()[sheetNum])
var sheet = SpreadsheetApp.getActiveSheet();
var x = 1;
for (coin in data) {
Logger.log(coin + data[coin].quote.USD.price)
sheet.getRange(x,1).setValue(coin);
sheet.getRange(x,2).setValue(data[coin].quote.USD.price);
x++;
}
}
}
}

The above code will also add a handy menu you can click on to update the prices.

ps. You will get some security warnings, but since you are writing the code, you can accept them

Conclusion

There we go, nice and easy free solution to keep your prices updated automatically with the click of a button.