dimanche 27 octobre 2019

How to build a search form in googlesheets with query()?

Requested behaviour:
I would like to create a search form in Google Sheets to query a table that I use as a database.

  1. The user should be able to query the table by multiple searches categories which the user can type into a cell of the sheet.
  2. If the user does not type in a search string, then all items should be displayed.
  3. Also, the user should be able to choose between an "including OR" and an "excluding AND" search.

The original table is on a different sheet than the search form. The final searchform should have more than 10 searchable categories.

Current State
Since the original table is in a different sheet than the search form, my idea has been to import the table by a dynamic query() function.

I created two input search input fields and a field where the user can switch between "OR" and "AND". I also created a query function that connects these 3 search terms. The change between "OR" and "AND" search works (with the first approach).

First approach:

=QUERY('Geschäftsvorfälle'!A2:AG1000, "select * WHERE A="&B4&" "&D1&" B='"&B5&"'")

Second approach:

=QUERY('Geschäftsvorfälle'!A2:AG1000, "select * " &if(B5="Alle",, "WHERE B='"&B5&"'") &if(B4="",, "WHERE A="&B4&""))

Issue
The first approach works with the "OR" search but gives an empty sheet back if I use multiple search terms. It also throws a "VALUE" error if leave one search term blank. The second approach throws a "VALUE" error if I use multiple search terms even there should be matching rows.

Is there a way to make this kind of a searchform work in Google Sheets? If yes is it possible to do it with query() and how do I do it? Could you provide some example screenshots or code?

Screenshots
The searchform:
enter image description here

The combining query:
enter image description here

Aucun commentaire:

Enregistrer un commentaire