Dropdown selection with QUERY function


One of my spreadsheets contains the following structure:
Two columns, A and B (see pic below)

Column A contain the parameter names
Column B contain the parameter values, which are populated when value B1 (dropdown) is selected by user.

When B1 is selected, cell B2 uses QUERY function to pull the data into the rest of the rows below in colum B.
This all work fine in Google sheet but I seem to have problem of bringing this functionality into AppSheet.
Any help would be apprecited.

HI @AlexShevyakov Check out this link on basic App design.

Hi @Lynn!
Well, I did. Do you imply that my data structure is initially incorrect?

Hi @AlexShevyakov Just looking at what you have there I would make the parameter names in col A column headers and have the data in column B in rows under the columns. Is there any reason that would not work for you?

@Lynn You mean - make it horizontal, rather than vertical? The only parameter that a user changes (selects) is Vessel - where would you place this one then?

As you are using an ARRAYFORMULA, it will populate the sheet content after the user selects B1 value in the dropdown and save the form. Without the B1 value recorded in the gSheet, your QUERY will be empty.

1 Like

@LeventK. No, actually

=TRANSPOSE(QUERY('$SelectData'!$A$2:$AN, "SELECT B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z WHERE A = '"&$B$1&"'", 0))

I couldn’t understand the difference??

@LeventK What I am asking is: do I need to replace QUERY with ARRAYFORMULA for the thing to work?


How B1 is selected? From where? From the app? If that’s the latter, unless you don’t record the B1 value to the sheet with the app, your ARRAYFORMULA or QUERY won’t populate anything. Besides, you need to sync twice for letting the app to read the populated values on this sheet.

@LeventK Ah I see - sorry.
No, at the moment B1 is selected in the google sheet by the user. The QUERY formula pulls data from another spreadheet and by use of TRANSPOSE populate information into the B column (B2:B).
When Building Appsheet app, I cannot create a dropdown to replicate the same functionality.

Have you checked this page?