crypto portfolio import for google sheets

cryptoPortfolio.gs

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

You can copy the above to a google spreadsheet's companion script tool to import the crypto market values and balances you want to call in real time.

At this time, I don't consider the wallet address lookups to be complete. Some only call a single address balance, but as most users have multiple public addresses per account, I need to devise a more elegant scripting to sum all the totals before loading into a cell. Additionally there is no clean way with APIs to track the balance of a Higher Deterministic Wallet (an inherent function due to it's secure design) which directly impacts BTC and LTC wallets (for those, I just manually update the balance in my spreadsheet as needed, the other coins however work just fine). Feel free to tweak this as needed, and by all means share any ideas on a more elegant solution!

Be sure to replace every instance of "KEY_HERE" with the appropriate API key. (For example, your Poloinex secret key where "POLOINEX_SECRET=" is

To call the scripts within your spreadsheet, put the following in the relevant cell. _Note, you can do some neat shortcuts, like calling a Crypto symbol from another cell such as a column header, by using Cell formula variables. Also you can do things like placing the getCoinPriceBySymbol() into the middle of a cell formula to calculate a known balance's value. Additionally, rather than call addresses directly in formulas I like to call a cell or ranges of cells from a separate sheet of categorized variables for easier tracking, _

For market values: =getCoinPriceBySymbol(CRYPTO_SYMBOL)

For cumulative BTC address balances: =btcWalletBalance(COMMA_SEPARATED_STRING_OF_ADDRESSES)

For LTC address balance: =ltcWalletBalance(WALLET_ADDRESS)

For DCR balance: =dcrWalletBalance(WALLET_ADDRESS)

For cumulative ETH address balances: =ethWalletBalance(COMMA_SEPARATED_STRING_OF_ADDRESSES)

For any ETH token balances: =tokenWalletBalance(TOKEN_ID, WALLET_ADDRESS)

For Coinbase balance: =coinbaseGetAccountBalance(ACCOUNT_ID_HASH) (this will be in the URL for a given account's detailed view)

For Poloinex balance: =getPoloniexBalance(CRYPTO_SYMBOL)

For Bittrex balance: =getBittrexBalance(CRYPTO_SYMBOL)

Lastly, Google Code scripts like to cache data, which means sometimes your balance tracking will get stale, either due to stale wallet balances or stale market values. The easiest way to fix this, is to make a cell somewhere on your background sheets that you increment by 1 each time you want a fresh update, either by hand or by "pressing" a drawn button. You can then add a reference to that cell in any of the above scripts (exampe: =ltcWalletBalance(WALLET_ADDRESS,manualIncrementCellNumber)) and then the cells will do a fully flushed re-scan each time.

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!

@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!