Import sheet data into an include file using Google Apps Scriptlets in Google Sheets

0
votes

In Google Sheets, I have a layout created with HtmlService of Google Apps Script (see masterPage.html, below). 

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <? var serverData = getServerData(); ?>
</head>

<body>
  <h2>Page begin.</h2>
  <p>Data A from server: <?= serverData[0] ?></p>
  <?!= HtmlService.createHtmlOutputFromFile('includedPage').getContent(); ?>
  <p>Page end.</p>
</body>
</html>

This layout calls HTML from a second layout called includedPage.html. 

<? var serverData = getServerData(); ?>
<div>
  <p>Data B from server: <?= serverData[1] ?></p>
</div>

I can use a G.A.S. scriptlet to get data ("Data A from server") from the server/sheet and into the master page.

However, the same scriptlet does not work when placed in the included page. The code simply renders as HTML and "Data B from server" is not displayed.

Is there a way to import server/sheet into the include page before the include page gets inserted into the master page?

Any assistance appreciated.

Note: the following questions are related to but do not answer my question:

1
javascriptgoogle-apps-scriptgoogle-sheets
Does this answer your question? How to use scriptlets in HTMLOutput in Google Apps Script – TheMaster
Thanks @TheMaster, that page is closely related to, though not exactly the same as, my issue. – Quillmondo

1 Answers

1
votes

The Apps Script scriptlets are being evaluated only once, at the beginning of script execution

In your case it makes more sense to use google.script.run that allows calling an Apps Script function from the client side (html file)

Sample:

includedPage

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div id="data"></div>
    <script>
    google.script.run.withSuccessHandler(updateDiv).withUserObject(this).getServerData();
    function updateDiv(returnValue){
      document.getElementById("data").innerHTML="<p>"+ returnValue +"</p>"
    }
    </script>
  </body>
</html>

Code.gs

function getServerData() {
  var serverData = [1, 2, 3, 4, 5];
  return serverData[1];
}