How to convert date column to JSON format for Google Visualization API

Question

Here's how I create the dataTable that comes from a google spreadsheet.

 var params = { 
    valueRenderOption: "UNFORMATTED_VALUE",
    spreadsheetId: '15WX-5oiu54oiu35oi3u4o5o34iu5ou43oiu534',
    range: 'Sheet2!A:H', // Retrieve the values of "A:H".
  };
      var request = gapi.client.sheets.spreadsheets.values.get(params); 
  request.then(function(response) {
         var values = response.result.values.map(function(e) {return [e[0], e[1], e[2], e[3], e[4], e[5], e[6], e[7]]}); // Added
    var w = new google.visualization.ChartWrapper({dataTable: values}); // Added

    var dataTable = w.getDataTable();

     dataTable.setColumnProperty(2, 'role', 'tooltip')
     dataTable.setColumnProperty(3, 'role', 'style')
     dataTable.setColumnProperty(4, 'date', 'Start'); 
     dataTable.setColumnProperty(5, 'date', 'End');
     dataTable.setColumnProperty(2, {type: 'string', role: 'tooltip', p: {html: true}});

 ......

How do I specify the column Property of Column 6 (indexColumn5) as date?

If I create a chartwrapper it fails with the error "Column 6 is not a date".

var chartwraprange = new google.visualization.ControlWrapper({
controlType: 'DateRangeFilter',
containerId: 'controldaterange',
dataTable:dataTable,
options: {
  filterColumnIndex: 6,

   'ui': {
                'label': '',
                 format:{pattern: "MM-dd-yyyy"},
         },    
         }
         });

Answer 1

• You want to retrieve e[5] of var values = response.result.values.map(function(e) {return [e[0], e[1], e[2], e[3], e[4], e[5], e[6], e[7]]}) as the date object.
  • e[5] is the column "F".

If my understanding is correct, how about this modification? In this modification, I modified your script. So please think of this as just one of several solutions.

Modified script:

In this modification, the date values retrieved as the serial number are converted to the unix time and to the date object. Please reflect the following modification to your script.

From:

var values = response.result.values.map(function(e) {return [e[0], e[1], e[2], e[3], e[4], e[5], e[6], e[7]]});

To:

var values = response.result.values.map(function(e) {return [e[0], e[1], e[2], e[4], new Date((e[5] - 25569) * 86400 * 1000), e[6]]});

Reference:

• Excel date to Unix timestamp

If I misunderstand your question, I apologize.