Date format to JavaScript JSON when using Google Sheets API in a chart

199 views
Skip to first unread message

heath hEATH

unread,
Apr 20, 2019, 7:35:03 PM4/20/19
to Google Visualization API
Can't believe how many questions I've read about date format and the stubbornness of the visualization API to read a date correctly.

I feed the data from a google sheet and present it in a chart and a timeline. I keep dates in column 5 and 6 and when I try to paint a DateRangeFilter, it fails with "Column 6 is not a date".

When I look at the console logs I see those columns listed as a date in this format "43528" and it sounds like the chart prefers it in JavaScript JSON format "Date(2019,1,1)". but how can I convert them to the right format in the dataTable? Can I do anything in the spreadsheet itself? 



 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}});

 
......

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

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


Reply all
Reply to author
Forward
0 new messages