I'm trying to use the results of a Json query in a select object (dropdown). This requires an array of strings.
By default, the JSON query returns data in a [{key:value},...] format and I'm trying to transform it to a simple array. I believe I can do this with a formatDataAsArray transformer.
It seems to work in preview, but when I run the query, I get nothing back. Apologies if this is a dumb question, this is all a little new to me.
Below is a screenshot showing that the preview seems to be working. But if I hit "save and run" it returns nothing. Thanks!
For a little more clarification, the source data "reorderReportData" is a Google Sheet. Because it's a Google Sheet, my options are limited on what I can do with it (cannot run a regular SQL query on it).
So I'm using the JSON query. If I turn off the transformer I had in the screenshot above, it runs just fine, but the output looks like this.
The problem is that I'm trying to use this output to populate the dropdown options in a select object. If I use the raw output, the dropdown options look like {"Supplier 1", "Supplier Name"}, and I just want it to show Supplier Name - this is what I was hoping to do with that transformer.
So I guess the main reason I posted this was for learning purposes...I was confused why preview would output something that looks OK, but the query outputs nothing whatsoever.
Maybe the better answer here is to avoid using Google Sheets, but I was hoping to be able to use Sheets for prototyping stuff.
This blank thing happens to me more often than I'll admit and I pound away at options and it always resolves. My first two suggestions are the most common errors I commit.
Now the transformer for your JSON SQL should be formatDataAsObject() to get it back to a dropdown friendly format. It looks like it is in an array format before transformation so you are sending an array and converting it to an array and confusing the function that expects an object to turn to an array. That's my newest guess.
I was able to get this to work (kind of) by using your suggestions.
Something odd happened. I had the transformer set up within the query - when would I run the query, it worked 50% of the time. In other words:
Run query -> good
Run query again -> error message related to transformer (something related to empty data)
Run again -> good
etc.
So I created a separate transformer instead of using the one within the query, and it worked fine with the exact same JS. It seems like the transformer within the query was only receiving data on every 2nd run of the query.
So I guess this was a bug, and I guess I found a workaround. Strange though.
Yeah, I have had random situations where I had to rebuild a query or delete and replace a component to get around weird behavior. I chalk it up to the same reason you need to reboot your computer sometimes - systems are so complicated that they "degrade" over time and must be refreshed.
