build real time dashboard using google apps script

Question

I want to be able to update my web app constantly (in real time) such that anytime there's an update on my Google Sheet (via a webhook configured using apps script's doGet function), the same is shown in the HTML dashboard that I've built.

I don't need help with setting up my sheet, or the webhook or the HTML dashboard - I have all of that already setup.

I do need help / advise on how can I update my HTML dashboard (web app) any time there's an update either on my doGet function or on the sheet (that part doesn't really matter).

The best example would be the way Google Analytics realtime dashboard changes, every time there's a new user who lands on your website.

PS. I know I'm supposed to share some code but everything I have has nothing to do with what I actually want; hope that's clear but should there be a need for any of you to see my code/sheet, I'd be happy to create a dummy version.

Answer 1

You'll need to use:

• google.script.run.withSuccessHandler which is a JavaScript, asynchronous, Client-side API that allows you to interact with server side functions (references could be found here).
• setInterval function to invoke the aforementioned client-side API at a frequency you see fit
  • 3000/3500 milliseconds is what I've been using so far and the service quotas don't specifically talk about its limitations

Server side

This is pretty much the code that gets written in the code.gs part of the script; where all your functions reside that interact perhaps with your Spreadsheets or act as the webhook

Client side

That's the code that runs from your *.html file and, post loading, on your web browser. This is where you get to use the "asynchronous" API

Example

In my dummy setup, I'm -

1. Fetching random quotes from thesimpsonsquoteapi
2. Displaying a timer that changes every second

Code.gs (server-side code)

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('Index').setTitle('Realtime Data');
}

function randomQuotes() {
  var baseURL = 'https://thesimpsonsquoteapi.glitch.me/quotes';
  var quotesData = UrlFetchApp.fetch(baseURL, { muteHttpExceptions: true });
  var quote;
  var imageURL;
  if (quotesData.getResponseCode() == 200 || quotesData.getResponseCode() == 201) {
    var response = quotesData.getContentText();
    var data = JSON.parse(response)[0];
    quote = data["quote"];
    imageURL = data["image"];
  } else {
    quote = 'Random Quote Generator is broken!';
    imageURL = 'https://cdn.shopify.com/s/files/1/1061/1924/products/Sad_Face_Emoji_large.png?v=1480481055';
  }
  var randomQuote = {
    "quote": quote,
    "imageTag": '<img class="responsive-img" src="' + imageURL + '">'
  }
  return randomQuote;
}

function getTime() {
  var now = new Date();
  return now;
}

Index.html (client-side code)

I'm only highlighting the relevant aspects of the code

The following fetches random quotes every 10 seconds (10000 ms)

<script>
    function onSuccess1(quotedata) {
        var quoteactual = document.getElementById('quote');
        quoteactual.innerhtml = quotedata.quote;
        var quoteimg = document.getElementById('quoteImage');
        quoteimg.innerhtml = quotedata.imagetag;
    }

    setInterval(function() {
        console.log("getting quote...")
        google.script.run.withSuccessHandler(onsuccess1).randomQuotes();
    }, 10000);
</script>

This fetches time every 1 second (1000 ms)

<script>
    function onSuccess2(now) {
        var div = document.getElementById('time');
        var today = new Date();
        var time = today.getHours() + " : " + today.getMinutes() + " : " + today.getSeconds();
        div.innerhtml = time;
    }

    setInterval(function() {
        console.log("getting time...")
        google.script.run.withSuccessHandler(onsuccess2).getTime();
    }, 1000);
</script>

You can access the entire script on my github repository or make a copy from the original script.

Output

The image here is supposed to change every 10s and timer, every 1s

The browser console log can be viewed here -

I wrote this article a couple weeks ago that outlines most aspects of what everyone has been answering/commenting so far but I'm hoping my explanation here helps as well.

Answer 2

In order to accomplish real-time performance to your dashboard, you would need to use Web Push notifications as the user @Dimu Designs claimed, or keep an open websocket connection between the client and your webapp. However, I'm afraid that for the time being, none of these solutions are supported.

The only solution that's left would be to employ polling: The HTML served to the client would have Javascript code embedded to it that would set up a cron-like function that issues a request to your webapp every X seconds (see setInterval) and updates your dashboard's content.

Bear in mind that this solution will involve sending many HTTP requests to your webapp, which will have the following downsides:

1. An increased amount of bandwidth consumption.
2. A non-real time dashboard, which will lag up to the X seconds determined to be the refresh rate.
3. Upon sending a large number of requests in a short-time period, you may experience rate limitations. For more information about this, you can check the quotas here.

Answer 3

I agree with the answer from @Cooper, @Dimu Designs and @carlesgg97. But I also also suggest an alternative solution via Google Sites and/or Google Sheets.

Dont know if you are willing to pass your HTML dashboard to Sites (it does support inserting HTML) , but if you do, you can insert the graphs directly from Google Sheets (or create graphs in there), even as tables, and they would automatically update (not embed ones though). [https://developers.google.com/chart/interactive/docs]

Optionally, you can create graphs in Google Sheets and then publish them [https://support.google.com/docs/answer/1047436?co=GENIE.Platform%3DDesktop&hl=en], which would provide you with a link you use in HTML.