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…