Google Charts API

Get & Set Data in a Datatable


No matter where the data is imported from it is going to be visualized from a datatable, this is a two-dimensional object. It can be thought of something like a spreadsheet. The origin of the datatable is the top left at (0,0) and each row and column numbered numerically after that. Each "cell" has its own value and properties. The values and properties can be accessed using the get and set methods found in the datatable methods documentation.

This all means each "cell" can be accessed in a JavaScript nested loop. The outer loop steps through the rows and the inner can go through the columns. Of course, if you prefer, you can go though the columns then the rows but I just prefer doing the rows first.

Note: There are several formatters available but these do no error checking all, not even for empty cells, so it is sometimes easier to use nested loops to step through them all.

Example: Long Text

This example was prompted by idan in the Google Visualization API Group who wanted to limit the number of characters in a cell. The data comes from a Google Sheet

The code goes through the data cells checking the length of the contents of each one and shortens them if needed. Only the values are changed so .getValue and .setValue are used.

The code for the above table is:

    <script type="text/javascript">
	var longText = (function() {
    google.charts.load('current', {'packages':['table']});
    function drawChart() {
		var queryString = encodeURIComponent('SELECT A,B,C ORDER BY A');
		var query = new google.visualization.Query('' + queryString);

    function handleQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
      var quotes = response.getDataTable();
	  var totalRows = quotes.getNumberOfRows();
	  var totalCols = quotes.getNumberOfColumns();
	  var maxLength = 50;

	  for (i = 0; i < totalRows; i++) { 
	     for (j = 1; j < totalCols; j++) {
	     var quote = quotes.getValue(i, j);
	     if (quote.length > = maxLength) {
	  	    quote = quote.slice(0,maxLength) + "...";
      var chart = new google.visualization.Table(document.getElementById('quotes_div'));
This page created March 4, 2021; last modified March 4, 2021