Monday, December 5, 2011

Embedding pre filtered table in Google Site

If you have a Google spreadsheet with lots of data and you want to embed different results in different pages with a Google site, this is how you do it.

First create a Google sheet and share it with the following option (Anyone with link):

Next populate your Worksheet (for example):

Highlight all the data and Insert a Named Range and give it a name.  In this example, I called it SampData.

Insert a new sheet and put in the first cell your query.  In my case, I place the following:

=Query(SampData, "select * where B='Rent'")

One key thing to note that for the where clause, you need to use the cell column in this case is B instead of the field name.

Next insert a Gadget to the worksheet call table Gadget.

You will then be prompted for the range, specify the range in the worksheet that has the query results.  In my case, it is Sheet7!A:E.  

You can create as many worksheets with different results as you want.

Next go to you Google site, create the page and add the table gadget there.    The easiest way, is to click insert Gadget and then add by url and paste the following: 

The gadget will require the data, the data is available by clicking on the gadget in the worksheet and select Get Query Data source url. Copy and paste the data source url to the gadget in the google site:

You can use the same gadget in the worksheet and change the range (using different sheets) to get different filtered results to display on your Google site.

No comments: