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:
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
CodePudding user response：
I believe your goal is as follows.
- You want to retrieve the 1st values from the URL of
https://pool.rplant.xyz/api2/poolminer2x/raptoreum/RThRfoQJg8qsoStLk7QdThQGmpbFUCtvnk/UlRoUmZvUUpnOHFzb1N0TGs3UWRUaFFHbXBiRlVDdHZua3x4using Google Apps Script and want to use the retrieved values at Google Spreadsheet.
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>
- In this script, please run
main()function from the script editor. By this, a dialog is opened on the Spreadsheet and the values are retrieved from the URL using Javascript, and when the 1st values are retrieved, the values are sent to Google Apps Script side. So you can use the retrieved values at the function ofgetValues.
Note:
In this workaround, it is required to execute the script by the browser. Because Javascript is used. So, please be careful about this.
As another workaround, when you can use only Javascript, Sheets API can be used with Javascript. Ref In this case, the values can be also retrieved and put to the Spreadsheet using Javascript.