Google Apps Script (web deploy) auto reload page

Question

I have written two small Google Apps, deployed as web apps, to "live stream" my son's baseball games. One app (I call it "Game Day") lets those watching the game see the score, inning, balls, strikes, and outs. The second app (I call it "Score Keeper") lets somebody update those stats. Both apps access a Google Spreadsheet.

Both apps show the correct stats from the Google Spreadsheet when first loaded, but only update the stats when the webpage is manually reloaded. I would like to auto reload "Game Day" every X seconds, but when I use something like setTimeout("location.reload(true);", 10000); in my Javascript file, when the page reloads, it is blank.

I have the same problem with the "Score Keeper" app in that after clicking a button--to increment the number of balls, for example--nothing changes on the web page unless I manually reload. The button does correctly increment the number of balls in the spreadsheet. If I then manually reload, it reads the stats from the spreadsheet and so displays the correct stats again.

I have read about and tried to implement google.script.run.withSuccessHandler(), but how the withSuccessHandler() works is currently beyond my understanding. If that is indeed the way to do it, I'd love help implementing it for my particular case as I have not been able to extrapolate from other people's examples.

Here is my code:

"Game Day" Code.gs:

var id = 'Google Spreadsheet ID';
var ss = SpreadsheetApp.openById(id);
var sheet = ss.getSheetByName('score');
var statsRange = sheet.getRange(2,1,1,9);

function doGet(e) {
  var temp = HtmlService.createTemplateFromFile('index');
  temp.stats = getScore();
  return temp.evaluate();
}

