Hacking MyBitcoins Gadget to Retrieve Data From Google Docs Speadsheet
Let's assume you want to know your Bitcoin net worth in US$ at all times. If you know how much bitcoins you have, with the help of MyBitcoins gadget it's rather easy; you just go to settings, and enter Bitcoin amount in correction field.
But what if that amount changes daily, and you want to automate this by fetching that amount from Google Docs spreadsheet?
Well, MyBitcoins is able to import data from pools. And it's not neccessarily a pool, it can be any JSON source... such as Google Docs spreadsheet. Here's how.
Step 1: Prepare (optional)
First, we need to publish our spreadsheet to the web to be able to access it programmatically. While spreadsheet sharing settings stay the same (it can be private, public, or shared between several people), this creates public copy of the spreadsheet which can be viewed by anyone who has a link. Unless your spreadsheet is not public anyway, this might be a bit of a problem for you. Now, you can publish only a single sheet, but I've had problems accessing it in JSON form (I get a message that "You do not have view access to the spreadsheet. Make sure you are properly authenticated."). I don't know if this is a bug or simply a missing feature, but this means that we're stuck with publishing all sheets or none.
Fortunately, there is a trick to get around that if you want to fetch specific piece of data instead of the whole sheet. You can create another, blank Google Docs spreadsheet, and pull only specific cell or range of cells in it. There's a function for that, IMPORTRANGE, which accepts two parameters. First one is spreadsheet key, and the second is name of sheet with cell position. You can find your spreadsheet key from the spreadsheet URL:
If we want to pull cell B5 in sheet named Sheet1, our function looks something like this:
=IMPORTRANGE("0Agd_0WH-ofPxdHQ1TWYtbm9ueUpHbFdjZU9kakZEWHc", "Sheet1!B5")
Step 2: Publish
Now we're ready to publish to the web. Go to File menu and choose "Publish to the web".
You will see the publish to the web dialog; press the "Start publishing" button.
Step 3: Retrieve
There is a link at the bottom of publish to the web dialog where you can see a published spreadsheet, but we don't really need it, although our spreadsheet key will appear here as well. Instead, we need to construct a link to our JSON feed:
http://spreadsheets.google.com/feeds/cells/0Agd_0WH-ofPxdFE5b2szdjRTMEJrTzF3RFRsZm14Mnc/od6/public/values/R2C1?alt=json
The green part is a spreadsheet key; insert your spreadsheet key there. The blue part is row and column we want to extract; row 2 and column 1 gives us cell A2. This will return all cells in a spreadsheet and we can examine JSON in Firebug or online JSON viewer such as this one (paste JSON and press Format button). If we want to access value in cell A2, careful examination will reveal that we need this JSON key:
json.entry.gs$cell.numericValue
And this is it - now we go to gadget settings and put our URL in Pool #1, Pool API URL, and our JSON expression in unpaid amount JSON key (although we could put it in paid amount as well). Now the amount shown in the gadget will be pulled from Google Docs spreadsheet.