Google Charts API

Tables: Using Dates

Introduction

Suppose you have a schedule of events, but you don't want it to show events that have already passed. This page investigates how to do it.

Here is the table produced from a Google Sheet. The date/time formatting comes from the sheet.

The code for the above table is:


    <script type="text/javascript">
	var schedule = (function() {
    google.charts.load('current', {'packages':['table']});
    google.charts.setOnLoadCallback(drawChart);
	
    function drawChart() {
		var queryString = encodeURIComponent('SELECT A,B,C,D ORDER BY B,C');
		var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=1144900715&headers=1&tq=' + queryString);
      query.send(handleQueryResponse);
    }

    function handleQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }

      var scheduleData = response.getDataTable();
      var chart = new google.visualization.Table(document.getElementById('schedule_div'));
      chart.draw(scheduleData);    
	}
	})();
	</script>

Hiding Past Events

The first three dates are before today and we do not want to show them. There are several methods of filtering by date. One of the easiest is not to import them at all by specifying only later dates in the queryString. The general format for dates in the queryString is yyyy-mm-dd, such as:

var queryString = encodeURIComponent('SELECT A,B,C,D WHERE B > date "2021-01-17" ');

Method 1

The problem is that what is needed is today's date. One way is to use the following to get today's date and reformat it to the its yyyy-mm-dd form using:


  var currentDate = new Date();
  var textDate = currentDate.getFullYear() + "-" + currentDate.getMonth() + 1 + "-" + currentDate.getDate();
  var queryString = encodeURIComponent('SELECT A,B,C,D WHERE B >= date "' + textDate + '" ORDER BY B,C');

Method 2

Another method is to use the toDate function to extract the date in the correct format from the NOW() function in the queryString:

var queryString = encodeURIComponent('SELECT A,B,C,D WHERE B >= toDate(NOW()) ORDER BY B,C');

Method 3

A third method is to convert today's date using toISOString(). This can be written as:


var dateNow = new Date();
dateNow = dateNow.toISOString().slice(0, 10);
dateNow = 'date "' + dateNow + '"';
var queryString = encodeURIComponent('SELECT A,B,C,D WHERE B >= date "' + dateNow + '" ORDER BY B,C');

The first three lines can be written as one and so the code now becomes:


var dateNow = 'date "' + new Date().toISOString().slice(0, 10) + '"';
var queryString = encodeURIComponent('SELECT A,B,C,D WHERE B >= date "' + dateNow + '" ORDER BY B,C');

Date/Time Alignment

I also prefer the dates and times to be left aligned rather than the centered default. In order to make this happen three things must be done:

cssClassNames must be enabled in the table options
The variable defined in cssClassNames must be populated with which parts of the table to be styled along with the CSS class name
Finally, the style tag must be populated with CSS class name and the CSS styling to be applied to it.

The CSS for the above table is:


   <style>
	  .cellformat { text-align: left;}
   </style>

The JavaScript for the above table is:

 
    <script type="text/javascript">
	var reformatted = (function() {
    google.charts.load('current', {'packages':['table']});
    google.charts.setOnLoadCallback(drawChart);
	
    function drawChart() {
		var queryString = encodeURIComponent('SELECT A,B,C,D WHERE B >= toDate(NOW()) ORDER BY B,C');
		var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=1144900715&headers=1&tq=' + queryString);
    	query.send(handleQueryResponse);
    }

    function handleQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }

     var scheduleData = response.getDataTable(); 
		
	 var cssClassNames = { 'tableCell': 'cellformat' };
		
	 var options = {'cssClassNames': cssClassNames};
		
     var chart = new google.visualization.Table(document.getElementById('reformatted_div'));
     chart.draw(scheduleData, options);    
	}
	})();
	</script>

Filtering by Date

Another method of excluding past events is to inport the entire sheet then filter the data table and create a data view based on the date in the date column.

In this example I want the current date's events displayed, even if the event occurred in the morning and table is viewed later in the day.

In the above example a new date object var todaysDate = new Date(); is created then one days subtracted from that using todaysDate.setDate( todaysDate.getDate() - 1 ); and that used to stop events earlier in the day from not showing. If this is not needed then this part of the code is not needed and {column: 1, minValue: new Date()} used in the filtering.

The JavaScript for the above table is:

 
    <script type="text/javascript">
	var filtered = (function() {
    google.charts.load('current', {'packages':['table']});
    google.charts.setOnLoadCallback(drawChart);
	
    function drawChart() {
		var queryString = encodeURIComponent('SELECT A,B,C,D ORDER BY B,C');
		var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=1144900715&headers=1&tq=' + queryString);
    	query.send(handleQueryResponse);
    }

    function handleQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }

     var originalData = response.getDataTable(); 
		
	 var cssClassNames = { 'tableCell': 'cellformat' };
		
	 var options = {'cssClassNames': cssClassNames};
		
	 var filteredView = new google.visualization.DataView(originalData);
		
	 var todaysDate = new Date();
     todaysDate.setDate( todaysDate.getDate() - 1 );
     
	 filteredView.setRows(originalData.getFilteredRows([
	 {column: 1, minValue: todaysDate}
     ]));
		
     var chart = new google.visualization.Table(document.getElementById('filtered_div'));
     chart.draw(filteredView, options);    
	}
	})();
	</script>

More Information

Date formatting, manipulation and calculation are not the easiest to understand or get correct. The following pages may be able to help.

DataTable & Dataviews - Google Charts API - Methods and events
DateFormat - Google Charts API
Dates and Times - Google Charts API
Miscellaneous Code Examples - Google Charts API - How to format table charts
Scalar Functions - Google Query Language API
Table Charts - Google Charts API - Table charts specific page