Had to change the block explorer API used by the script again because Blockcypher.com's limit on the number of requests per second caused errors opening my spreadsheets.
See the updated script on the GitHub page.
Topic: How to make Bitcoin spreadsheets that automatically update (Read 985 times)
Blockr.io stopped updating their data a couple of months ago. They've announced that they're closing up shop tomorrow (September 4, 2017).
As a result, I had to change the script to use a different API provider. You can find the latest version on GitHub:
https://github.com/edonkeycoin/gsbitcoinutils
As a result, I had to change the script to use a different API provider. You can find the latest version on GitHub:
https://github.com/edonkeycoin/gsbitcoinutils
Updated to include info about the new GitHub project for the scripts...
Good post. Thanks for the information, would have tipped you if I had lots of bitcoins. 
You're welcome.
I'm not really looking for tips. When I come across something cool I just like to share it.
Hope you get a lot of bitcoins someday
Good post. Thanks for the information, would have tipped you if I had lots of bitcoins.
You're welcome.
I'm not really looking for tips. When I come across something cool I just like to share it.
Hope you get a lot of bitcoins someday
Good post. Thanks for the information, would have tipped you if I had lots of bitcoins.
UPDATE! As my script has grown in functionality, I decided to share it on GitHub here:
https://github.com/edonkeycoin/gsbitcoinutils
Original 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:
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:
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:
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/18wQtEDmhur2xAd3oE8qgrZbpCDeuMsdQW
I 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.
https://github.com/edonkeycoin/gsbitcoinutils
Original 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:
Code:
=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:
Code:
// 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);
}
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:
Code:
=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/18wQtEDmhur2xAd3oE8qgrZbpCDeuMsdQW
I 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. Jump to: