AppsScript_TickerData

In this example below you will see how to do a AppsScript_TickerData with some HTML / CSS and Javascript

Thumbnail
This awesome code was written by 0damian, you can see more from this user in the personal repository.
You can find the original code on Codepen.io
Copyright 0damian ©
  • HTML
  • JavaScript
<!DOCTYPE html>
<html lang="en" >

<head>
  <meta charset="UTF-8">
  <title>AppsScript_TickerData</title>
  
  
  
  
  
</head>

<body>

  
  
  

    <script  src="js/index.js"></script>




</body>

</html>

/*Downloaded from https://www.codeseek.co/0damian/appsscript_tickerdata-WXvvzz */
function getTickerData() {
  var SS = SpreadsheetApp.openById(''),
      TICKERS = ['btcusd', 'ethusd', 'ethbtc', 'bchbtc'],
      SHEETS = SS.getSheets(),
      METRICS = [['last'], ['high'], ['low'], ['change'], ['volume'], ['bid'], ['ask'], ['spread']],
      URL = 'https://api.cryptowat.ch/';
  
  // Get all current sheet names
  var sheetNames = SHEETS.map(function(sheet) {
    return sheet.getName();
  });
  
  for (var i=0;i<TICKERS.length;i++) {
    var activeSheet;
    // Activate / create sheet for ticker
    if (sheetNames.indexOf(TICKERS[i]) < 0) {
      activeSheet = SS.insertSheet(TICKERS[i]);
      var dateTitle = activeSheet.getRange('a1:b1').merge();
      dateTitle.setValue('datetime').setFontWeight('bold').setHorizontalAlignment('center');
    } else { activeSheet = SS.setActiveSheet(SS.getSheetByName(TICKERS[i])); }
    
    // Insert row below column titles for new data
      activeSheet.insertColumnBefore(3);
    
    // Record datetime of function call
    var d = new Date(),
        dateCell = activeSheet.getRange('c1');
    dateCell.setValue(d.toUTCString()).setFontWeight('bold');
    activeSheet.setColumnWidth(3,200);
    
    // Get all exchanges in active sheet
    var activeCells = activeSheet.getDataRange(),
        lastRow = activeCells.getLastRow().toFixed(),
        exchangeRangeStr = 'a2:a' + lastRow;
    var exchangeRanges = activeSheet.getRange(exchangeRangeStr).getMergedRanges();
    
    var exchanges = {};
    for (var j=0;j<exchangeRanges.length;j++) {
      exchanges[exchangeRanges[j].getValue()] = exchangeRanges[j].getA1Notation();
    }
    
    // Cryptowat.ch API tickers endpoint
    var tickerUrl = URL + 'pairs/' + TICKERS[i];
    // Get all markets for ticker
    var tickerResp = UrlFetchApp.fetch(tickerUrl, {'muteHttpExceptions': true}),
        tickerData = JSON.parse(tickerResp),
        markets = tickerData.result.markets;
    
    for (var k=0;k<markets.length;k++) {
      // Get summary and orderbook routes
      var summaryUrl = markets[k].route + '/summary';
      var orderbookUrl = markets[k].route + '/orderbook';
      
      // Add exchange if not found in active sheet
      if (!markets[k].active) {
        continue;
      } else if (!(markets[k].exchange in exchanges)) {
        var newExchange = markets[k].exchange;
        // Set ranges for exchange and metric column titles
        var start = (Number(lastRow) + 1), end = (Number(lastRow) + 8);
        var exRngString = 'a' + start + ':a' + end;
        var metricsRngString = 'b' + start + ':b' + end;
        var targetRngString = 'c' + start + ':c' + end;
        var exCells = activeSheet.getRange(exRngString).merge(),
            metricsCells = activeSheet.getRange(metricsRngString);
        
        // Add values to row titles
        exCells.setValue(newExchange).setVerticalAlignment('middle').setHorizontalAlignment('center').setFontWeight('bold');
        metricsCells.setValues(METRICS).setFontWeight('bold');
        lastRow = (Number(lastRow) + 8);
      } else {
        targetRngString = exchanges[markets[k].exchange].replace(/A/g,'C');
      }
      
      // Select metrics cells
      var dataCells = activeSheet.getRange(targetRngString),
          dataVals = [];
      
      // Get market summary data
      var summaryResp = UrlFetchApp.fetch(summaryUrl, {'muteHttpExceptions': true}),
          summaryData = JSON.parse(summaryResp).result;
      
      if (summaryData && summaryData.volume) {
        // Build metrics array
        // Logger.log('sumData: ' + JSON.stringify(summaryData));
        dataVals.push([summaryData.price.last]);
        dataVals.push([summaryData.price.high]);
        dataVals.push([summaryData.price.low]);
        dataVals.push([summaryData.price.change.absolute]);
        dataVals.push([summaryData.volume]);  
      } else { dataVals.push(['No data'], ['No data'], ['No data'], ['No data'], ['No data']); }
        
      // Get market orderbook data
      var orderbookResp = UrlFetchApp.fetch(orderbookUrl, {'muteHttpExceptions': true}),
          orderbookData = JSON.parse(orderbookResp).result;
      // Logger.log('obData: ' + JSON.stringify(orderbookData));
      if (orderbookData && orderbookData.asks.length > 1 && orderbookData.bids.length > 1) {
        dataVals.push([Number(orderbookData.asks[0][0])]);
        dataVals.push([Number(orderbookData.bids[0][0])]);
        dataVals.push([Number((orderbookData.asks[0][0] - orderbookData.bids[0][0]))]); 
      } else { dataVals.push(['No data'], ['No data'], ['No data']); }
      
      // Add metrics to cells
      dataCells.setValues(dataVals);  
    }
  }
}

Comments