In a previous post (Using Google Spreadsheet as a database for your site) it was shown that a Google Spreadsheet could function as a database and that it was possible to make queries as opposed to just publishing the entire content into one very long table (in a case where the table contains lots of data).
But in my desire to give more functionality (like searching a certain text, limiting to 10 result per page and a navigation button for the next or previous 10 results) to the page, I thought it would be much simpler to do the query in Javascript rather than to do it in the data source URL. Fortunately I found one in Google code playground.
Here is how
By using the setquery method to put your query string within the Javascript code. Here is what it would basically look like:
var query = new google.visualization.Query(DATA_SOURCE_URL);
query.setQuery('select A,B,C group by A order by B desc');
query.send(handleQueryResponse);
query.setQuery('select A,B,C group by A order by B desc');
query.send(handleQueryResponse);
With the query in Javascript it turned my display result page from this:
into this:
The Javascript code for this is found just below. Feel free to use it. Just copy and paste the code into your page replacing the source spreadsheet and the query string as best fits your requirement.
<style>
.post h3 {display:none !important;}
</style>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>
Google Visualization API Sample
</title>
<script src="http://www.google.com/jsapi" type="text/javascript"></script>
<script type="text/javascript">
google.load('visualization', '1', {packages: ['table']});
</script>
<script type="text/javascript">
var isFirstTime = true;
var data;
var queryInput;
var credits = '<br /><p style="font-size: 8px;">Courtesy of <a href="http://cyberliving.blogspot.com">http://cyberliving.blogspot.com</a></p>';
var query = new google.visualization.Query(
'https://spreadsheets.google.com/tq?key=YOUR_SPREADSHEET_ID_HERE');
function sendAndDraw() {
// Send the query with a callback function.
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
data = response.getDataTable();
var table = new google.visualization.Table(document.getElementById('querytable'));
table.draw(data, {'allowHtml': true, 'alternatingRowStyle': true, 'page': 'enable', 'pageSize': 10, 'sort': 'enable', 'sortAscending': false, 'sortColumn': 0});
if (isFirstTime) {
init();
}
}
function setQuery(queryString) {
// Query language examples configured with the UI
query.setQuery(queryString);
sendAndDraw();
queryInput.value = queryString;
}
google.setOnLoadCallback(sendAndDraw);
function init() {
isFirstTime = false;
queryInput = document.getElementById('display-query');
}
function setQueryFromUser() {
var queryInput1 = "Select * where C contains " + "'" + queryInput.value + "'" + " or D contains " + "'" + queryInput.value + "'" + " or E contains " + "'" + queryInput.value + "'" + " or F contains " + "'" + queryInput.value + "'" + " or G contains " + "'" + queryInput.value + "'"
setQuery(queryInput1);
}
</script>
</head>
<body style="border: 0 none; font-family: Arial;">
<div style="background-color: buttonface; border: 1px solid gray; margin-bottom: 10px; padding: 5px;">
<span> Refine your search</span>
<form action="">
<table style="font-size: 12px;">
<tr>
<td>Select Category</td>
<td><select id="query-1" onchange="setQuery(this.value)">
<option value="">None</option>
<option value="where B = 'Real Estate'">Real Estate</option>
<option value="where B = 'Automobile/Vehicle'">Automobile/Vehicle</option>
<option value="where B = 'Computers/Electronics'">Computers/Consumer Electronics</option>
<option value="where B = 'Job Opportunity'">Job Opportunity</option>
<option value="where B = 'Others'">Others</option>
</select></td>
<td>Search iSari-Sari Store</td>
<td><input id="display-query" type="text" />
<input onclick="setQueryFromUser()" type="button" value="Search" />
</td>
</tr>
</table>
</form>
</div>
<br />
<div id="querytable">Loading...</div>
<script>document.write(credits);</script>
</body></html>
The following things you need to do after pasting the code in your page:.post h3 {display:none !important;}
</style>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>
Google Visualization API Sample
</title>
<script src="http://www.google.com/jsapi" type="text/javascript"></script>
<script type="text/javascript">
google.load('visualization', '1', {packages: ['table']});
</script>
<script type="text/javascript">
var isFirstTime = true;
var data;
var queryInput;
var credits = '<br /><p style="font-size: 8px;">Courtesy of <a href="http://cyberliving.blogspot.com">http://cyberliving.blogspot.com</a></p>';
var query = new google.visualization.Query(
'https://spreadsheets.google.com/tq?key=YOUR_SPREADSHEET_ID_HERE');
function sendAndDraw() {
// Send the query with a callback function.
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
data = response.getDataTable();
var table = new google.visualization.Table(document.getElementById('querytable'));
table.draw(data, {'allowHtml': true, 'alternatingRowStyle': true, 'page': 'enable', 'pageSize': 10, 'sort': 'enable', 'sortAscending': false, 'sortColumn': 0});
if (isFirstTime) {
init();
}
}
function setQuery(queryString) {
// Query language examples configured with the UI
query.setQuery(queryString);
sendAndDraw();
queryInput.value = queryString;
}
google.setOnLoadCallback(sendAndDraw);
function init() {
isFirstTime = false;
queryInput = document.getElementById('display-query');
}
function setQueryFromUser() {
var queryInput1 = "Select * where C contains " + "'" + queryInput.value + "'" + " or D contains " + "'" + queryInput.value + "'" + " or E contains " + "'" + queryInput.value + "'" + " or F contains " + "'" + queryInput.value + "'" + " or G contains " + "'" + queryInput.value + "'"
setQuery(queryInput1);
}
</script>
</head>
<body style="border: 0 none; font-family: Arial;">
<div style="background-color: buttonface; border: 1px solid gray; margin-bottom: 10px; padding: 5px;">
<span> Refine your search</span>
<form action="">
<table style="font-size: 12px;">
<tr>
<td>Select Category</td>
<td><select id="query-1" onchange="setQuery(this.value)">
<option value="">None</option>
<option value="where B = 'Real Estate'">Real Estate</option>
<option value="where B = 'Automobile/Vehicle'">Automobile/Vehicle</option>
<option value="where B = 'Computers/Electronics'">Computers/Consumer Electronics</option>
<option value="where B = 'Job Opportunity'">Job Opportunity</option>
<option value="where B = 'Others'">Others</option>
</select></td>
<td>Search iSari-Sari Store</td>
<td><input id="display-query" type="text" />
<input onclick="setQueryFromUser()" type="button" value="Search" />
</td>
</tr>
</table>
</form>
</div>
<br />
<div id="querytable">Loading...</div>
<script>document.write(credits);</script>
</body></html>
- Replace the YOUR_SPREADSHEET_ID_HERE with the correct ID.
- There are also other red texts in the handleQueryResponse function. Edit it as you wish or you may remove an option:
- 'allowHtml': true this is to allow some formatting from the table to be carried out in the query output.
- 'alternatingRowStyle': true when set to true alternating rows will get different tone (gray for odd rows and white for evenrows).
- 'page': 'enable' when set to enable, paging is turned on meaning you can set the maximum result to be displayed per page by using the pageSize option.
- 'pageSize': 10 the number is the maximum limit of displayed result per page. A navigation button (previous and next buttons) is placed at the buttom of the result table.
- 'sort': 'enable' seems self-explanatory. When enabled, you can sort any column.
- 'sortAscending': false when set to true, data is sorted in ascending manner. When false, data are sorted in descending manner.
- 'sortColumn': 0 Tells the query language which column to sort automatically before publishing the result on the page. 0 is for column A, 1 for B and so on.
- The blue and purple texts makes up the "Refine your search" user interface. The blue texts is for the drop down menu while the purple makes the search input field.
Back to CyberLiving home page
Other Posts
Put the Loading... text with animated dots
Make your static page look cleaner by removing the page title
5 comments
I've been looking for a way to display my spreadsheets on my site in a better way than a simple iframe, and this is awesome!
I'm having a problem with column headers though, when I use the code for my own spreadsheet, it doesn't pick up my titles and merely sorts it into the rest of everything. What's the trick there? ^^;
Nevermind, I got it. :)
But now I'm curious about another thing, lol. Is it possible to get the entire table to have a transparent background? It'd fit much better into my website that way.
Hi, I am glad that you were able to resolve the problem with your header.
As for the transparent background, try adding css rules from within the javascript function call to set transparent the following:
table.google-visualization-table-table
.google-visualization-table-tr-head
.google-visualization-table-tr-over
.google-visualization-table-tr
.google-visualization-table-tr-head td
.google-visualization-table-tr-over td
personally, I haven't tried it yet (no time, sorry). When I do, I will post it here so do come visit once in a while to check if there is any update.
Here is an update on the posted question. Instead of my previous response try using the cssClassName property within table.draw and define the styles within the style tag. Here is a step-by-step guide to doing it:
http://cyberliving.blogspot.com/2013/08/how-to-customize-google-visualization.html
How to show only specific columns?
Post a Comment