UPDATE! As my script has grown in functionality, I decided to share it on GitHub here:
https://github.com/edonkeycoin/gsbitcoinutilsOriginal post follows below...
--------------------------------------------------------
I'm on a Mac, so up until recently I used Numbers as the spreadsheet application for mining ROI and other Bitcoin investment worksheets. The thing is that I wanted values in the spreadsheet to automatically update, which Numbers doesn't support.
After searching for a solution, I decided to try Google Sheets. It supports automatic currency rate updates, including BTC. It also supports Javascript, so it's easy to access various blockchain info APIs.
I've used this functionality to automate my spreadsheets. It's pretty cool to get notification of a mining pool payment, then open the spreadsheet and see the ROI numbers automatically recalculate.
This info is probably not news to veteran forum members. Also much of this info can be found by Google searching. But in case anyone finds it helpful, I'm including the formulas and scripts that I'm using for my BTC spreadsheets.
First, you can paste the following formula into a cell and it provides the current BTC/dollar rate:
=GoogleFinance("CURRENCY:BTCUSD")On reddit a poster indicated that the above value comes from the Coinbase broker prices, but I have not confirmed that. For my purposes the value returned by the above function is close enough.
Next, here's some simple scripts that will return information about a given bitcoin address:
// Use blockr.io to receive JSON info for a given bitcoin address
function getAddressInfoJson(address)
{
var url = "https://btc.blockr.io/api/v1/address/info/" + address;
var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var parsed = JSON.parse(json);
var data = parsed.data
return data
}
// Return the total received by a bitcoin address
function walletReceived(address)
{
var data = getAddressInfoJson(address)
return data.totalreceived;
}
// Return the total transactions for a bitcoin address
function walletNumTransactions(address)
{
var data = getAddressInfoJson(address)
return data.nb_txs;
}
// Test the above functions
function test()
{
var address = "18wQtEDmhur2xAd3oE8qgrZbpCDeuMsdQW"
var received = walletReceived(address)
var numTransactions = walletNumTransactions(address)
Logger.log(address + ": ");
Logger.log(" received: " + received);
Logger.log(" transactions: " + numTransactions);
// Blocks if Safari popups blocked
//Browser.msgBox(numTransactions);
}
To use the above scripts, go to the Tools -> Script editor... page and paste the above script code and save it. Then you can go back to the spreadsheet and use the script functions.
For example, to get the total received by a given address enter a formula like the following into a cell:
=walletReceived("18wQtEDmhur2xAd3oE8qgrZbpCDeuMsdQW")Originally I tried using blockchain.info, because I found a script example using their API. But I started getting errors indicating that the endpoint had been used too much, or something to that effect. So I switched to blockr.io and haven't had a problem.
If you want to add a new function to access different information about an address, it's pretty easy to add. Just go to the Script editor, copy one of the functions (renaming it as appropriate) and change it to return the JSON data you're interested in. You can see the list of JSON data items returned by just pasting the following URL (with your Bitcoin address) into a browser:
https://btc.blockr.io/api/v1/address/info/18wQtEDmhur2xAd3oE8qgrZbpCDeuMsdQWI realize that Google isn't for everyone, especially sidehack
But if you can get past the potential privacy issues and working with a monster company like Google, the functionality they provide is very convenient.
