Google Sheets App Script code for CryptoCurrency price tracking #Javascript

I do a lot of crypto currency trading on Bittrex.com and I made a Google Sheet to help me keep track of my return on investment on each of the currencies in my portfolio, as well as an overall ROI for my entire portfolio.

Both Bittrex.com and Coinmarketcap.com provide free public API’s that will return current prices, market cap, volume, and other useful information.  Free to use and you do not need to provide authentication.

With Google Sheets you can add your own scripts to execute and return values you can use in a worksheet calculation. Just like how you would enter “=SUM(A1:A5)” in a cell to add the values of cells A1 – A5, you enter the name of your custom function.



To enable scripts for your Google Sheet, open a sheet then click Tools > Script Editor…

This will open a new window with a basic environment for you to write your custom functions. The language is Javascript and there are loads of fun things you can do.

Heres a code snippet for Google Sheets App Scripts you can use to get the last price of a cryptocurrency, convert to USD, and display the result in a cell.

function updateBittrex(crypto) {
var btc = UrlFetchApp.fetch("https://bittrex.com/api/v1.1/public/getticker?market=BTC-" + crypto);
var j1=JSON.parse(btc.getContentText());
var btctousd = this.btctousd();
var con = btctousd * j1.result.Last;
return con;
}

function btctousd() {
var converted = UrlFetchApp.fetch("https://api.coinmarketcap.com/v1/ticker/bitcoin/?convert=USD");
var json1 = JSON.parse(converted.getContentText());
return json1[0].price_usd;
}

The Bittrex.com API returns values in BTC, so if you use USD as your Fiat currency as I do, its helpful to convert it into USD for easier reference.

In order to use the custom functions you have to authorize your add-on to run in your Google Sheet. From within the Script Editor, click Run > Test as add-on. Next in the Configure New Test, choose – Test with Latest Code, and under Installation Config choose, “Installed and Enabled”. Then click the Select Doc button and choose your Worksheet. Click save and you should see this. Click the radio button for the doc and click Test.

Now when you’re in a cell and wish to retrieve the last price for Monero, for instance, simply enter “=updateBittrex(“XMR”)” and the script will get the last price for Monero, get the last price for a Bitcoin in USD, and return the last price for Monero in USD.

Heres an example using Ethereum. The column $ per coin has the value “=updateBittrex(“ETH”)” in it.