Google Spreadsheet as Database: Pulling content from Shared sheet with Query language

Often we come across requirements where content in the back-end to be edited by admin or a team and in the front-end user should be able to use filter or option to view the data particularly he is looking for. Developing the front end for this may be easy. But make the content editable, by multiple users will usefully take lot of time.

spreadsheet-db
So imagine using Google Spreadsheet containing data and querying content for your table directly from there… Doesn’t it sound interesting? Whole effort of developing interface to edit content and save it in a database will be saved. You can just create one spreadsheet in Google drive, share it with collaborators for editing and to the public for viewing.

Just open google drive and create that sample spreadsheet first. Mine is here: Students spreadsheet, you can even copy it to your drive. Note the parameter “Key” in the URL(Highlighted in bold here): https://docs.google.com/spreadsheet/ccc?key=0Al71oaThfyoadGF3QnMxQVcxcjAwQk9hNGNTelFxcnc&usp=drive_web#gid=0

Query Language:

You can use many statements just similar to your sql queries. First, let is display the whole spreadsheet as table. For this you can use “select” statement. Use the following URL, you can change the “key” from your spreadsheet URL. Query “select *” becomes https://spreadsheets.google.com/tq?tqx=out:html&key=0Al71oaThfyoadGF3QnMxQVcxcjAwQk9hNGNTelFxcnc&headers=1&tq=select%20*

Query

 select * where E = "Art" 

becomes select%20*where%20E%20=%20%22Art%22 after URL encoding it. When added it to as parameter to URL, it becomes https://spreadsheets.google.com/tq?tqx=out:html&key=0Al71oaThfyoadGF3QnMxQVcxcjAwQk9hNGNTelFxcnc&headers=1&tq=select%20*where%20E%20=%20%22Art%22

Use the tool below to

Use this tool to encode encode or decode a query string for URL: url-encoder


You can use either frame or Ajax to display this table.

If you observe parameters passed to spreadsheet, out:html specifies that we need the response in html format. CSV and JSON are other options available. “headers=1″ says that first row if spreadsheet is freezed as header row of table.

View Example (Right click->”Save as” to save the example)

 

<select id="major" onchange="display_list(this.value);"><option value="English">Please select Major...</option><option value="English">English</option><option value="Math">Math</option><option value="Art">Art</option><option value="Physics">Physics</option>
</select></pre>
<div id="tablediv">Select Subject to list students</div>
<pre><script type="text/javascript">// <![CDATA[
function display_list(major){ 		var file = 'https://spreadsheets.google.com/tq?tqx=out:html&key=0Al71oaThfyoadGF3QnMxQVcxcjAwQk9hNGNTelFxcnc&headers=1&tq=select%20A,B,C,D,E,F%20WHERE%20E%20contains%27' + major + '%27'; 		var request = getHTTPObject(); 		request.onreadystatechange = function() { 			parseResponse(request); //this is what happens once complete 		} 		request.open("GET",file,true); 		request.send(null); 		document.getElementById('tablediv').innerHTML = "Loading..."; 	} 	function parseResponse(request) { 		if(request.readyState == 4){ 			if(request.status == 200 || request.status == 304){ 				var results = document.getElementById("tablediv"); 				results.innerHTML = request.responseText; 			} else { 				results.innerHTML = "There was some error :(  "; 			} 		} 	} 	function getHTTPObject() 	{ 	  if (window.XMLHttpRequest) return new XMLHttpRequest(); 	  else return new ActiveXObject("Microsoft.XMLHTTP"); 	}
// ]]></script>

Some of the options that can give you idea about more possibilities of usage:
headers=1 : Use first row as header.
order by: Sort the table by a particular column. Ex: https://spreadsheets.google.com/tq?tqx=out:html&key=0Al71oaThfyoadGF3QnMxQVcxcjAwQk9hNGNTelFxcnc&headers=1&tq=select%20*where%20E%20=%20%22Art%22%20order%20by%20C

group by:
SELECT A,SUM(B) GROUP BY A

https://spreadsheets.google.com/tq?tqx=out:html&key=0Al71oaThfyoadFhlZHBiT1I2MVlXSWlTWm4zblVlMnc&headers=1&tq=select%20A,sum(B)%20group%20by%20A

You can use operators ( =, <=, <, >, >=, =, !=, <>) for example,

SELECT * WHERE B <= 5

or, and operators: select B,C,D where (F contains 'Drama' or C matches 'Art') select A,B,C,I where (I!=76 and I>=0) order by I

Comments

  1. Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>