Display Google Sheet Data in HTML Page

Bill East Source

I have a Google Sheet with values in cells A1 and A2. An index.html file was also created in this sheet. I want to display information from the Google Sheet in the html file. Here is what I have:

Code.gs:

function myFunction() {
  var html = HtmlService.createHtmlOutputFromFile('index').setSandboxMode(HtmlService.SandboxMode.IFRAME);
  SpreadsheetApp.getUi().showModalDialog(html, 'Display Sheet Data');
}

index.html:

<!DOCTYPE html>
<html>

<head>
  <script type="text/javascript">
    function setPageValues () {

      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var myFirstValue  = ss.getRange(1, 1).getValue();
      var mySecondValue = ss.getRange(2, 1).getValue();
      document.getElementById("first").innerHTML = myFirstValue;
      document.getElementById("second").innerHTML = mySecondValue;

      }
  </script>
</head>

<body onload="setPageValues()">
  <br>data from cell A:1 = <span id="first"></span>
  <br>data from cell A:2 = <span id="second"></span>
</body>

</html>

When I run the myFunction from the Google Sheet script editor, the window containing the index.html page displays correctly. The sheet values do not.

I feel stupid about asking something that seems like it should be so basic but ... "Everybody's a noob at something"! Thanks in advance!

google-apps-scriptgoogle-spreadsheet

Answers

answered 11 months ago Tanaike #1

How about a following modified script? In order to retrieve and send data with Google Apps Script, you can use google.script.run.

Code.gs:

function myFunction() {
  var html = HtmlService.createHtmlOutputFromFile('index').setSandboxMode(HtmlService.SandboxMode.IFRAME);
  SpreadsheetApp.getUi().showModalDialog(html, 'Display Sheet Data');
}

function getValuesFromSS(range) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  return ss.getRange(range).getValues();
}

index.html:

<!DOCTYPE html>
<html>

<head>
  <script type="text/javascript">
    function setPageValues () {
      google.script.run.withSuccessHandler(disp).getValuesFromSS("A1:A2");
    }

    function disp(values){
      document.getElementById("first").innerHTML = values[0][0];
      document.getElementById("second").innerHTML = values[1][0];
    }
  </script>
</head>

<body onload="setPageValues()">
  <br>data from cell A:1 = <span id="first"></span>
  <br>data from cell A:2 = <span id="second"></span>
</body>

</html>

If I misunderstand your question, I'm sorry.

comments powered by Disqus