Creating Database Query Forms in Google Spreadsheets – Sort Of
It’s all very well using a Google spreadsheet as a database, but sometimes you just want to provide a simple form to let people run a particular query. Here’s a quick way of doing that within a Spreadsheet…
So for example: Can you help me crowd source a solution?. The problem is as follows:
Students will make five choices from a list of over 200 projects that have been anonymised… We will give each project a code, and have already entered all the details into an excel sheet so we can tie the project code to the supervisor.
We need a solution that will enable students to enter their project code and then have the title of the project displayed as a check to make sure they have entered the code correctly. The list of projects is just too long for a drop down list, even when split by department (around 50 in each).
Does anyone have any suggestions of tools that we can use for students to submit this type of information, so that we get it in a format that we can use, and they get confirmation of the project titles they have chosen? A simple google form isn’t going to hack it!
Here’s one way…
Create a “form” – the text entry cell can be highlighted by setting the background colour from the spreadsheet toolbar:
Construct a query. In this case, I need to select three results columns (H, I and J) from another sheet (‘Sheet1′, the one that acts as the database and contains the project codes) so the query will be of the form “select H,I,J where H contains “BIOCHEM”; the search term (“BIOCHEM”) is pulled in from the query form show above:
=concatenate(“select H,I,J where H contains ‘”,B2,”‘”)
(As a rule of thumb, if you want your query to select cells A, D, AC, the range set in the first part of the query that defines the database should span the first to the last column in the select range (Sheet1!A:AC, for example).)
By using the contains relation, this query will generate a set of results that are, in effect, a list of auto-complete suggestions as the result of a searching on a partially stated query term.
Assuming I have placed the query in cell A4, I can automatically get the results from the query as follows:
Note that it would be possible to hide the query generator (the contents of cell A4) in another sheet and just have the search box and the results displayed in the user interface sheet.
Another approach is to query the spreadsheet via its API.
So for example, if the original spreadsheet database was published as a public document, we could also grab the results as an HTML table via an API using a URI of the form:
Setting out:csv would return the results in comma separated variable format, so we could create a Yahoo pipes interface to query the form, for example:
What would be really useful would be if the Google/Yahoo widget options for the feed respected the form elements, rather than just generating a widget that displays the feed corresponding to the current Run of the pipe with the provided search terms.
Building such a widget is something I need to put on my to do list, I guess?! Sigh…