Retrieve cell date from a google spreadsheet to HT

2019-09-08 09:57发布

问题:

I have a google apps script which serves HTML. It retrieves data from a google spreadsheet and renders it as a html table.

It works well except when there is a date on the cell, I get the following error on the JS console:

Uncaught ScriptError: Se ha ejecutado la secuencia de comandos, pero el tipo de valor que se muestra es incompatible.

It is in Spanish, but means: the cmd sequence was executed, but the type of data showed is not compatible.

HTML

<script type="text/javascript">
$(document).ready(function(){
$("#tabs").tabs();
google.script.run.withSuccessHandler(fillTabs).getAllTabsData();

}); 

</script>

I think the problem is in this function, because it fails in every script that I implement it:

function fillTabs(tabs) {
/*Tabs debe ser un array de objetos. cada objeto debe tener, name refiriendose a la pestaña y un array bidimensional con los datos de esa pestaña [rows][columns]*/
  var tabsDiv;//=$('#tabs');
  var table;
  var data;
  console.log("=Array tabs: "+tabs);
  for (var i = 0; i < tabs.length; i++) {

    data=tabs[i].data;
    var tabName=tabs[i].name;
    console.log("==Procesando: "+tabName);
    tabsDiv=$('#'+tabName);
    table='<table class="mytable"><tr>'    

    //iterando columnas primera fila (HEADERS)
    for(var x=0; x<data[0].length;x++){
        table+='<th>'+data[0][x]+'</th>'
    }
    table+=' <th width="20%">Action</th></tr>'; //cabeceras fin

    for( var j=1; j<data.length; j++){//iterando rows. Empezamos en la 1 porque 0=headers
      table+='<tr id="'+j+'">'
      for(var k=0; k<data[j].length;k++){//iterando columns
        table+='<td>'+data[j][k]+'</td>'
      }
      table+='<td></td></tr>'
    }
    table+='</table/>'

    tabsDiv.html(table);
  }
  $("tr:odd").addClass("odd");

};

GS

function getAllTabsData(){
  var tabNames = getColumnData('Types'); 
  var sSheet =SpreadsheetApp.openById(key);
  var tabsObject=[];

  for(var i=0; i<tabNames.length;i++){
    tabsObject.push(
      { name:tabNames[i],
       data:sSheet.getSheetByName(tabNames[i]).getDataRange().getValues()});

  }
Logger.log(tabsObject);
  return tabsObject;
}

I don't get any error on the GS part, just in the HTML. All the JavaScript code after that is not being executed.

I checked other similar script and it works with dates. You can see it here https://script.google.com/macros/s/AKfycbwr9wDJibSknCy4thjj6Hedz8H9NOq2Ren6NLqBUhf6nzVYquFL/exec

The only difference is that does not returns an array of objects (like mine does), it only returns a single array.

回答1:

I don't understand why this does not work "by deafault", but as I suspected, JSON did the JOB

Adding JSON.stringify on the server side and

JSON && JSON.parse(json) || $.parseJSON(json);

on the client side worked.

I copied that line of code. I understand the part after the ||, but I don't understand why to use JSON && JSON.parse. I suppose is to avoid trying JSON.parse if JSON is not available.