How do i get the date value from google spreadsheet database using javascript in google app script

Question

I am having a problem on why its showing nothing when i put date in the record on my google spreadsheet database. Cant figure out why, but when i delete the dates, it shows. I think that javascript cant read the dates from google spreadsheet? I tried to use new Date() but still not working. Pls help me with this. And also when i put like this

<? var data = getData(); ?>
        <table id="tableShift2">
        <caption>Team unknown</caption>
          <th>   Date and Time Plotted   </th>
          <th>   LDAP   </th>
          <th>   Date of VL   </th>
          <th>   HD/WD   </th>
          <th>   Action   </th>
          <? for (var q = 1; q < data.length; q++) {?>
          <tr>
            <td><?=data[q][1];?></td>
          </tr>
          <?}?>
        </table>

it show the dates. So its like when in JS it not reading the date from database. Thats why i need your help guys how it make perfect the codings.

Code.gs

function doGet() {
  return HtmlService
      .createTemplateFromFile('Index')
      .evaluate();

}

function getData() {
  return SpreadsheetApp
      .openById('17lKIhONfEeNogsBhKtGr4zVaLgH0_199-3J3-0tAdcE')
      .getActiveSheet()
      .getDataRange()
      .getValues();
}

Index.html

    <html>
  <head>
    <base target="_top">

  </head>

  <body>
  <? var data = getData(); ?>
  <div id="Options">
    <label><b>Month</b></label>
    <select id="selectMonth">
      <option value="0">-Select Month-</option>
      <option value="1">January</option>       
      <option value="2">February</option>       
      <option value="3">March</option>       
      <option value="4">April</option>       
      <option value="5">May</option>       
      <option value="6">June</option>       
      <option value="7">July</option>       
      <option value="8">August</option>       
      <option value="9">September</option>       
      <option value="10">October</option>       
      <option value="11">November</option>       
      <option value="12">December</option>
    </select> - 
  <input type="radio" name="radioYear" id="radioYear" value="<?= new Date().getYear(); ?>"> <?= new Date().getYear(); ?>
  <input type="radio" name="radioYear" id="radioYear2" value="<?= new Date().getYear()+1; ?>"> <?= new Date().getYear()+1; ?>
  <button id="btnFetch" onclick="google.script.run.withSuccessHandler(fetchRecord).getData()">Fetch</button>
  </div>
  <div  id="tables">
        <table id="tableShift2">
        <caption>Team unknown</caption>
          <th>   Date and Time Plotted   </th>
          <th>   Name   </th>
          <th>   Date of VL   </th>
          <th>   HD/WD   </th>
          <th>   Action   </th>
        </table>
  </div>
  <div id="date"></div>

  <script
src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>
<script>
$(function() {
  google.script.run.withSuccessHandler(showData)
      .getData();
});

function showData(things) {
  var table = $('#tableShift2');
  for (var i = 0; i < things.length; i++) {
    if (things[i][6] == '') {
    table.append('<tr> <td>' + things[i][1] + 
                 '</td> <td>' + things[i][2] +
                 '</td> <td>' + things[i][3] +
                 '</td> <td>' + things[i][4] +
                 '</td> <td ><button onclick=\'ApproveAndRefresh("' + things[i][0] + '","' + things[i][2] +
                 '")\' id="btnApprove">Approve</button> <button onclick=\'DeclineAndRefresh("' + things[i][0] + '","' + things[i][2] + 
                 '")\' id="btnDecline">Decline</button></td></tr>');
    }
  }
}
function ApproveAndRefresh(data, data1){
  google.script.run
  .withSuccessHandler(refreshData)
  .setApproved(data, data1);
}

function DeclineAndRefresh(data, data1){
  google.script.run
  .withSuccessHandler(refreshData)
  .setDeclined(data, data1);
}

function refreshData(hl){
   document.open();
   document.write(hl);
   document.close();
}

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

Could you add some error messages? Check the browser console and the script window > View > Execution Logs — Chris, Feb 20 '18 at 07:39
`Uncaught Error: The script completed but the returned value is not a supported return type.` what does it mean? — MOO, Feb 20 '18 at 07:44

Chris · Accepted Answer · 2018-02-20T08:23:26.923

1

So the data that is returned from .getData() is an Object. Google Apps Script's google.script.run doesn't support that. You can

return JSON.stringify(SpreadsheetApp.openById('17lKIhONfEeNogsBhKtGr4zVaLgH0_199-3J3-0tAdcE')
  .getActiveSheet()
  .getDataRange()
  .getValues())

in your script, then in the SuccessHandler:

function showData(things) {
  things = JSON.parse(things)
  //rest of your code...
}

edited Feb 20 '18 at 08:23

answered Feb 20 '18 at 08:01

Chris

1,827
1
12
23

How? do i need to change to `function getData() { return JSON.stringify(SpreadsheetApp.openById('17lKIhONfEeNogsBhKtGr4zVaLgH0_199-3J3-0tAdcE') .getActiveSheet() .getDataRange() .getValues()) }` ? I dont know how to put it. – MOO Feb 20 '18 at 08:09
That goes in your Code.gs. You make the values into a string then once it is back in the SuccessHandler you parse it back to the Array Object. – Chris Feb 20 '18 at 08:22
It works! thanks on your help. But how i can customize the time and date of it? is there a format? it shows like this `2018-10-10T07:00:00.000Z`. I just need the date and time without the ¨T and Z¨. – MOO Feb 20 '18 at 08:27
Here is a post about [customizing javascript Date object](https://stackoverflow.com/a/3552493/1691677). Be aware you might have to convert the variable containing the date into a Date object: `var time = new Date(things[?][?]); time.getDay() //...etc` – Chris Feb 20 '18 at 08:38
Thanks @Chris W. You´re a big help. – MOO Feb 20 '18 at 08:45
I need help in this part @Chris W. https://stackoverflow.com/questions/48887507/how-to-refresh-tag-in-javascript-using-google-app-script – MOO Feb 20 '18 at 16:30

How do i get the date value from google spreadsheet database using javascript in google app script

1 Answers1