function getScore() {
  var s = statsRange.getValues();
  return s[0];
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

"Game Day" index.html:

<div class="container">

  <div class="row">
    <div class="col s12 center-align">
      <h5><?= stats[0] + " (home) v. " + stats[1] + " (guest)"; ?></h5>
    </div>
  </div>
  
  <div class="row">
    <div class="col s4 center-align"><b>Home</b></div>
    <div class="col s4 center-align"><b>Inning</b></div>
    <div class="col s4 center-align"><b>Guest</b></div>
  </div>

  <div class="row">
    <div class="col s4 center-align stat"><?= stats[4] ?></div>
    <div class="col s2 right-align stat"><?= stats[2] ?></div>
    <div class="col s2 left-align stat"><?= stats[3] ?></div>
    <div class="col s4 center-align stat"><?= stats[5] ?></div>
  </div>

  <div class="row"></div>

  <div class="row">
    <div class="col s4 center-align"><b>Balls</b></div>
    <div class="col s4 center-align"><b>Strikes</b></div>
    <div class="col s4 center-align"><b>Outs</b></div>
  </div>

  <div class="row">
    <div class="col s4 center-align stat"><?= stats[6] ?></div>
    <div class="col s4 center-align stat"><?= stats[7] ?></div>
    <div class="col s4 center-align stat"><?= stats[8] ?></div>
  </div>

  <div class="row">
    <div class="col s12 center-align refresh">refresh page to update stats</div>
  </div>

</div>

<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
<?!= include('index-js'); ?>

"Game Day" index-js.html:

<script>
  setTimeout("location.reload(true);", 10000);
</script>

I also just tried adding a Simple Trigger in the Google Apps Script editor to run on the doGet() function every minute. It executed the trigger every minute, but the page did not change.

I added `setTimeout("window.top.location.reload();", 5000);` to my index-js.html file and nothing seems to happen... no reload and no blank page. (I change the stats in the spreadsheet manually and then wait.) And because I don't really know what I'm doing, I also tried `setTimeout(window.top.location.reload(), 5000);` as well as `setTimeout(function(){ alert("Hello");},5000); ` The alert does pop up once, but no reloads and the stats don't change if I change them in the spreadsheet. — ledisnomad, Oct 16 '21 at 19:48
Check your browser console for errors. Forget setTimeout and all your code, focus on getting a reload once — TheMaster, Oct 16 '21 at 20:02
This is what the console says when I load my page/web app with a simple `window.top.location.reload()` in the index-js.html file... userCodeAppPanel:10 Uncaught DOMException: Blocked a frame with origin "https://n-ljffvpsnqcfjgtbyymmo3j5jcyxiq372n4jyeky-0lu-script.googleusercontent.com" from accessing a cross-origin frame. at https://n-ljffvpsnqcfjgtbyymmo3j5jcyxiq372n4jyeky-0lu-script.googleusercontent.com/userCodeAppPanel:10:23 (anonymous) @ userCodeAppPanel:10 — ledisnomad, Oct 16 '21 at 22:28
Make sure that that error is not a generic error(i.e., it still occurs even without `location.reload()`) If it isn't a generic error, you'll have to use `google.script.run.withSuccessHandler()`. Or if you're ok with two webapps, it might be possible - webapp1 to open webapp2. Webapp1 will run in the background - using setTimeout reloading webapp2 — TheMaster, Oct 16 '21 at 23:59
I do not get an error when I remove the `location.reload()` from index-js.html and no error with `setTimeout(function(){ alert("Hello");}, 5000);` in the index-js.html. Thank you so far for your help. Any chance you can help me figure out how to use `google.script.run.withSuccessHandler()`? — ledisnomad, Oct 17 '21 at 00:51
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/238234/discussion-between-ledisnomad-and-themaster). — ledisnomad, Oct 17 '21 at 01:24
Does this answer your question? [Polling a spreadsheet?](https://stackoverflow.com/questions/12124688/polling-a-spreadsheet) — Kos, Oct 17 '21 at 12:00

score 1 · Accepted Answer · answered Oct 17 '21 at 13:19

You can use google.script.run and html data attributes to achieve your goal:

code.gs

function getStats() {
  return new Array(15).fill("").map(Math.random)
}

function doGet(e) {
 return HtmlService.createTemplateFromFile('index').evaluate();
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top" />
    <style>
      .stats::after {
        content: attr(data-stats-value);
      }
    </style>
  </head>

  <body>
    <div class="container">
      <div class="row">
        <div class="col s12 center-align">
          <h5 id="header"></h5>
        </div>
      </div>

      <div class="row">
        <div class="col s4 center-align"><b>Home</b></div>
        <div class="col s4 center-align"><b>Inning</b></div>
        <div class="col s4 center-align"><b>Guest</b></div>
      </div>

      <div class="row">
        <div
          class="col s4 center-align stats"
          data-stats-index="4"
          data-stats-value=""
        ></div>
        <div
          class="col s2 right-align stats"
          data-stats-index="2"
          data-stats-value=""
        ></div>
        <div
          class="col s2 left-align stats"
          data-stats-index="3"
          data-stats-value=""
        ></div>
        <div
          class="col s4 center-align stats"
          data-stats-index="5"
          data-stats-value=""
        ></div>
      </div>

      <div class="row"></div>

      <div class="row">
        <div class="col s4 center-align"><b>Balls</b></div>
        <div class="col s4 center-align"><b>Strikes</b></div>
        <div class="col s4 center-align"><b>Outs</b></div>
      </div>

      <div class="row">
        <div
          class="col s4 center-align stats"
          data-stats-index="6"
          data-stats-value=""
        ></div>
        <div
          class="col s4 center-align stats"
          data-stats-index="7"
          data-stats-value=""
        ></div>
        <div
          class="col s4 center-align stats"
          data-stats-index="8"
          data-stats-value=""
        ></div>
      </div>

      <div class="row">
        <div class="col s12 center-align refresh">
          refresh page to update stats
        </div>
      </div>
    </div>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
    <?!= include('index-js'); ?>
  </body>
</html>

index-js.html

<script>
  const updateScore = (stats) => {
  document.querySelector('#header').textContent =
    stats[0] + ' (home) v. ' + stats[1] + ' (guest)';
  document
    .querySelectorAll('.stats')
    .forEach(
      (el) => (el.dataset.statsValue = stats[Number(el.dataset.statsIndex)])
    );
};
setInterval(
  () => {google.script.run.withSuccessHandler(updateScore).getStats()},
  10000
);
</script>

Works beautifully @TheMaster. I added another line in index-js.html of `google.script.run.withSuccessHandler(updateScore).getStats();` so that it would load the stats right away. And of course I the code.gs function getStats() to pull from my spreadsheet. Thank you! — ledisnomad, Oct 18 '21 at 10:49

Google Apps Script (web deploy) auto reload page

1 Answers1

code.gs

index.html

index-js.html