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.

Thursday, December 1, 2011

Performing Word Search

I finally found something I can't do with Google Search.  It is trying to solve look for words of a specific length, either start with end with etc.  Basically a word search.  We normally do that when we want to solve a puzzle but in this particular case, it was a homework assignment for my daughter. 

Luckily there are a few sites that provide solutions.  To find a 5 character word which starts with a, you just search a----  To find a 5 character word which starts with a, you just search a????.  However, with this site, you may need to click on common words and phases otherwise it will include a lot of other results which may contain -, ' etc.