Crypto Price Spot in Google Sheets

If you have a portfolio of cryptos across multiple exchanges and wallets, then you may have noticed that it's hard to track your overall asset value, especially as the values of currencies fluctuate, and some of them may not be trading against the USD directly.

What I've found to be a quick and easy way to gauge my portfolio value is to convert all the cryptos to BTC value first, and then use a BTC_USD spot price to get portfolio value in USD.

I was tracking all this info in Google Sheets, but quickly found it quite tedious to have to look-up and update the price spots for all the cryptos every time I wanted to check my portfolio. There are a few Add-ons available that will allow you to look up the price and insert it into a sheet, but they all request access to "read and share spreadsheet data" as well as "communicate with external services". Now this may all be reasonable for their purpose, but I am just very paranoid about my data and third party aps, so I decided against it.

What I decided to do instead is create a short script that can look up the price from an API and dump it into a sheet for use in my other calcs. And it's short indeed, here's the code:

var baseurl = 'https://api.binance.com'

function main_price_check(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PRICE_SPOT");
  var r = sheet.getRange(2,1,100,2);
  for(var i = 1; i <= r.getNumRows(); i++ ){
    var s = r.getCell(i, 1).getValue();
    if(Boolean(s)){
      var p = get_ticker(s);
      if( p ){
        r.getCell(i,2).setValue(p);
      }
    }
  }
}


function get_ticker(symbol) {
  var url = baseurl+'/api/v3/ticker/price?symbol='+symbol;
  var r = UrlFetchApp.fetch(url);
  if (r.getResponseCode() == 200) {
    var d = JSON.parse(r.getContentText());
    return(d.price);
  }else{
    Logger.log(r.getContentText());
    return(null);
  }
}

As you can see, all that happens is that it iterates over the A column in a sheet called "PRICE_SPOT" and looks up the prices for any symbols it finds via a URL request. I am using the Binanace API to accomplish this because it's super easy to use, and their exchange has a very large collection of alt coins. You can check them out here: https://www.binance.com

To get this to work in your Google Sheet, do the following:

1. Open the Google Sheet where you do/will track your crypto assets
2. Create a new sheet called PRICE_SPOT
3. Enter a header into row 1 like this: "Symbol", "Price"
4. Enter some symbols into column A
   1. The symbol is a pair of currencies like this: ETHBTC
   2. For $ use USDT, like this: BTCUSDT
5. Go to Tools > Script editor
6. Go through some project setup bullshit. Call it "Crypto API" or whatever, this is just to create a script workspace for your personal use.
7. Paste the code above into the Code.gs file generated, and Save
8. Go to Run > Run Function > main_price_check
9. If there was an error, you will see it in the header region. Otherwise, just go back to the spread sheet.

You should now have a sheet called PRICE_SPOT that looks something like this:

You may notice a big-ass blue button there. That's to make the script easier to run, and you can make one really simply, as follows:

1. Go to Insert > Drawing
2. Draw a button to your liking. I recommend dark background, white color Roboto font.
3. Click Save and Close
4. Right-click to select button, and (left) click the "..." icon that comes up in the right corner
5. Click Assign script and type this into the dialog: main_price_check

You should now be able to click the button and reload the prices.

THE END!