Google Charts API

Introduction

I use Google Sheets and put the data held in them on my websites using the Google Visualizations API, also known as the Google Cahrts API.

To access the charts on a webpage, the API uses the Charts Query Language.

This page was written to provide a little additional help using the Query Language.

Referencing a Single Cell

See this Google Sheet for examples.

In a Google Sheet, the contents of a single cell can be referenced in the Query Language by using:

"&C1&"

Where C1 is the cell whose contents you want. An example from the Sheet is:

=QUERY(A:A, "select * where Month(A) = "&C1&"")

The above query goes through a list of dates in column A and lists those where the month is the contents of cell C1 which is 5.

The referenced cell can also contain any of the common mathematical or comparison operators (+. -, *, x, /, ^, =, <, >, <>) and so on. These operators will be acted upon in the query. Another example from the Sheet is:

=QUERY(A:A, "select * where Month(A) "&E1&"")

Where the contents of cell E1 is <7. Notice there is now no need for the = sign in the query.

If the cell centents contains a space or some punctuation marks such as !, then the reference to it in the query must be enclosed in single quotes to prevent an error. Another example from the Sheet is:

=QUERY(G:G, "select * where G = '"&G1&"'")

Unfortunately this type of query appears to only work inside a Sheet. I cannot find a method of getting a single cell refernce to work in a query from a web page.

References

Google Sheet
How to Reference a Cell in Query in Google Sheets
Simple Date Query

Selecting Dates

Date formatting, manipulation and calculation are not the easiest to understand or get correct. There are time zones, daylight savings, GMT (Greenwich Mean Time), and UTC (Coordinated Universal Time) to be aware of.

Even getting the day and month from a date may be tricky. For example, JaveScript the getMonth() method from a date returns a number from 0 to 11, with 0 being January, 1 being February and so on. The getDay() method from a date returns a number from 0 to 6, with 0 being Sunday, 1 being Monday and so on. The same applies when using the Query Language month() function, but the Query Language's quarter() and dayOfWeek() functions are 1-based.

This example uses this Google Sheet.

In the spreadsheet there is column A is made of dates but I only want to display the dates that are in June. The Query Language month() function is 0-based, so January is 0, February is 1 and so on. This means that June is 5.

The code to do this 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 where Month(A) = 5');
		var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1NTGuA7DA5-GVGbvT-aEYMIYmEl358NUjxExDFDVDq-8/gviz/tq?gid=0&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('juneDates-div'));
      chart.draw(scheduleData);    
	}
	})();
  </script>

The important bit in the query is:

('select A where Month(A) = 5')

Sources and Resources

Charts Query Language
Google Sheets
Google Visualizations API
Ingest Date from Google Sheets - Google Visualizations API