Using Google Spreadsheet as a database for your site

Turn Google spreadsheet into a query able database that you can publish on your web page.

In a previous post (Create your own form and display all response real time using Google Drive) I have mentioned that you can post the results of your Google Form that updates real time. It is good because it is an active result by that I mean it updates data real time.

But the output result will also include everything like the menu bar and tool bars which is basically anything when you open a spreadsheet in Google Drive. It therefore looks and feel more like a work space than a web page. Refer to below image for the output.

Output on your website using the link share address of the spreadsheet.

But what if you wanted a better output without the distracting menu and tool bars, column and row headings and just wanted it to look like any other table you see around the internet?

Use the Data Source URL instead of the link share address
Fortunately, there is a better way of showing your response table and that is using the data source URL. It looks like this:

https://spreadsheets.google.com/tq?tqx=out:html&key=YOUR_SPREADSHEET_ID_HERE

To find your spreadsheet ID, open your spreadsheet in Google Drive and on the address bar should look like this:

https://docs.google.com/spreadsheet/ccc?key=SPREADSHEET_ID#gid=0

Just copy the spreadsheet ID and paste it on your data source URL. To post this in your website, use the iframe tag just like when you made the link share address. It therefore should look something like:

<iframe frameborder="0" height="1100" marginheight="0" marginwidth="0" width="1000" src="https://spreadsheets.google.com/tq?tqx=out:html&key=YOUR_SPREADSHEET_ID_HERE"></iframe>

Please note also that I have highlighted in bold and blue the text tqx=out:html that is because there are three possible output in using the data source URL (json, html and csv). Without specifying the output, it will automatically assume a json output (default). The tqx=out:html text will ensure that it gives an html output which is more readable to us common folk. Using csv will download the table to your hard drive.

Supposing your iframe options were set properly, you will not see any evidence of the iframe on your page and should look like this:

Using the data source URL gives a cleaner look.

Add Query on your data source URL for better interactivity with your table
Now looking better! How about if you wanted some interactivity with the table like showing responses only on a certain category. Like this:

Sort by:
Real Estate                     Automobile or Vehicle
Job Opportunity              Computers and Electronics
Others

From the above table, you can see that you can sort the data by clicking on any of the choices on how to sort it.

To do this include a query in the data source URL. The query has the following syntax:

&tq=QUERY_STRING_HERE

The entire data source URL then would look like this:

https://spreadsheets.google.com/tq?tqx=out:html&key=YOUR_SPREADSHEET_ID_HERE&tq=QUERY_STRING_HERE

Replace that into your iframe source and it will display any data that meets the query requirement.

But what to put on the query string? Instead of discussing the details, I will give you the code for the above example as it is beyond the scope of this article to discuss in detail about the query language.

Here is the code. Feel free to use it. Just copy and paste it to a blank page and it should work fine.

<b>Sort by:</b><br />
<a href="https://spreadsheets.google.com/tq?tqx=out:html&amp;tq=select * where (B='Real Estate' or B='Category') order by A desc&key=YOUR_SPREADSHEET_ID_HERE" target="pakita">Real Estate</a>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<a href="https://spreadsheets.google.com/tq?tqx=out:html&amp;tq=select * where (B='Automobile/Vehicle' or B='Category') order by A desc&key=YOUR_SPREADSHEET_ID_HERE" target="pakita">Automobile or Vehicle</a><br />
<a href="https://spreadsheets.google.com/tq?tqx=out:html&tq=select * where (B='Job Opportunity' or B='Category') order by A desc&key=YOUR_SPREADSHEET_ID_HERE" target="pakita">Job Opportunity</a>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<a href="https://spreadsheets.google.com/tq?tqx=out:html&tq=select * where (B='Computers/Electronics' or B='Category') order by A desc&key=YOUR_SPREADSHEET_ID_HERE" target="pakita">Computers and Electronics</a><br />
<a href="https://spreadsheets.google.com/tq?tqx=out:html&tq=select * where (B='Others' or B='Category') order by A desc&amp;key=YOUR_SPREADSHEET_ID_HERE" target="pakita">Others</a>
<br />
<iframe frameborder="0" height="900" marginheight="0" marginwidth="0" name="pakita" src="https://spreadsheets.google.com/tq?tqx=out:html&amp;tq=select * order by A desc&key=YOUR_SPREADSHEET_ID_HERE" width="1000"></iframe><br /><p style="font-size: 8px;">Courtesy of <a href="http://cyberliving.blogspot.com">http://cyberliving.blogspot.com</a></p>

A little explanation for the code
In order to make an interaction with the table, I made use of an achor text (<a href="source URL>) in order to make the query using the data source URL then targeted the frame (iframe) as my output.

You might have also noticed the blue and red texts. These are what composed the query string, except for the key=YOUR_SPREADSHEET_ID_HERE which we have already explained earlier. The blue texts are called clauses while the red texts are the conditions you set for each clause.

For the select I gave it the * to mean select all columns in the table. You may also select only certain columns. Do it by specifying its column letter. Ex. select A,B,C or select A,C,D,E or select B,E,F

The where clause tells your query which among the data is to be displayed. In my example I used where (B='Real Estate' or B='Category') on my first anchor text (Real Estate) because I just wanted to display all responses under the category Real Estate. I included or B='Category' so that it will still display the column title. Without it, the displayed data will not have any column title.

Then there is the order by A desc. This serves to arrange the data according to the content of column A. Since I wanted it to display the data from the latest entry from the form down to the earliest I choose column a to sort because it contains the time stamp data as to when each data has been created and since I wanted the latest entry to be on top I choose "desc" for descending order.

That is it. I hope I helped you solved your problem. If you wish for any clarification, leave your comments below.

Back to CyberLiving home page

Other Posts
Turn blogger blog into a static web page
Make your static page look cleaner by removing the page title