Google Chart API Tables are incredibly useful. The API takes care of pagination and sorting while the categoryFilter and stringFilter in the Controls and Dashboards section takes care of filtering and searching.
This page takes a look at how to add a link to the text in a table cell. Three methods are looked at:
Write the Links Yourself - Tedious and perhaps error-prone method
PatternFormat - Good but with no error checking
Nested Loops - The best if there is empty data
Many data tables such as spreadsheets and Google Charts can contain two pieces of information. One is the value and the other is the instructions on how to format that value. This is how the various date, time and number formats can be used. Operations such as filtering and sorting are made on the underlying values not the formatting. This is why the first "DIY" method described here cannot be sorted properly; this method changes the value of the data not the formatting. The other two methods change the formatting and so sort properly.
Google Charts contains several methods of changing the values (v) and formatting (f) of the data cells. These are explained in the datatable methods documentation. Specifically setCell, setFormattedValue, and setValue.
The table below is made from a Google Sheet. There a few things to notice. If a cell contains an URL, as in the sheet itself or another like Excel, the URL is not automatically turned into a link. Another thing is that if the cell contains a properly formed link, as in the last column, it displays as a link, if allowHtml:true is added to the datatable options in the code.
The code to produce the above table:
<script type="text/javascript">
var basicTable = (function() {
google.charts.load('current', {'packages':['table']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var queryString = encodeURIComponent('SELECT A,B,C,D,E ORDER BY A');
var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=234750873&headers=1&tq=' + queryString);
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var options = {allowHtml:true};
var originalData = response.getDataTable();
var chart = new google.visualization.Table(document.getElementById('originalData_div'));
chart.draw(originalData, options);
}
})();
</script>
Just because all the columns in the Google Sheet were imported in the above example does not mean they all need to be used in the output, in that particular order, or even with the same column header. The column labels can be changed in the imported data table using setColumnLabel. To change the order of the columns a dataView must be created and the visibility/order of the columns can be changed, in this case using setColumns
The code for the above table is now:
<script type="text/javascript">
var orderColumns = (function() {
google.charts.load('current', {'packages':['table']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var queryString = encodeURIComponent('SELECT B,D,E ORDER BY A');
var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=234750873&headers=1&tq=' + queryString);
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var options = {allowHtml:true};
var reorderedData = response.getDataTable();
reorderedData.setColumnLabel(2, 'Artist');
var reorderedView = new google.visualization.DataView(reorderedData);
reorderedView.setColumns([2, 0, 1]);
var chart = new google.visualization.Table(document.getElementById('reorderedData_div'));
chart.draw(reorderedView, options);
}
})();
</script>
Of course, the columns could have been imported in any order you like by changing the order in the query that imported them. In this case changing 'SELECT B,D,E ORDER BY A' to 'SELECT E,B,D ORDER BY A'
Gotchas
When importing the columns the query follows the lettering of the columns in the Google Sheet (A, B, C etc.) but when specifying the columns by number they start from 0 (0, 1, 2 etc.)
There is another problem using this method. When the columns are sorted by the new 'Artist' label, they do not sort in the correct order. This is because the URLs created using this method are used to sort on not just the link text.
But why go through the trouble having to make every single link in the spreadsheet when the text needed and the URL is already there? This type of tedious work is what computers are for!
Tables have a number of formatters available, and one of them is the PatternFormat. This formatter is able to merge the values of designated columns into a single column, along with arbitrary text.
The code for the above table is now:
<script type="text/javascript">
var patternTable = (function() {
google.charts.load('current', {'packages':['table']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var queryString = encodeURIComponent('SELECT A,B,C,D ORDER BY A');
var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=234750873&headers=1&tq=' + queryString);
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var options = {allowHtml:true};
var patternData = response.getDataTable();
// Create the links using the formatter
var formaturl = new google.visualization.PatternFormat('{0}');
formaturl.format(patternData, [0,1,2],0);
var formaturl = new google.visualization.PatternFormat('{1}');
formaturl.format(patternData, [0,1,2,3],1);
var patternView = new google.visualization.DataView(patternData);
patternView.setColumns([0, 1]);
var chart = new google.visualization.Table(document.getElementById('patternData_div'));
chart.draw(patternView, options);
}
})();
</script>
Gotchas
The PatternFormat method works best if there is data in every cell used by the formatter. No error checking is done, not even to check if any of the cells used are empty. This can result in the links not being made properly.
There seems to be a problem with the .format documentation. The documentation says about applying the formatter that:
srcColumnIndices - An array of one or more (zero-based) column indices to pull as the sources from the underlying DataTable. This will be used as a data source for the pattern parameter in the constructor. The column numbers do not have to be in sorted order.
But the only way I could get it to work was create the arry of columns starting from the first (0) to the last used by the formatter. This is in the lines formaturl.format(patternData, [0,1,2],0); and formaturl.format(patternData, [0,1,2,3],1);
Data tables are simply two dimensional grids of data cells. The columns and rows can be stepped through and Google Charts has methods to both read (get) and write (set) the data value, formatting or both of individual cells.
The code for the above table is now:
<script type="text/javascript">
var nestedLoops = (function() {
google.charts.load('current', {'packages':['table']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var queryString = encodeURIComponent('SELECT A,B,C,D ORDER BY A');
var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=234750873&headers=1&tq=' + queryString);
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var options = {allowHtml:true};
var loopData = response.getDataTable();
var totalRows = loopData.getNumberOfRows();
for (i = 0; i < totalRows; i++) {
// For each row get the link text for the artist (index 2) and song (index 3) then
// create the link in the artist column (index 0) and song column (index 1)
var linkText = loopData.getValue(i, 2);
if (linkText !=null && linkText !="") {
txt = loopData.getValue(i, 0);
newURL = '<a href="'+linkText+'" target="_blank">'+txt+'</a>';
loopData.setFormattedValue(i, 0, newURL);
}
var linkText = loopData.getValue(i, 3);
if (linkText !=null && linkText !="") {
txt = loopData.getValue(i, 1);
newURL = '<a href="'+linkText+'" target="_blank">'+txt+'</a>';
loopData.setFormattedValue(i, 1, newURL);
}
}
var loopView = new google.visualization.DataView(loopData);
loopView.setColumns([0, 1]);
var chart = new google.visualization.Table(document.getElementById('loopedData_div'));
chart.draw(loopView, options);
}
})();
</script>
Google Charts contains several methods of changing the values (v) and formatting (f) of the data cells. These are explained in the datatable methods documentation. Specifically setCell, setFormattedValue, and setValue. In the above code setFormattedValue is used.
Gotchas
There are no real gotchas with this method but some knowledge of the Google Chart methods and JavaScript is required. An advantage of using this method is that there is full control of the data.