RE: The Cryptocurrency Bank Spreadsheet

[lukestokes]

I wasn't sure if anyone was using this anymore. Here's the script code I use now on my version:

function getccprices(random_number)
//function getccprices()
{
  var json_data = fetchJson(1,1,random_number);
  var num_cryptocurrencies = json_data.metadata.num_cryptocurrencies;
  Logger.clear();
  Logger.log("num_cryptocurrencies: " + num_cryptocurrencies);
  var results = [];
  var start = 0;
  while (num_cryptocurrencies > 0) {
    var new_results = fetchData(100,start,random_number);
    results = results.concat(new_results);
    start += 100;
    num_cryptocurrencies -= 100;
  }
  return results;
}

function fetchJson(limit,start,random_number)
{
  var url = "https://api.coinmarketcap.com/v2/ticker/?start="+start+"&limit="+limit+"&sort=id&1="+random_number;
  var response = UrlFetchApp.fetch(url);
  var json = response.getContentText();
  var json_data = JSON.parse(json);
  return json_data;
}

function fetchData(limit,start,random_number)
{
  var json_data = fetchJson(limit,start,random_number);
  var results = [];
  var row = 0;
  for (key in json_data.data) {
    var currency = json_data.data[key];
    var rowData = [];
    rowData[0] = currency.symbol;
    rowData[1] = currency.quotes.USD.price;  
    rowData[2] = new Date(currency.last_updated * 1000);
    results[row] = rowData;
    row++;
    //Logger.log("details: " + currency.symbol + " " + currency.quotes.USD.price);
  }
  return results;
}

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);
}

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

I also added a new sheet to get prices with this:

=getccprices('Prices & Totals'!M2)

Comments

[odelayjb]

Where do I paste the first code? The revision I've got already has the new sheet with getccprices code. Thanks @LukeStokes.

Poking around more I assume it is Tools > Script Editor

And I imagine it is expected that you must grant authorization for the script to access your Google account? Which it makes sound very imposing.

[lukestokes]

Yes, the script editor. The version I'm using personally does have script access, I think, which is partly why I haven't bothered to update the publicly shared one since many may not be comfortable doing that. The main benefit, I think, is having a button I can press to refresh the rates.