Google Charts API

Timelines

Introduction

I have a list of entities with the dates they were active. The list is in a Google Sheet and I want to display them in a Google Visualization Timeline. I do not have full dates, just years, for most of them. Some are still active but some ended years ago.

The Basic Timeline

Using just the basic information from the Google Sheet, the API draws a chart but, not surprsingly there are problems with it. The years are interpreted as seconds in the chart as well as the tooltips and the formatting needs some attention. The API documentation includes information on how to import data from Google Spreadsheets. It also includes a section on the Google Visualization API Query Language which explains how to select the needed data.

Webring systems 1995 to 2021

The code to produce the above timeline:


    <script type="text/javascript">
    var basicTimeline = (function() {
    google.charts.load('current', {'packages':['timeline']});
    google.charts.setOnLoadCallback(drawChart);
	
    function drawChart() {
		var queryString = encodeURIComponent('SELECT A,B,C ORDER BY B OFFSET 1');
		var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1dEYsZdPV_MbAL_WKTNb8n75jmh-ThaFJR8krzzZk83s/gviz/tq?gid=694494516&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 chart = new google.visualization.Timeline(document.getElementById('originalData_div'));
    chart.draw(originalData);
    }    
	})();
	</script>

Creating a New Data Table

It is possible to change the Google Sheet or other data source to include all the information needed to create a chart. Timelines are a little different because the columns need specifc data types and roles as well as being in a specific order.

A disadvantage of changing the spreadsheet is that there are then two entities to keep updated, the spreadsheet and the JavaScript code and therefore two sources of possible errors.

For the above reasons I decided to create a new DataTable and populate it from the original. This is done by using nested loops to go through each column in each row, getting the value from the original datatable and rewritting it in a new format to the new one.

As I only have the year for the entities, I want a start date of January 1 and an end date of December 31 for all of them. Remember JavaScript months start from 0 but not the days! Incidentally the days of the week start from 0 as well, they run 0 to 6.

Formatting the Timeline

From the original Timeline, it would be nice for more rows to be shown at once. This can be done by increasing the height of the chart and by decreasing the height of the bars by decreasing the fontsize used to draw them.

The tooltips need to reformatted at HTML as I want them to be different from the standard ones.

It would have been nice to have the bottom hAxis have every year or at least every other year marked, but as far as I know it is not possible to format those yourself in Timelines.