Hi, I have been using the following simple .gs to pull CoinMarketCap prices but it is broken now with their migration to their new API. I believe the issue lies with "function getCoins()" but I'm not sure how to proceed given CoinMarketCap's guide here: https://coinmarketcap.com/api/documentation/v1/#section/Quick-Start-Guide
var queryString = Math.random();
var ss = SpreadsheetApp.getActiveSpreadsheet(); var ssRates = ss.getSheetByName('Rates'); if (ssRates === null) { ssRates = ss.insertSheet('Rates'); }
var targetCurrency = 'usd'
// Grabs all CoinMarketCap data if (typeof targetCurrency == 'undefined' || targetCurrency == '') {targetCurrency = 'usd'}; var coins = getCoins();
function getCryptoData() {
// Use the value in the 'id' field here: https://api.coinmarketcap.com/v1/ticker/?limit=0 // If you're getting errors, you may be using the wrong 'id'
var myCoins = [ '0x', 'adelphoi', 'adx-net', 'aeternity', 'aion', 'aigang', 'airswap', 'appcoins', 'aeron', 'aragon', 'ardor', 'ark', 'aurora-dao', 'banyan-network', 'bitclave', 'bancor', 'basic-attention-token', 'binance-coin', 'bitcoin', 'bitcoin-cash', 'bitcoin-god', 'bitcoin-gold', 'bitcoin-interest', 'bitcoin-token', 'bitcoin-private', 'bitcoinx', 'bitcore', 'bitdegree', 'bitsend', 'bitshares', 'block-array', 'blockmason', 'bytecoin-bcn', 'c20', 'canyacoin', 'cardano', 'chatcoin', 'cheesecoin', 'cindicator', 'civic', 'cofound-it', 'counterparty', 'coss', 'cpchain', 'cybermiles', 'dash', 'datum', 'decred', 'digibyte', 'digixdao', 'district0x', 'dogecoin', 'dragonchain', 'edgeless', 'enjin-coin', 'eboostcoin', 'enjin-coin', 'eos', 'eosdac', 'ethereum-classic', 'ethereum', 'ethlend', 'everex', 'factom', 'filecoin', 'funfair', 'gas', 'gnosis-gno', 'golem-network-tokens', 'groestlcoin', 'guppy', 'havven', 'huobi-token', 'icon', 'iconomi', 'ignis', 'invictus-hyperion-fund', 'iostoken', 'iot-chain', 'iota', 'internet-of-people', 'ixledger', 'kucoin-shares', 'kyber-network', 'legolas-exchange', 'library-credit', 'litecoin', 'lightning-bitcoin', 'maker', 'medical-chain', 'mercury', 'metronome', 'monero', 'nano', 'neo', 'newton-coin-project', 'nexus', 'oax', 'omisego', 'omni', 'odyssey', 'origintrail', 'patientory', 'pivx', 'phore', 'polymath-network', 'power-ledger', 'qash', 'qtum', 'quantstamp', 'raiden-network-token', 'ravencoin', 'rchain', 'reddcoin', 'republic-protocol', 'rialto', 'ripio-credit-network', 'ripple', 'rise', 'rlc', 'salt', 'semux', 'siacoin', 'singulardtv', 'snovio', 'solaris', 'spreadcoin', 'steem', 'stellar', 'storj', 'stratis', 'streamr-datacoin', 'suncontract', 'syscoin', 'telcoin', 'tenx', 'tezos', 'theta-token', 'time-new-bank', 'tron', 'turtlecoin', 'ubiq', 'ultranote-coin', 'vechain', 'verge', 'veriumreserve', 'vertcoin', 'viacoin', 'vibe', 'viberate', 'vinchain', 'wabi', 'waves', 'wax', 'worldcore', 'zcash', 'zclassic', 'zcoin', 'zencash', ]
ssRates.getRange('A1').setValue("ID"); ssRates.getRange('B1').setValue("Symbol"); ssRates.getRange('C1').setValue("Price USD"); ssRates.getRange('D1').setValue("Price BTC");
var myCoinsObj = {}; var myCoinsCount = myCoins.length; for (var i = 0; i < myCoinsCount; i++) { var c = i+2; var n = 0; while (coins[n]['id'] !== myCoins[i]) { n++; }
myCoinsObj[coins[n]['id']] = coins[n]; ssRates.getRange('A'+(c).toString()).setValue(myCoinsObj[myCoins[i]]['id']); ssRates.getRange('B'+(c).toString()).setValue(myCoinsObj[myCoins[i]]['symbol']); ssRates.getRange('C'+(c).toString()).setValue(myCoinsObj[myCoins[i]]['price_usd']); ssRates.getRange('D'+(c).toString()).setValue(myCoinsObj[myCoins[i]]['price_btc']);
}
// ================================= // // WALLET BALANCE CONFIGURATION // // =================================
// ===== Wallet Sheet Creator ======================================== // Uncomment the lines of code below // It will create the Wallets sheet for you // If using the Wallets sheet ALWAYS leave it uncommented // ===================================================================
//var ssWallets = activeSpreadsheet.getSheetByName('Wallets'); //if (ssWallets === null) {ssWallets = activeSpreadsheet.insertSheet('Wallets');}
// ===== BCH Wallet Balances ========================================= // Uncomment the lines of code below // Set the variable by pasting your Address inside of the ("") // Change getRange('A1') and getRange('B1') to match the row you want // ===================================================================
//var bchWallet = getBchBalance("Your BCH Address"); //ssWallets.getRange('A1').setValue("BCH Wallet"); //ssWallets.getRange('B1').setValue(bchWallet);
// ===== BTC Wallet Balances ========================================= // Uncomment the lines of code below // Set the variable by pasting your Address inside of the ("") // Change getRange('A2') and getRange('B2') to match the row you want // ===================================================================
//var btcWallet = getBtcBalance("Your BTC Address"); //ssWallets.getRange('A2').setValue("BTC Wallet"); //ssWallets.getRange('B2').setValue(btcWallet);
// ===== Ethereum Wallet Balances ==================================== // Create an account on Etherscan.io // Create an API key at https://etherscan.io/myapikey // Uncomment the lines of code below // Set the API key variable by pasting your API key inside of the ("") // Set the address variable by pasting your Address inside of the ("") // Change getRange('A3') and getRange('B3') to match the row you want // ===================================================================
//var ethApiKey = "Your Etherscan API Key"; //var ethWallet = getEthBalance(ethApiKey,"Your ETH Address"); //ssWallets.getRange('A3').setValue("ETH Wallet"); //ssWallets.getRange('B3').setValue(ethWallet);
// ===== DGB wallet balances ========================================= // Uncomment the lines of code below // Set the variable by pasting your Address inside of the ("") // Change getRange('A4') and getRange('B4') to match the row you want // ===================================================================
//var dgbWallet = getDgbBalance("Your DGB Address"); //ssWallets.getRange('A4').setValue("DGB Wallet"); //ssWallets.getRange('B4').setValue(dgbWallet);
// ===== LTC wallet balances ========================================= // Uncomment the lines of code below // Set the variable by pasting your Address inside of the ("") // Change getRange('A5') and getRange('B5') to match the row you want // ===================================================================
//var ltcWallet = getLtcBalance("Your LTC Address"); //ssWallets.getRange('A5').setValue("LTC Wallet"); //ssWallets.getRange('B5').setValue(ltcWallet);
// ===== VTC wallet balances ========================================= // Uncomment the lines of code below // Set the variable by pasting your Address inside of the ("") // Change getRange('A5') and getRange('B5') to match the row you want // ===================================================================
//var vtcWallet = getVtcBalance("Your VTC Address"); //ssWallets.getRange('A6').setValue("VTC Wallet"); //ssWallets.getRange('B6').setValue(vtcWallet); }
function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menubuttons = [ {name: "clearRates", functionName: "clearRates"},{name: "getCryptoData", functionName: "getCryptoData"}]; ss.addMenu("crypto", menubuttons); }
function clearRates() { var sheet = SpreadsheetApp.getActive().getSheetByName('Rates'); sheet.getRange('A1:D1000').clearContent(); }
function getCoins() {
var url = 'https://api.coinmarketcap.com/v1/ticker/?limit=0&convert='+targetCurrency; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var json = response.getContentText(); var data = JSON.parse(json);
return data; }
function getBchBalance(bchAddress) {
var url = 'https://bitcoincash.blockexplorer.com/api/addr/'+bchAddress+'/balance'; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var balance = response.getContentText(); //Pause to not trigger API limit for multiple wallets Utilities.sleep(300);
return balance * Math.pow(10,-8); }
function getBtcBalance(btcAddress) {
var url = 'https://blockexplorer.com/api/addr/'+btcAddress+'/balance'; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var balance = response.getContentText(); //Pause to not trigger API limit for multiple wallets Utilities.sleep(300);
return balance * Math.pow(10,-8); }
function getEthBalance(ethApiKey,ethAddress) {
var url = 'https://api.etherscan.io/api?module=account&action=balance&address='+ethAddress+'&tag=latest&apikey='+ethApiKey; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var json = response.getContentText(); var obj = JSON.parse(json); var balance = obj.result; //Pause to not trigger API limit for multiple wallets Utilities.sleep(300);
return balance * Math.pow(10,-18); }
function getDgbBalance(dgbAddress) {
var url = 'https://chainz.cryptoid.info/dgb/api.dws?q=getbalance&a='+dgbAddress; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var balance = response.getContentText(); //Pause to not trigger API limit for multiple wallets Utilities.sleep(300);
return balance; }
function getLtcBalance(ltcAddress) {
var url = 'https://chainz.cryptoid.info/ltc/api.dws?q=getbalance&a='+ltcAddress; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var balance = response.getContentText(); //Pause to not trigger API limit for multiple wallets Utilities.sleep(300);
return balance; }
function getVtcBalance(vtcAddress) {
var url = 'http://explorer.vertcoin.info/ext/getbalance/'+vtcAddress; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var balance = response.getContentText(); //Pause to not trigger API limit for multiple wallets Utilities.sleep(300);
return balance; }
// USE AT YOUR OWN RISK function getRate(currencyId) {
if (typeof targetCurrency !== 'undefined') {conversionRate = 'usd'};
var url = 'https://api.coinmarketcap.com/v1/ticker/' + currencyId + '/?convert=' + targetCurrency; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var json = response.getContentText(); var data = JSON.parse(json); var obj = parseFloat(data[0]['price_' + targetCurrency]);
return obj; }
function getWebRate(currencyId) { //Example Output: // '=IMPORTXML("https://coinmarketcap.com/currencies/zeeyx?3908288283","//span[@id=\'quote_price\']")';
var coinScrape1 = '=IMPORTXML("https://coinmarketcap.com/currencies/'; var coinScrape2 = '","//span[@id=\'quote_price\']")';
return coinScrape1 + currencyId + '?' + queryString + coinScrape2; }
function getCurrencyConversion(currencyOne, currencyTwo) {
var url = 'https://api.fixer.io/latest?symbols='+currencyOne.toUpperCase()+','+currencyTwo.toUpperCase(); var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var json = response.getContentText(); var data = JSON.parse(json);
return parseFloat(data['rates'][currencyTwo]); }
