Creating live updating display from multiple Google Sheets-CodePudding

I have updating results data in four Sheets within a Google Sheets spreadsheet. I want to display the live contents of each sheet in turn on an external display (screen/projector), with an update rate of 10 to 15 seconds between each change of sheet. I've been unable to find any complete solution to this.

Things I've looked at:

Copy the data from each page into Google Slides, maintaining live data update. Then publish the four slides, specifying a refresh rate. This works, but it doesn't update the Slides publication when data in the source Sheets changes. You have to go into Slides and do a manual refresh. Then you also have to refresh the webpage displaying the published slides. It does work, but I really want the displays to update as soon as data in the Sheets are updated.
Display the Google Sheets themselves, with a mechanism to select each sheet in turn every 10 seconds. This felt do-able, with a bit of AppsScript Java I can select individual sheets. Issues with this are I've been unable to resolve are the the minimum timer for a trigger is 1 minute, and I've been unable to get a Triggered function to select the sheets. I think the context for a Trigger may prevent this working.
Use 'something else' to extract the data from the Google Sheet, and create the live updating displays. I've been unable to find anything that would do this. I suspect someone with much better web coding than mine could achieve it, but I'm trying not to get too complicated.

Any suggestions greatfully received.

CodePudding user response：

You can create a local HTML file, show the spreadsheet inside an iframe and change the iframe-URL to the next sheet every 15 seconds. This way, instead of using GAS triggers, the page gets updated using local JavaScript, so you dont have to wait 1 minute.

Save the next code as an HTML file.
Update the spreadsheetUrl and sheetIds variables
Open the file in a browser (I recommend Firefox)

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title></title>
</head>
<body>
    <iframe id="iframe1" src="" style="position:fixed; top:0; left:0; bottom:0; right:0; width:100%; height:100%; border:none; margin:0; padding:0; overflow:hidden; z-index:999999;"> Your browser doesn't support iframes
    </iframe>
</body>

<script>
    var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/123/edit#gid=";
    var sheetIds = ["12345", "23456", "34567", "45678"];
    var currentSheet = 0; //0-3
    var myIframe = document.getElementById("iframe1");

    // Initializes iframe
    myIframe.src = spreadsheetUrl   sheetIds[currentSheet];

    // Updates iframe very 10,000 milliseconds
    setInterval(function() {
        // Increases sheet
        currentSheet  ;

        // If 4th sheet has been shown, then go back to 1st sheet
        if (currentSheet > 3)
            currentSheet = 0;

        // Updates iframe
        myIframe.src = spreadsheetUrl   sheetIds[currentSheet];
    }, 10000);
</script>
</html>