Alternative to arrayToDataTable for date column

Question

I'm new to stackexchange so my apologies if this question is too extensive or already answered somewhere I couldn't find. You can find the spreadsheet here, the script here and the dashboard (dev version) here.

I have been banging my head on handling dates in the google app script visualization for days.

My ultimate goal is to make a dashboard that includes an annotated timeline as well as other charts based on a data set in a spreadsheet. I have started this process using Mogsdad tutorial on creating a 3-tier google visualization dashboard, where the data is pulled from external spreadsheet and then pulled into the DataTable using arrayToDataTable. Everything worked great out of the box. However, my data contains dates, so I added a date column to the original data, but alas arrayToDataTable doesn't accept date type per this post. So when a Date column is added i get the following result:

ScriptError: The script completed but the returned value is not a supported return type.

I have tried multiple approaches to ensure even date formatting: options includes putting the values in the date column through new Date(dateColumn[i]), dateColumn[i].toJSON() (renders the dash board, but dates aren't able to be processed), forced date formats in the spreadsheet (yyyy-MM-dd), using the DataView outlined in the post above (dashboards don't get past 'Loading'), and such.

So my question is what is the alternatives to arrayToDataTable that will accept date columns in this 3-tier approach? Or alternatively, what are the errors in the below methods?

For all the cases when I have attempted to add columns I have changed the code from var data = google.visualization.arrayToDataTable(response,false) to var data = google.visualization.DataTable()

I have tried the following:

1. Manually adding columns and manually adding data (not working)

   //Add Columns 
   data.addColumn('string','Name');
   data.addColumn('string','Gender');
   data.addColumn('number','Age');
   data.addColumn('number','Donuts eaten');
   data.addColumn('date','Last Donut Eaten');
   
    //Add Rows
    data.addRows([
      ['Miranda','Female', 22,6,6],
      ['Jessica','Female',22,6,12],
      ['Aaron','Male',3,1,13]
    ]);
   

2. Automatically adding the rows without dates (The rows are added, but it only works if there are no date columns)

    //Add Rows
    for (var i=1; i<response.length; i++) {
      data.addRow(response[i]);
    }
   

3. Manually adding columns and automatically adding rows (not working, combination of 1 and 2)

4. Automatically adding the columns with loops (not working, neither if dates or not)

   for (var i=0; i<response[0].length; i++) {
     if (response[1][i] instanceof Date) {  //Checks if first value is Date
       data.addColumn('date',response[0][i]);
     };
     else if (response[1][i] instanceof Number)  //Checks if first value is Number
       data.addColum('number',response[0][i]);
     else data.addColumn('string',response[0][i]; //Otherwise assume string
     }; 
   

Thank you so much for your help!

Answer 1

you can use the Query (google.visualization.Query) class to pull the data from the spreadsheet,
this will convert the date column properly...

google.charts.load('current', {
  packages:['table']
}).then(function () {
  var queryURL = 'https://docs.google.com/spreadsheets/d/1aaxYNLCuPz3o3TA1jdryenUP01Qbkdaut4AR5eIhe9s/edit#gid=0';
  var query = new google.visualization.Query(queryURL).send(function (response) {
    var data = response.getDataTable();

    // show column types
    for (var i = 0; i < data.getNumberOfColumns(); i++) {
      console.log(data.getColumnLabel(i), '=', data.getColumnType(i));
    }

    // draw table chart
    var table = new google.visualization.Table(document.getElementById('chart-table'));
    table.draw(data);
  });
});

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart-table"></div>

---

note: the example uses jsapi to load the library,
this library should no longer be used.
according to the release notes...

The version of Google Charts that remains available via the jsapi loader is no longer being updated consistently. Please use the new gstatic loader.js from now on.

this will only change the load statement, see above snippet...