'Google Spreadsheet with data from WebSocket

I want to write a custom script using Google Apps Script for Spreadsheet that gets data from WebSocket. Is there a way doing so?

google-apps-script google-sheets

Solution 1:^[1]

No, Google Apps Script does not include any Web APIs other than what UrlFetchApp offers. In particular, it cannot maintain a websocket connection.

Solution 2:^[2]

Let's try with Binance for example. You can use websocket in an html file. It means that you can create a sidebar with this html file and transfer information with it.

<!DOCTYPE html>
  <meta charset="utf-8" />
<head>
  <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
  <style>
    body{margin:12px;}
  </style>
  <script language="javascript" type="text/javascript">

  var wsUri = ("wss://stream.binance.com:9443/ws");
  <? var myparam = param(); ?>
  var nbr=0
  
  function init()  {
    websocket = new WebSocket(wsUri);
    websocket.onopen = function(evt) { onOpen(evt) };
    websocket.onclose = function(evt) { onClose(evt) };
    websocket.onmessage = function(evt) { onMessage(evt) };
    websocket.onerror = function(evt) { onError(evt) };
    nbr=0;
  }

  function onOpen(evt)  {
    writeLog("Connected to:<br>" + wsUri);
    doSend(<?= myparam ?>);
  }

  function onClose(evt)  {
    writeLog("Disconnected from:<br>" + wsUri);
  }

  function onMessage(evt)  {
    nbr++;
    google.script.run.getJSON( evt.data );
    document.getElementById('nbr').innerHTML=('<span style="color: blue;">Response #<b>' + nbr + '</b> transmitted to sheet</span>');
  }

  function onError(evt)  {
    writeLog('<span style="color: red;">ERROR:</span> ' + evt.data);
  }

  function doSend(message)  {
    writeLog("Sent: " + message);
    websocket.send(message);
  }

  function writeLog(message)  {
    var pre = document.createElement("p");
    pre.style.wordWrap = "break-word";
    pre.innerHTML = message;
    document.getElementById("log").appendChild(pre);
  }

  window.addEventListener("load", init, false);
  window.addEventListener("beforeunload", websocket.close, false);
  
  </script>
</head>
<body>
    <div id="nbr">&nbsp;</div><br>
  <div id="lastButtonBlock" class="block">
    <button onclick="init()" class ="action button actionButton" >&nbsp;Connect&nbsp;</button>
    <button onclick="websocket.close()" class ="action button actionButton" >&nbsp;Disconnect&nbsp;</button>
    <button onclick="google.script.host.close()" class ="create deleteButton actionButton" >&nbsp;Close&nbsp;</button>
  </div><br>
  <div id="log"></div><br>
</body>
</html>

the gs file will be for instance

const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('derCotation');

function param(){
  var valeurs = sh.getRange('B3:B'+sh.getLastRow()).getValues().join().split(",")
  var myparams = {"method": "SUBSCRIBE", "params": valeurs, "id": 1}
  return JSON.stringify(myparams) 
}

function getJSON(jsonstring){
  try{
    var data = JSON.parse(jsonstring);
    var pairs = sh.getRange('A3:A').getValues().join().split(",");
    var ligne = pairs.indexOf(data.k.s);
    if (ligne != -1){
      var result=[]
      result.push([ data.k.o , data.k.c , data.k.h , data.k.l ])
      sh.getRange('C' + (ligne+3) + ':F' + (ligne+3)).setValues(result)
    }
  }
  catch(e){
  }
}

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	Mike Steelson

'Google Spreadsheet with data from WebSocket

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]