Drop down that reads dynamically from google sheets

Hi, I have a question regarding accessing data from a google sheet column dynamically in a drop down.
In the Values field:
{{get_member.data.name}} returns no data.
{{get_member.data}} returns an array of member objects, so how can I get just the 'name' of all the members in a front end drop down?

Please suggest.


1 Like

Hi @ppatel!

You might have an array of objects here, which is a different data structure than SQL will return. Does this post help with this? How to prefill the content of dropdown from a query? - #6 by alex-w

Copying here for convenience :grin:
Some queries (usualy SQL based ones) return data as an object of arrays like this:
{ key1: [val, val], key2: [val, val] }

Which would make the selection of all of the values in an array easy as shown above, queryName.data.key1.

Many other data sources return an array of objects like this:
[ { key1: val, key2: val }, { key1: val, key2: val } ]

For this, you could either get the array of all of a certain value by using the .map() JS method, or converting it to an object of arrays using our helper function formatDataAsObject() (there’s also the opposite as formatDataAsArray). That would look like either:


1 Like

So for Datasource type - google sheets,
where (what property in the form ) and in what syntax will we use the conversion of an array of objects that looks like the format:
[ { key1: val, key2: val }, { key1: val, key2: val } ]
to an array of strings which for eg is all key2.

Please advise

Does {{ get_member.data.map(member=>member.name) }} in the Values setting of the dropdown work?

1 Like

thanks a lot it worked!!

@alex-w I'm having trouble applying your solution above to my use case which I believe is the same issue as OP. I can't get values from a Google Sheets query column to populate my dropdown dynamically.

For the solutions:


Where is key1 defined from the query output? My assumption is that key1 is the column header, but I don't see that as an output value. Do I need to define is somewhere in the query? It was mentioned these formulas need to go in the Values section of the Select Component, does the query output been any additional transformations as well?

Thanks in advance for the help.


Hi @AndyD, welcome to the community! :blush:

Happy to help. Would you be able to share a screenshot of your Google Sheets query response from the left panel expanded out to show the query.data as much as possible?

Hi Tess, thanks for the welcome!! I posted a screenshot below (omitted actual names), though a co-worker helped me figure this out. Our Sheet column header was previously Formatted Vendor Name and we weren't able to reference this with spaces using the suggested solution queryName.data.map(d=>d.key1). Once we changed the Column Name to formatted_vendor_name, the suggested solution worked and the column values were Values in the Select component, as expected. I'd definitely be interested in knowing how to reference a column header without needing to replace spaces, if it is possible.

For anyone interested, here is the final query in the Values section of the Select Component:

{{get_vendor_matrix.data.map(d=>d.formatted_vendor_name)}} where get_vendor_matrix is the query name and formatted_vendor_name is the Column Header in the Sheet. The Sheet query used A1 Notation to only output the respective vendor name column.

Screen Shot 2021-10-04 at 3.39.50 PM

Oh, wonderful to hear you found a solution in the meantime! :slightly_smiling_face:

This syntax should allow you access keys that have spaces: {{get_vendor_matrix.data.map(d=>d['Formatted Vendor Name'])}}

Ah that's what I was looking for! Thank you!