I’m having problems exporting the csv with make, can someone give me a hand?
What’s not working for you?
Hola!
Playing with the Google Sheets API days ago, I found this useful tip to export your data from GS to a CSV file very easy.
All what we need is follow this syntax:
https://spreadsheets.google.com/tq?tqx=out:csv&tq={Query}&key={SpreadsheetKey}&sheet={SheetName}
Example: my GS is this and has more than 110k rows
and if I apply a query to retrieve 3 columns and 50 rows:
select A,B, D where B>75 and B<78 limit 50
using this data source URL
https://spreadsheets.google.com/tq?tqx=out:csv&tq=select A,B, D where B>75 and B<78 limit 50&key={SheetKey}
Google replies with a very clean and exact csv file to my query ready to be saved to my device.
This is how it looks like in MS Excel
The advantages are:
- Using a query, we can find and use any sheet(s) in GS regardless of whether Glide loaded it into the app (avoid loading unnecessary rows) .
- We don’t need to create additional relations or columns in Glide to retrieve data and build the new csv file. The Query allows several clauses (where, group by, pivot, order by, limit, etc) to build a customized or complex file ready to be used.
- It’s free and doesn’t consume edits
The disadvantage is:
- only apply to Google Sheets
Note:
Your file can be exported in several formats depending on tqx parameter:
- tqx=out:csv → CSV format
- tqx=out:html → HTML table
- tqx=out:json → JSON data
I hope it helps anyone…
Saludos…
4 Likes
Wow. Very helpful. I was playing around with the query parameters in url described here:
But the format of the JSON that it returns wasn’t useful for my goals, so I moved on. Great to know you can define the output to include csv and other formats. Thanks for sharing.
1 Like
@gvalero wondering if you have a tip for me. I can’t seem to build a query using this method that works with text. I have this data:
| Color | Type | Number |
|---|---|---|
| Green | Apple | 1 |
| Red | Apple | 2 |
This works to search for a number in the number column
https://spreadsheets.google.com/tq?tqx=out:html&tq=SELECT * Where C=1&key=1CWnyfwHdhMrftTv6YMw-qNAwt1NELZthdFgf3Av-Ilk
But this does not work:
https://spreadsheets.google.com/tq?tqx=out:html&tq=SELECT * Where A=Red&key=1CWnyfwHdhMrftTv6YMw-qNAwt1NELZthdFgf3Av-Ilk
Single and/or double quotes around the word Red do not work. Any thoughts?
Let me try when I have my laptop nearby 
Ok… this works for me!
I have this table:
If I apply
https://spreadsheets.google.com/tq?tqx=out:html&tq=select * where B='Wood'&key={SheetKey}
I receive this data:
If again, I send
https://spreadsheets.google.com/tq?tqx=out:html&tq=select * where A='Steel'&key={SheetKey}
the new reply is:
Even, it works using single or double quotes as well…
https://spreadsheets.google.com/tq?tqx=out:html&tq=select * where A="Steel"&key={SheetKey}
Can you show the error message you receive from web browser?
Saludos!
Hi. Thanks for testing. So this URL:
https://spreadsheets.google.com/tq?tqx=out:html&tq=select * where A=‘Red’&key=1CWnyfwHdhMrftTv6YMw-qNAwt1NELZthdFgf3Av-Ilk
Returned this error:
{“version”:“0.6”,“reqId”:“0”,“status”:“error”,“errors”:[{“reason”:“invalid_query”,“message”:“INVALID_QUERY”,“detailed_message”:"Invalid query: PARSE_ERROR: Encountered \u0022 \u003cID\u003e \u0022A \u0022\u0022 at line 1, column 16.\nWas expecting one of:\n \u0022(\u0022 …\n \u0022(\u0022 …\n "}]}
Looks like you have “smart” quotes around Red
2 Likes
Argh! Ok. 