7

I am trying to return the data in the spreadsheet to an Apps Script published as a webApp. However, for one of my spreadsheets (link), the returned data is null. I logged the the data before returning to make sure that it isn't null, and I can see them in the logs.

This is my code:

function doGet(){
  return HtmlService.createHtmlOutputFromFile('index');
}

function spreadsheetTest() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var maxRows = sheet.getMaxRows();
  var data = sheet.getSheetValues(1, 1, maxRows, 10);
  Logger.log(data)
  return data;
}
<html>
<button type="button" onclick="clcked();">Click Me!</button>
<div id="Message"></div>

<script>
  function clcked(){
    google.script.run
      .withSuccessHandler(function(response) {
        console.log(response);
      })
      .spreadsheetTest();
  }
</script>
</html>

Any help would be highly appreciated. :)

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
Himal Samarasekara
  • 73
  • 1
  • 3
  • You should be more specific about the log results - you log both in the server function and in the client function. Which is `null`? Both? Just the client? What is being logged? (You should include these example logs in the question). – tehhowch May 22 '18 at 00:36
  • 2
    Note: this behavior (value -> `null` for some types, such as `Date`) is explicitly identified in documentation for the client-server communication: https://developers.google.com/apps-script/guides/html/reference/run#myFunction(...) – tehhowch May 22 '18 at 00:39
  • Thanks tehhowch. I will take your suggestions for my future posts and thank you for editing my post to make it more readable. Much appreciated :) – Himal Samarasekara May 22 '18 at 03:20

1 Answers1

20

I have ever experienced the same issue before. When the values with the date format are included in data which is retrieved by getSheetValues(), the returned values become null. I thought that the issue might occur when the date values are parsed and/or converted, when the values are sent from GAS to Javascript. By this, null is returned. In order to avoid this, I think that there are 2 patterns for your situation. Please chose one of them for your situation.

Pattern 1

For the function spreadsheetTest(), modify as follows.

From : 
return data;
To : 
return JSON.stringify(data);

Pattern 2

For the function spreadsheetTest(), modify as follows.

From : 
var data = sheet.getSheetValues(1, 1, maxRows, 10);
To : 
var data = sheet.getRange(1, 1, maxRows, 10).getDisplayValues();

If these are not useful for your situation, I'm sorry.

Tanaike
  • 181,128
  • 11
  • 97
  • 165