Como faço para incorporar uma planilha do Planilhas Google realmente ativa em uma página da web?

Torben E Publicado em Dev

Torben E

O que eu quero

Um site que exibe a planilha realmente ativa (atualizando instantaneamente quando a planilha é alterada de outro lugar, como no editor), mas centralizado na tela e sem menus etc. (como em 2b)

Especificamente um site que

mostra uma folha de uma planilha do Planilhas Google, formatada corretamente
atualiza a planilha ao vivo sem nenhuma entrada do usuário uma vez por segundo
não contém cabeçalhos de edição do Planilhas Google
centraliza o conteúdo na página e tem uma borda preta para preencher a tela fora da planilha

O que eu sei

Depois de muitas pesquisas no Google, encontrei dois resultados alinhando meu objetivo:

1. Editor de planilhas do Google sem 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

Torben E

É possível fazer isso armazenando em cache a resposta da função fetch e apenas atualizando a página se ela tiver sido alterada, como @TheMaster sugeriu. Eu também adicionei uma função hash simples deste post e usei uma expressão regular para proteger um pouco o código contra injeção de HTML.

O código a seguir irá atualizar a página assim que a última atualização for concluída (aproximadamente a cada segundo). Isso ainda é mais lento do que no editor, então você pode querer usar a solução 1 na questão original.

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};
}

(Anexe também este código )

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>

Este artigo é coletado da Internet.

Se houver alguma infração, entre em [email protected] Delete.

editar em2021-01-24

deixe-me dizer algumas palavras

0comentários

loginDepois de participar da revisão

Postagem anterior:空のオブジェクトを返すjson2typescriptserializeObjectメソッド

TOP lista

artigo