Contents of a spreadsheet are not displayed in the deployed Apps Script webapp

Question

I've tried to do an example of the 3rd tutorial on this ctrlq page, in which we display the content of a spreadsheet on a web page. Here are my files which I simply entered - I didn't activate anything, I just did Publish -> Deploy as web app

I expect to see all of the spreadsheet's values, but instead nothing is shown:

Code.gs

function doGet() {
  var html = HtmlService.createTemplateFromFile("index").evaluate();
  html.setTitle("Dynamic Webpage");
  return html; 
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .getContent();
}

function getData(){
  var sheet = SpreadsheetApp.openById("1k_kj98U__0Bk44gh0qRFpaVx0ru3sN1pSPGiMQwimxo").getSheets()[0];
  return sheet.getDataRange().getValues();
}

index.html

<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<?!= include('script_js'); ?>
<body>
 <div id="data"></div>
</body>
</html>

script_js.html

<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<script>

    window.onload = function(){
      google.script.run.withSuccessHandler(showData).getData();
    }

    function showData(data){
      var html = "";
      for (var i=0; i<data.length; i++) {
        html += "<br>" + data[i].join(":");
      }
      document.getElementById("data").innerHTML = html;
    }

</script>
</body>
</html>

Link to the script project

No need to make snippets if the code does not run in one. Just indent 40 spaces or use the snippet and remove the line beginning with ` — mplungjan, Jul 11 '18 at 12:19
Only certain data types can be returned from the server to the client. Consider returning JSON instead. — tehhowch, Jul 11 '18 at 12:27
Thank you very much for the answers. I've edited the code. So I will be impossible to show my spreadsheet in the web page because withe the example of ctrlq I thought I will be possible. We can use JSON to show the spreadsheet? — ana maria, Jul 11 '18 at 12:32
Possible duplicate of [Building a Gantt chart in Google Sites with Google Sheet data](https://stackoverflow.com/questions/50686187/building-a-gantt-chart-in-google-sites-with-google-sheet-data) — tehhowch, Jul 11 '18 at 12:38
Thank you but it's a simple Google spreadsheet it does not use a database :) — ana maria, Jul 11 '18 at 12:42
@anamaria did you read my answer to that question? Or did you just read the question? — tehhowch, Jul 11 '18 at 16:40
@anamaria since I originally posted that link to the question which solves your issue, I have edited its question to be more clear. Please confirm: is your issue solved by the suggested duplicate? — tehhowch, Jul 12 '18 at 12:00
@tehhowch thank you. No I don't know exactly how to modify it from yesterday I am re-reading the post do I have Date() and I need to use new Array() ? and for JSON I don't see where it's used — ana maria, Jul 12 '18 at 12:20

LS_ · Accepted Answer · 2018-07-12T16:56:14.107

2

EDIT as @tehhowch pointed out his answer is the correct solution here.

For your case you should make these changes, getData():

function getData(){
  var sheet = SpreadsheetApp.openById("1k_kj98U__0Bk44gh0qRFpaVx0ru3sN1pSPGiMQwimxo").getSheets()[0];
  return JSON.stringify(sheet.getDataRange().getValues()); // return value as Json
}

And showData(data):

function showData(data) {
      var arr = JSON.parse(data);
      var html = "";
      for (var i=0; i < arr.length; i++) {
        html += "<br>" + arr[i].join(":");
      }
      document.getElementById("data").innerHTML = html;
    }

edited Jul 12 '18 at 16:56

answered Jul 12 '18 at 13:53

LS_

6,763
9
52
88

2

You can absolutely pass a 2d array via `google.script.run`. However, because OP has `Date` objects in the array, which are explicitly identified as not communicable by documentation, the OPs code fails. See the linked post (of which this question is a duplicate) for relevant links. – tehhowch Jul 12 '18 at 15:01
1

@tehhowch You're right, thanks! I wasn't aware of the `Date` limit – LS_ Jul 12 '18 at 16:57

Contents of a spreadsheet are not displayed in the deployed Apps Script webapp

1 Answers1