In this tutorial, you’ll learn how to create an app with a searchable drop-down list – i.e., a drop-down list that will show the matching items as you type.
Preparing the data source
Let’s consider an excel with a list of employee details for this tutorial.
The intent is to create a drop-down list with a search suggestion mechanism, such that it shows a drop-down with the matching options (in this case a list of names). As shown below:
Drop-down based on the text in the ‘Search First name’ field.
The first step is to create a ‘Search name’ text field (app!B2) in the spreadsheet.
The next step is to create a column (A) next to the ‘Full Name’ column.
The formula used in this column (IF(AND(IFERROR(SEARCH(app!$B$2,B2)=1,FALSE)),(MAX($A$1:A1)+1),"")), matches the name from the ‘Search name’ text field and compares with the ‘Full Name column’ and assigns a number in a sequential manner.
Based on the text in the ‘Search name’ field a number is assigned next to the ‘Full name’ column.
The columns D and E in the ‘app’ sheet, lookups the number and name from the ‘data’ sheet and displays in these columns (refer the excel attached below for the formula used).
Then create ‘Names’ field drop-down list (Dataà Data Validation) and use a simple IF function (refer the excel attached below for the formula used) so the drop-down displays only the name based on the text input from the ‘Search name’ field.
Building the app
Now let’s create an app with ‘Open as App’ with this excel file.
After selecting the Excel file (or Google Sheet) in the "Choose your data source" step, you will be automatically redirected to the next step in the app creation process, where you can select the template for your app.
Select the ‘’Search name’ text field and ‘Names’ drop-down field and click “Next”.
And select a template and create the app by clicking the “Create app” button.
Congratulations!! You have created an app with a searchable drop-down list.