running websocket in google sheet-CodePudding

I use API Endpoint in my app script google sheet but some time, I get this Error :

{"code":-1003,"msg":"Way too much request weight used; IP banned until 1632924884440. Please use the websocket for live updates to avoid bans."}

Any Solution for that problem, I read A lot of content but there are says this is able to do websocket and another article says not able to do websocket in google sheet. If I don't able to do it, There are another solutions for that??

CodePudding user response：

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) { one rror(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 one rror(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);
  }

  </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){
  }
}

here is a complete spreadsheet