google-apps-script google-sheets web-applications embed

How do I embed a truly live Google Sheets spreadsheet into a webpage?

What I want

A website which displays the truly live sheet (updating instantly when the sheet is changed from elsewhere, like in the editor), but centered on the screen and without menus etc. (like in 2b)

Specifically a website which

shows a sheet of a Google Sheets spreadsheet, correctly formatted
updates the sheet live without any user input around once a second
does not contain Google Sheets editing headers
centeres the content in the page and has a black border to fill the screen outside of the spreadsheet

What I know

After many Google searches, I have found two results lining my goal:

1. Google Sheets editor without menu

You can directly display the sheet within the editor by simple adding ?rm=minimal to the url as in

https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/view?rm=minimal#gid=SHEET_ID

This

updates the data truly live, whenever the sheet is changed

but

shows row and column headers (A, B, C, ..., 1, 2, 3, ...)
shows sheet selection and "insert x rows below"
is not centered and does not have a black background

2. This other URL thing

When you edit the URL and replace /edit... with /htmlembed/sheet?gid=SHEET_ID like in

https://docs.google.com/spreadsheets/u/0/d/SPREADSHEET_ID/htmlembed/sheet?gid=SHEET_ID

This

does not contain any headers or similar
even allows me to specify only a fixed range to be displayed using the range=A1NOTATION parameter

It can be extended using a GScript WebApp:

2b. GScript WebApp

(Note that I used green instead of black for visualisation)

Using this URL within a GScript doGet(e) function published as a WebApp allows me to customise it further. I simply added a style-tag to the original source and used background-color as well as flex display to set the background and center the content. This is my function, WHICH IS VERY VULNERABLE TO HTML INJECTION:

function doGet(e) {
  // Getting spreadsheet app
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // Getting sheet
  var sheet = ss.getSheetByName("Monitor " + e.parameter.monitor);
  //Return error if specified sheet does not exist
  if (sheet == null)
    return HtmlService.createHtmlOutput("<b>Invalid monitor id \"" + e.parameter.monitor + "\"</b> pass as ?monitor=MONITOR");

  // Generating the URL
  var publishedURL = "https://docs.google.com/spreadsheets/u/0/d/" + ss.getId() + "/htmlembed/sheet?range=a3:z&gid=" + sheet.getSheetId();

  // Fetching the site
  var response = UrlFetchApp.fetch(publishedURL, {'muteHttpExceptions': true}).getContentText();

  // Getting the background color from paramter (default is black)
  var bg = e.parameter.bg;
  if (bg == null)
    var bg = "black";

  // Defining the styling (I know this way is lazy)
  var styling = "<style>\
body, div {\
background-color: " + bg + " !important;\
display: flex;\
justify-content: center;\
align-items: center;\
}\
</style>";

  // Returning the webpage from original data combined with styling
  return HtmlService.createHtmlOutput(response+styling);
}

This is further centered in the page and has a black border to fill the screen outside of the spreadsheet

But the URL-approach has a really significant drawback: It does not update every second, but only if the page is refreshed

What I then tried

Refreshing the webpage every second thru html or js

This should work, but since the page loads "so slowly", I would see a blank page half of the time, if I refresh every second

Fetching the URL from the client

Utilising the js fetch function, I could fetch the source on the client in the background which would then update quicker, but I ran into a cross-origin resource sharing (CORS) issue in that Google won't let me fetch the source when the request comes from the client. (It does work, when I fetch it within the GScript.)

Fetching the source from the client via the WebApp

My last resolution was to fetch the source from the WebApp, which intern fetches it from the spreadsheet, but apparently I can't allow CORS for the WebApp.

What I don't know

How do I get the middleground which a) instantly updates and b) is well formatted?

Is there something else I can do with the URL? Like /htmlembed or

https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/gviz/tq?tqx=out:html&tq&gid=0

as described in this medium post

Solution

It is possible to do this by caching the response of the fetch function and only refreshing the page if it has changed, like @TheMaster suggested. I also added a simple hash function from this post and used a regular expression to secure the code a bit against HTML-injection.

The following code will refresh the page a soon as the last update has finished (approx. every second). This is still slower then in the editor, so you may want to use solution 1 in the original question.

monitor.gs

/**
 * Only needs acced to the spredsheet the code is installed in
 * @OnlyCurrentDoc
 */

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile("frame");
}


// Fetching the live content from URL
function fetchContent(publishedURL, e) {
  // Fetching the site
  var response = UrlFetchApp.fetch(publishedURL, {'muteHttpExceptions': true}).getContentText();

  // Getting the background color from paramter (default is black)
  var bg = e.parameter.bg;
  if (bg == null)
    var bg = "black";

  // Creating and returning the response
  var template = HtmlService.createTemplateFromFile("style");
  template.bg = /\w+/.exec(bg)[0]; // Setting the background-color
  return template.evaluate().append(response);
}

// Returns the live content if it has cahnged, null otherways
function getContent(e, currentHash) {
  // Getting spreadsheet app
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // Getting sheet
  var sheet = ss.getSheetByName("Monitor " + e.parameter.monitor);
  //Return error if specified sheet does not exist
  if (sheet == null)
    return {content: "<b>Invalid monitor id \"" + /\w+/.exec(e.parameter.monitor)[0] + "\"</b> pass as ?monitor=MONITOR"};

  // Generating the URL
  var publishedURL = "https://docs.google.com/spreadsheets/u/0/d/" + ss.getId() + "/htmlembed/sheet?range=a3:z&gid=" + sheet.getSheetId();

  // Returning the content if it is different, null otherways
  var content = fetchContent(publishedURL, e).getContent();
  var hash = strhash(content);
  if (hash == currentHash)
    return null;
  Logger.log(hash);
  return {content: content, hash: hash};
}

(Also append this code)

frame.html

<!DOCTYPE html>
<html>
  <head>
  <style>
  html {
  display: flex;
  justify-content: center;
  align-items: center;
  }
  </style>
  <script>
  let currentContent = undefined;
  function updateContent(content) {
  let doc = new DOMParser().parseFromString(content, "text/html")
  let sheets_viewport = doc.getElementById("sheets-viewport");

  console.log("Current hash: " + currentContent);
  if (content !== null) {
  document.open();
  document.write(content.content);
  document.close();

  console.log("refreshed.");
  currentContent = content.hash;
  console.log("New hash: " + currentContent);
  } else
  console.log("Nothing to refresh.");

  refresh();
  }
  function go(location) {
  google.script.run.withSuccessHandler(updateContent).getContent(location, currentContent);
  }
  refresh();
  function refresh() {console.log("refreshing..."); google.script.url.getLocation(go);}
  </script>
  </head>
  <body>
<div>
<p>Loading...</p>
</div>
  </body>
</html>

style.html

<style>
body, div {
background-color: <?= bg ?> !important;
display: flex;
justify-content: center;
align-items: center;
}
</style>