RE: The Cryptocurrency Bank Spreadsheet

You are viewing a single comment's thread from:

RE: The Cryptocurrency Bank Spreadsheet

View the full context

slomo (40)in #crypto • 7 years ago

Thanks for sharing, love the spreadsheet.

To get it to auto-update with fresh prices every minute, I went to File/Spreadsheet Settings, Calculation tab, set Recalculation to "On change and every minute."

7 years ago in #crypto by slomo (40)

Sort:

lukestokes (75) 7 years ago

Oh, I thought it wouldn't recalculate because the values were derived from a function call (with cached output) and the values to the function call have not changed. I thought that's why the original spreadsheet had the currency type as an input to the function to cause it to recalculate when the currency is changed.

$0.00

[-]

slomo (40) 7 years ago

You're right, I didn't test this well and it isn't working. Changing the value in L1 forces a refresh but I'm unable to use a now() or rand() function to trigger this because of restrictions on custom functions. If I have time later I'll look for another option for auto-refresh.

$0.29

4 votes

[-]

lukestokes (75) 7 years ago

I found one earlier, but haven't implemented it yet. Essentially it talked about creating a function which returns rand() or now() and then using that output as a function input to the ImportJSON call. Then the trick is just triggering that function. Ideally a little "Refresh" button would do the trick, but I'm not sure if Google sheets supports anything like that.

Also, check the updates I added today. You can now see your wallet balances. :)

$0.00

[-]

slomo (40) 7 years ago

I got a refresh button working by adding this function to the code:

function increment()
{
  SpreadsheetApp.getActiveSheet().getRange('L1').setValue(SpreadsheetApp.getActiveSheet().getRange('L1').getValue()+1);
}

Then drawing a button like this: http://stackoverflow.com/a/6966272
and attaching the increment script to it.

No luck yet on getting auto-refresh to work.

$0.09

4 votes

[-]

lukestokes (75) 7 years ago

Made another tweak to my personal copy:

With this new function:

function takeSnapshot()
{
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 var range = sheet.getRange("K2:L2");
 var rowCount = SpreadsheetApp.getActiveSheet().getRange('SnapshotRow').getValue();  
 rowCount = rowCount+1;
 SpreadsheetApp.getActiveSheet().getRange('SnapshotRow').setValue(rowCount);  
 range.copyValuesToRange(range.getGridId(), 11, 12, rowCount+2, rowCount+2);
}

with SnapshotRow being M10. Every time the snapshot button is pressed, it copies the current values from K2:L2 and puts them in the portfolio value section.

I'd love to add these to the main sheet, but if that triggers the script access stuff, then that's a non-starter, IMO.

$0.00

2 votes

[-]

lukestokes (75) 7 years ago (edited)

Very cool! I made some changes on my personal copy with this:

function refreshJSON()
{
  var d = new Date();
  var currentTime = Utilities.formatDate(d, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "yyyy-MM-dd hh:mm a");
  SpreadsheetApp.getActiveSheet().getRange('LastUpdated').setValue(currentTime);
}

(instead of L1, I'm using a named range "LastUpdated")

The button works great, but I learned you have to go to spreadsheet settings to set your timezone as you want it.

Here's the main problem with this approach:

I really don't like having to grant access to a script and I'm sure others won't either. :(

It won't let you use NOW() directly:

So... yeah. I'm thinking most people will probably just want to leave it as is instead of granting a script they didn't write access to their stuff.

$0.00

2 votes

[-]

slomo (40) 7 years ago

Cool, I'll check out those changes.

I'm not sure if my last version required the script permissions or not because I'd already granted them when trying something else, but yeah, that was a "scary" permissions ask. I'd already read the code and wasn't worried about that, but I had to dig around Google's docs a bit to make sure I was only granting my copy of the script permissions and not the public copy.

I don't seem to be able to reply to you anymore - I guess there's a maximum reply depth on steemit? I'm new to the platform but really liking it so far.

$0.00

STEEM 0.19

TRX 0.15

JST 0.029

BTC 63287.47

ETH 2569.39

USDT 1.00

SBD 2.81

RE: The Cryptocurrency Bank Spreadsheet

Coin Marketplace