javascriptgoogle-apps-scriptgoogle-sheetsrequestevent-stream

Is there a way to get a single response from a text/event-stream without using event listeners?

I'm writing a script in Google Sheets to retrieve a value from an API. The API provides text/event-stream responses ~every 10 seconds. Is there a way I can retrieve a single response without using async functions or event listeners? I'm not very competent in JavaScript, but because I'm working in Google Sheets, it seems like async functions and event listeners won't work properly. From what I've learned so far, the only way to work with text/event-stream responses is to use EventSource but I can't make it work with Google Sheets.

My goal is just to retrieve one response from the endpoint though, so any way I can accomplish that in Google Sheets would be great. Here is the endpoint in case that helps:

https://pool.rplant.xyz/api2/poolminer2x/raptoreum/RThRfoQJg8qsoStLk7QdThQGmpbFUCtvnk/UlRoUmZvUUpnOHFzb1N0TGs3UWRUaFFHbXBiRlVDdHZua3x4

Because I was unable to use EventStream in Google Sheets, I tried using a polyfil found here: https://github.com/amvtek/EventSource/blob/master/dist/eventsource.js

and then running it with:

function getRplantTotal() {
  var source = new EventSource('https://pool.rplant.xyz/api2/poolminer2x/raptoreum/RThRfoQJg8qsoStLk7QdThQGmpbFUCtvnk/UlRoUmZvUUpnOHFzb1N0TGs3UWRUaFFHbXBiRlVDdHZua3x4');
  
  source.addEventListener("message", function(e) {
    console.log(e.data);
  });
}

but this just outputs:

3:11:49 PM  Notice  Execution started
3:11:49 PM  Notice  Execution completed
Solution

  • I believe your goal is as follows.

    Issue and workaround:

    When I saw https://github.com/amvtek/EventSource/blob/master/dist/eventsource.js, it seems that the request is run with XMLHttpRequest. At Google Apps Script, UrlFetchApp is used, and XMLHttpRequest cannot be used. I thought that this might be the reason for your current issue. But unfortunately, in the current stage, this cannot use text/event-stream type at Google Apps Script. When your URL is requested with UrlFetchApp, it looks like the infinite loop. This is the current situation.

    So, from My goal is just to retrieve one response from the endpoint though, so any way I can accomplish that in Google Sheets would be great. and the above situation, I would like to propose a workaround. When you are running your script on Google Spreadsheet, how about retrieving the value from the URL using Javascript? Google Apps Script can retrieve the values from Javascript side using a dialog and a sidebar. From your question, when Javascript is used, the value can be retrieved. I thought that this might be able to be used. When this workaround is reflected in the Google Apps Script, it is as follows.

    Sample script:

    Google Apps Script side: Code.gs

    Please copy and paste the following script to the script file of the script editor of Google Spreadsheet.

    // Please run this function.
function main() {
  SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "sample");
}

function getValues(e) {
  const obj = JSON.parse(e); // This is the 1st value from the URL of "https://pool.rplant.xyz/api2/poolminer2x/raptoreum/RThRfoQJg8qsoStLk7QdThQGmpbFUCtvnk/UlRoUmZvUUpnOHFzb1N0TGs3UWRUaFFHbXBiRlVDdHZua3x4"
  console.log(obj)
  // DriveApp.createFile("sample.txt", e); // When you use this, the retrieved value can be created as a text file.
}

    Javascript side: index.html

    Please copy and paste the following script to the HTML file of the script editor of Google Spreadsheet. Please set the filename as index.html.

    Values are retrieving now. Please wait. After the values were retrieved, this dialog is automatically closed.
<script>
var source = new EventSource('https://pool.rplant.xyz/api2/poolminer2x/raptoreum/RThRfoQJg8qsoStLk7QdThQGmpbFUCtvnk/UlRoUmZvUUpnOHFzb1N0TGs3UWRUaFFHbXBiRlVDdHZua3x4');
source.addEventListener("message", function(e) {
  source.close();
  google.script.run.withSuccessHandler(google.script.host.close).getValues(e.data);
});
</script>

    Note:

    References: