Skip to content

Instantly share code, notes, and snippets.

@sargonas
Last active April 5, 2022 13:27
Show Gist options
  • Save sargonas/9dff03831cce63329efe254fc7895bec to your computer and use it in GitHub Desktop.
Save sargonas/9dff03831cce63329efe254fc7895bec to your computer and use it in GitHub Desktop.
crypto portfolio import for google sheets
/////////////////////////
//Section 1: Coin Value//
/////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////
//coin market value lookup (works for all coins, just pass a single coin symbol as a parameter)
function getCoinPriceBySymbol(symbol) {
var url = 'https://api.coinmarketcap.com/v1/ticker/';
var response = UrlFetchApp.fetch(url);
var json = JSON.parse(response.getContentText());
for (i in json) {
if (json[i].symbol == symbol)
return json[i].price_usd;
}
return "N/A";
}
////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////
//Section 2: Wallet tracking//
//////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////
//Bitcoin Balance
function btcWalletBalance(addresses) {
var url = "https://multiexplorer.com/api/address_balance/fallback?currency=btc&address=" + addresses;
var btc_response = UrlFetchApp.fetch(url);
var btc_balance = 0;
var btc_results = JSON.parse(btc_response.getContentText());
for (var i = 0; i < btc_results.result.length; i++) {
var btc_bal = btc_results.result[i].balance;
btc_balance = btc_balance + btc_bal;
}
return btc_balance;
}
////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////
//ethereum Balance
function ethWalletBalance(addresses) {
var url = "https://api.etherscan.io/api?module=account&action=balancemulti&address=" + addresses + "&tag=latest&apikey=API_KEY_GOES_HERE";
var eth_response = UrlFetchApp.fetch(url);
var eth_balance = 0;
var eth_results = JSON.parse(eth_response.getContentText());
for (var i = 0; i < eth_results.result.length; i++) {
var eth_bal = eth_results.result[i].balance;
eth_balance = eth_balance + eth_bal;
}
eth_balance = eth_balance / 1000000000000000000;
return eth_balance;
}
////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////
//Litecoin Balance
function ltcWalletBalance(addresses) {
var url = "https://chainz.cryptoid.info/ltc/api.dws?key=API_KEY_GOES_HERE&q=multiaddr&active=" + addresses;
var ltc_response = UrlFetchApp.fetch(url);
//var ltc_response = UrlFetchApp.fetch(url);
var ltc_balance = 0;
var ltc_results = JSON.parse(ltc_response.getContentText());
for (var i = 0; i < ltc_results.currency.length; i++) {
var ltc_bal = ltc_results.currency[i].balance;
ltc_balance = ltc_balance + ltc_bal;
}
return ltc_balance;
}
////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////
//eth-token balance
function tokenWalletBalance(contract, address) {
var tokenApiKey = "YOUR_ETHERSCAN_API_KEY_HERE"; //REPLACE WITH YOUR ETHERSCAN API KEY!!
var url = "https://api.etherscan.io/api?module=account&action=tokenbalance&contractaddress=" + contract + "&address=" + address + "&tag=latest&apikey=" + tokenApiKey;
var token_response = UrlFetchApp.fetch(url);
var token_results = JSON.parse(token_response.getContentText());
var token_balance = token_results.result / 1000000000000000000;
return token_balance;
}
////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////
//Decred Balance
function dcrWalletBalance(address) {
var url = "https://mainnet.decred.org/api/addr/" + address + "/balance";
var dcr_response = UrlFetchApp.fetch(url);
var dcr_results = JSON.parse(dcr_response.getContentText());
var dcr_balance = dcr_results / 100000000;
return dcr_balance;
}
////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////
//Section 3: Exchange APIs//
////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////
//coinbase balance
var hmac_hash = function(params, private_key) {
const hash = CryptoJS.HmacSHA256(params, private_key).toString();
return hash;
}
var coinbaseGetTimestamp = function() {
return coinbaseRequest("GET", "/v2/time", false)["epoch"];
}
var coinbaseRequest = function(method, requestPath, authRequired) {
if (authRequired) {
var COINBASE_PUBLIC_KEY = "YOUR_COINBASE_PUBLIC_KEY_HERE"; //REPLACE WITH YOUR API KEY!
var COINBASE_PRIVATE_KEY = "YOUR_COINBASE_PRIVATE_KEY_HERE"; //REPLACE WITH YOUR API SECRET!
const timestamp = coinbaseGetTimestamp();
const prehash = timestamp + method + requestPath;
const signature = hmac_hash(prehash, COINBASE_PRIVATE_KEY);
var options = {
headers: {
"CB-ACCESS-KEY": COINBASE_PUBLIC_KEY,
"CB-ACCESS-SIGN": signature,
"CB-ACCESS-TIMESTAMP": timestamp,
"CB-VERSION": "2016-02-05"
},
method: method
}
} else {
var options = {
method: method
};
}
const response = UrlFetchApp.fetch("https://api.coinbase.com" + requestPath, options);
return JSON.parse(response)["data"];
}
function coinbaseGetAccountBalance(accountId, nonce) {
return coinbaseRequest("GET", "/v2/accounts/" + accountId, true)["balance"]["amount"];
}
var CryptoJS=CryptoJS||function(h,s){var f={},g=f.lib={},q=function(){},m=g.Base={extend:function(a){q.prototype=this;var c=new q;a&&c.mixIn(a);c.hasOwnProperty("init")||(c.init=function(){c.$super.init.apply(this,arguments)});c.init.prototype=c;c.$super=this;return c},create:function(){var a=this.extend();a.init.apply(a,arguments);return a},init:function(){},mixIn:function(a){for(var c in a)a.hasOwnProperty(c)&&(this[c]=a[c]);a.hasOwnProperty("toString")&&(this.toString=a.toString)},clone:function(){return this.init.prototype.extend(this)}},
r=g.WordArray=m.extend({init:function(a,c){a=this.words=a||[];this.sigBytes=c!=s?c:4*a.length},toString:function(a){return(a||k).stringify(this)},concat:function(a){var c=this.words,d=a.words,b=this.sigBytes;a=a.sigBytes;this.clamp();if(b%4)for(var e=0;e<a;e++)c[b+e>>>2]|=(d[e>>>2]>>>24-8*(e%4)&255)<<24-8*((b+e)%4);else if(65535<d.length)for(e=0;e<a;e+=4)c[b+e>>>2]=d[e>>>2];else c.push.apply(c,d);this.sigBytes+=a;return this},clamp:function(){var a=this.words,c=this.sigBytes;a[c>>>2]&=4294967295<<
32-8*(c%4);a.length=h.ceil(c/4)},clone:function(){var a=m.clone.call(this);a.words=this.words.slice(0);return a},random:function(a){for(var c=[],d=0;d<a;d+=4)c.push(4294967296*h.random()|0);return new r.init(c,a)}}),l=f.enc={},k=l.Hex={stringify:function(a){var c=a.words;a=a.sigBytes;for(var d=[],b=0;b<a;b++){var e=c[b>>>2]>>>24-8*(b%4)&255;d.push((e>>>4).toString(16));d.push((e&15).toString(16))}return d.join("")},parse:function(a){for(var c=a.length,d=[],b=0;b<c;b+=2)d[b>>>3]|=parseInt(a.substr(b,
2),16)<<24-4*(b%8);return new r.init(d,c/2)}},n=l.Latin1={stringify:function(a){var c=a.words;a=a.sigBytes;for(var d=[],b=0;b<a;b++)d.push(String.fromCharCode(c[b>>>2]>>>24-8*(b%4)&255));return d.join("")},parse:function(a){for(var c=a.length,d=[],b=0;b<c;b++)d[b>>>2]|=(a.charCodeAt(b)&255)<<24-8*(b%4);return new r.init(d,c)}},j=l.Utf8={stringify:function(a){try{return decodeURIComponent(escape(n.stringify(a)))}catch(c){throw Error("Malformed UTF-8 data");}},parse:function(a){return n.parse(unescape(encodeURIComponent(a)))}},
u=g.BufferedBlockAlgorithm=m.extend({reset:function(){this._data=new r.init;this._nDataBytes=0},_append:function(a){"string"==typeof a&&(a=j.parse(a));this._data.concat(a);this._nDataBytes+=a.sigBytes},_process:function(a){var c=this._data,d=c.words,b=c.sigBytes,e=this.blockSize,f=b/(4*e),f=a?h.ceil(f):h.max((f|0)-this._minBufferSize,0);a=f*e;b=h.min(4*a,b);if(a){for(var g=0;g<a;g+=e)this._doProcessBlock(d,g);g=d.splice(0,a);c.sigBytes-=b}return new r.init(g,b)},clone:function(){var a=m.clone.call(this);
a._data=this._data.clone();return a},_minBufferSize:0});g.Hasher=u.extend({cfg:m.extend(),init:function(a){this.cfg=this.cfg.extend(a);this.reset()},reset:function(){u.reset.call(this);this._doReset()},update:function(a){this._append(a);this._process();return this},finalize:function(a){a&&this._append(a);return this._doFinalize()},blockSize:16,_createHelper:function(a){return function(c,d){return(new a.init(d)).finalize(c)}},_createHmacHelper:function(a){return function(c,d){return(new t.HMAC.init(a,
d)).finalize(c)}}});var t=f.algo={};return f}(Math);
(function(h){for(var s=CryptoJS,f=s.lib,g=f.WordArray,q=f.Hasher,f=s.algo,m=[],r=[],l=function(a){return 4294967296*(a-(a|0))|0},k=2,n=0;64>n;){var j;a:{j=k;for(var u=h.sqrt(j),t=2;t<=u;t++)if(!(j%t)){j=!1;break a}j=!0}j&&(8>n&&(m[n]=l(h.pow(k,0.5))),r[n]=l(h.pow(k,1/3)),n++);k++}var a=[],f=f.SHA256=q.extend({_doReset:function(){this._hash=new g.init(m.slice(0))},_doProcessBlock:function(c,d){for(var b=this._hash.words,e=b[0],f=b[1],g=b[2],j=b[3],h=b[4],m=b[5],n=b[6],q=b[7],p=0;64>p;p++){if(16>p)a[p]=
c[d+p]|0;else{var k=a[p-15],l=a[p-2];a[p]=((k<<25|k>>>7)^(k<<14|k>>>18)^k>>>3)+a[p-7]+((l<<15|l>>>17)^(l<<13|l>>>19)^l>>>10)+a[p-16]}k=q+((h<<26|h>>>6)^(h<<21|h>>>11)^(h<<7|h>>>25))+(h&m^~h&n)+r[p]+a[p];l=((e<<30|e>>>2)^(e<<19|e>>>13)^(e<<10|e>>>22))+(e&f^e&g^f&g);q=n;n=m;m=h;h=j+k|0;j=g;g=f;f=e;e=k+l|0}b[0]=b[0]+e|0;b[1]=b[1]+f|0;b[2]=b[2]+g|0;b[3]=b[3]+j|0;b[4]=b[4]+h|0;b[5]=b[5]+m|0;b[6]=b[6]+n|0;b[7]=b[7]+q|0},_doFinalize:function(){var a=this._data,d=a.words,b=8*this._nDataBytes,e=8*a.sigBytes;
d[e>>>5]|=128<<24-e%32;d[(e+64>>>9<<4)+14]=h.floor(b/4294967296);d[(e+64>>>9<<4)+15]=b;a.sigBytes=4*d.length;this._process();return this._hash},clone:function(){var a=q.clone.call(this);a._hash=this._hash.clone();return a}});s.SHA256=q._createHelper(f);s.HmacSHA256=q._createHmacHelper(f)})(Math);
(function(){var h=CryptoJS,s=h.enc.Utf8;h.algo.HMAC=h.lib.Base.extend({init:function(f,g){f=this._hasher=new f.init;"string"==typeof g&&(g=s.parse(g));var h=f.blockSize,m=4*h;g.sigBytes>m&&(g=f.finalize(g));g.clamp();for(var r=this._oKey=g.clone(),l=this._iKey=g.clone(),k=r.words,n=l.words,j=0;j<h;j++)k[j]^=1549556828,n[j]^=909522486;r.sigBytes=l.sigBytes=m;this.reset()},reset:function(){var f=this._hasher;f.reset();f.update(this._iKey)},update:function(f){this._hasher.update(f);return this},finalize:function(f){var g=
this._hasher;f=g.finalize(f);g.reset();return g.finalize(this._oKey.clone().concat(f))}})})();
////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////
//Poloniex balances (you should stop using this by the way!)
function getPoloniexBalance(coin) {
var POLONIEX_APIKEY = "YOUR_POLONIEX_API_KEY_HERE"; //REPLACE WITH YOUR API KEY!
var POLONIEX_SECRET = "YOUR_POLONIEX_API_SECRET_HERE"; //REPLACE WITH YOUR API SECRET!
var nonce = 1517426902234426 + new Date().getTime();
var p = "command=returnBalances&nonce="+nonce
var signature = Utilities.computeHmacSignature(Utilities.MacAlgorithm.HMAC_SHA_512, p, POLONIEX_SECRET);
signature = signature.map(function(byte) {
return ('0' + (byte & 0xFF).toString(16)).slice(-2);
}).join('')
var headers = {
"Key" : POLONIEX_APIKEY,
"Sign" : signature
};
var options = {
"method" : "POST",
"headers": headers,
"payload": p
};
Utilities.sleep(1000) //This is to prevent nonce overlaps for multiple rapid calls
var response2 = UrlFetchApp.fetch("https://poloniex.com/tradingApi", options);
var json2 = JSON.parse(response2.getContentText());
var balance = json2[coin];
return balance;
};
////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////
//Bittrex Balances
function getBittrexBalance(coin) {
var BITTREX_API_KEY = "YOUR_BITTREX_API_KEY_HERE"; //REPLACE WITH YOUR API KEY!
var BITTREX_SECRET = "YOUR_BITTREX_API_SECRET_HERE"; //REPLACE WITH YOUR API SECRET!
var nonce = 1505426902234426 + new Date().getTime();
var request_url = "https://bittrex.com/api/v1.1/account/getbalance?apikey="+BITTREX_API_KEY+"&nonce="+nonce+"&currency="+ coin;
var signature = Utilities.computeHmacSignature(Utilities.MacAlgorithm.HMAC_SHA_512, request_url, BITTREX_SECRET);
signature = signature.map(function(byte) {
return ('0' + (byte & 0xFF).toString(16)).slice(-2);
}).join('');
var headers = {
apisign: signature
};
var options = {
"headers": headers
};
Utilities.sleep(1000) //This is to prevent nonce overlaps for multiple rapid calls
var response3 = UrlFetchApp.fetch(request_url, options);
var json3 = JSON.parse(response3.getContentText());
var bittrexBalance = json3.result.Balance;
return bittrexBalance;
}
////////////////////////////////////////////////////////////////////////////////////////////////
@ricardochaves
Copy link

You left some API keys in the code. I haven't tested if they work, but if they exist, it would be good to remove them.

by the way, good job!

@sargonas
Copy link
Author

sargonas commented Jan 3, 2022

@ricardochaves I knowingly left a few that were cycled out and only used for testing purposes so I wasn't worried... but given I don't make that clear in the documentation your concerns are justified so thanks for bringing that up!

As old an outdated as this doc is for my uses (i wrote this 4 and a half years ago and odds are a lot of it won't work right anymore, unsure) it's probably a good idea I update that just to be clear. Thanks!

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