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:

http://spreadsheets.google.com/tq?tqx=out:html
&tq=select%20H%2CI%2CJ%20where%20H%20contains%20%22SEARCHTERM%22
&key=SPREADSHEETKEY

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:

Here’s how:

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…

Author: Tony Hirst

I'm a Senior Lecturer at The Open University, with an interest in #opendata policy and practice, as well as general web tinkering...

12 thoughts on “Creating Database Query Forms in Google Spreadsheets – Sort Of”

  2. Thanks so much for this great post! I am building a crowdsourcing project that requires each new database entry to be checked against the (rather large) existing data set to avoid duplicate entries. Your case study above seems to provide a perfect solution for this problem! Unfortunately I am running into a small problem making the autocomplete work off of an input cell as you described above.

    I can use the query function to return a correct subset if I “hard code” the query parameter into the formula (eg, “Select A where A contains ‘San'”), but can’t make it work when I try to use a single input cell for my query parameter like you did above (eg, “Select A where A contains ‘”,A1,”‘”).

    I get the following error:

    error: Invalid query: Query parse error: Encountered ” “A “” at line 1, column 16. Was expecting one of: “(” … “(” …

    I tried to copy your code exactly but I’m guessing there’s some small piece of syntax I’m still messing up. If it helps to see it for yourself, below is the link to the spreadsheet itself:

    http://spreadsheets.google.com/ccc?key=0AgABtvIwUWQudGxQR04yQXJjU2VZSVh2WTRLR1REN0E&hl=en

    Thank you so much for any help you are willing to provide!

    1. Hi Mike,
      When you generate the query string, you need to concatenate the separate elements of it; I used a constructor cell to do this but you can compound the queries as follows:
      =query(List!A2:A56,CONCATENATE(“select A where A contains'”,A1,”‘”))
      Okay?

  3. Got it — thanks Tony! I didn’t really understand where the concatenate command was supposed to fit in. As you may know, Excel has a “concatenate” command that is used to simply append multiple text strings to each other. So I thought your use of it had to do with the fact that you were grabbing multiple columns in your query, and/or maybe that you were allowing a text match to be found across any of those fields. Thanks so much for clarifying that.

  4. Just came across this and wanted to give some input:

    =query(List!A2:A56,CONCATENATE(“select A where A contains’”,A1,”‘”))

    can be written like this:

    =query(List!A2:A56,”select A where A contains ‘”””&A1&”””‘”)

    Bit weird to read with three doublequotes on each side, but at least you can drop the CONCAT.

    You could also replace A1 with YourCustomScript() if you wanted to.

  5. I created the Yahoo!pipes to query a dummy spreadsheet. The url base I use for the builder is http://spreadsheets.google.com/tq?tqx=out:csv&tq=select%20A%2CB%2CC%2CD%2CE%2CF%20 where%20A%20contains%20%22BA0000GH%22&key=0Avz70mbKstyLdFpwTjN0YzVZYlZadk5GQldyazlSSWc&hl=en#gid=0. My sheet have six columns A,B,C,D,E,F with the column header in row 1.I am confused with inputs in the following:
    1.a.url on the “fetch csv”
    b.use rows input
    2.the query parameters on url builder especially the tq parameter.(tq gives a parameter different from “text[wired]”) The builder generates another parameter hl which is not in the example. Can I canceled it.
    3. the strings on the string builder
    4.Relevant inputs on text input
    Finally how do I create a Yahoo pipes interface to query the spreadsheet and embed it on Google site.
    Thanks for your response.

  7. Hello,

    I was wondering if you know of a way to query it by the current date. I can only query it by a single day using the decimal form or the date (for example, 12345 might represent 4/19/2010). I tried using TODAY in the URL but that didn’t take.

    any help is appreicated.

    thanks.

    1. Yahoo pipes has a date user input block that accepts things like ‘now’, ‘yesterday’, ‘two days ago’ etc, and generates the appropriate universal time. The Date block can then format the time as required…

  8. Looking for a simple way to create a form to search out and display various rows from a google spreadsheet based on two or 3 columns. Will this do it for me?

  9. Pingback: Google: Database Queries | The Braindoc's Blog

Comments are closed.