Retool not interpreting Google Sheet data correctly


I am trying to create an app that will read a range from a google sheet, convert it to array, and upload the result to an existing table in my database. I have set up a query that reads the gsheet and outputs the results perfectly, but I am struggling to query it in SQL.
When I write this query:

select "First Name" from {{}};

It returns the following output which is not correct.

Screenshot 2024-04-02 at 17.01.35

When I inspect {{}}, however, it shows the data correctly, as a set of key value pairs corresponding to my headings.

How do I set it up so that I can

  1. Read the Gsheet and convert to array
  2. Inner join a couple of other tables from my db
  3. select columns from the result and insert into another table.

Thank you!

Hi @adam1, could you share the "get_gsheet" query with us? It would help to see how we are formatting the results to an array as well.

Using Query JSON with SQL to read the data from "get_gsheet" could help. Then, a query to bulk insert records to your db. :slightly_smiling_face:

Thanks for the response! Yep I used that approach and I have managed to get it working! I had some incorrect syntax in my query which was causing the error. And renaming the columns in my gsheet db to snake_case format made it easier to query.

You are welcome! Happy to hear everything is working.
snake_case column names on Google sheets definitely makes the integration easier. :snake: