Hello everyone
There are a lot of query related to read data from spreadsheet. You can use Spreadsheet Component available in our builder but today i’m going to share the CSV method to read/get data from Spreadsheet. By using CSV method you can call data of sheets using SheetName, Ranges and can also use SQL querys to get filtered data from sheet. To explain this i’m going to summarize this topic in 3 simple method…
- Get data form a sheet by Name
- Get data based on Ranges
- Get filtered data using SQL query
And before all these you must have a spreadhseet, having permission “Anyone with this link can view” this is needed because when we call data from spreadhseet it must allow anonymous calls (sent from your app) to read data from the sheet.
I’m using “Sample Data” Spreadsheet for this topic and it is available for all for testing.
Now 1st we need the url of our spreadsheet. You can get that from the address bar
https://docs.google.com/spreadsheets/d/1_ztoqpbM16kGVz_F3ojrma7ZmNnso7O0cAdx5exfkMw/edit#gid=0
We just have to replace /edit#grid=0 with /gviz/tq?tqx=out:csv so the final URL of our spreadsheet is,
https://docs.google.com/spreadsheets/d/1_ztoqpbM16kGVz_F3ojrma7ZmNnso7O0cAdx5exfkMw/gviz/tq?tqx=out:csv
1. Get data form a sheet by name
To get data from a perticular sheet we have to use parameter sheet={sheet_name}, So let put this parameter on the modified url of our spreadsheet
https://docs.google.com/spreadsheets/d/1_ztoqpbM16kGVz_F3ojrma7ZmNnso7O0cAdx5exfkMw/gviz/tq?tqx=out:csv&sheet=Sheet1
2. Get data based on Ranges
Using this method we can get data from specific Row, Column & Cell too. But 1st let’s try to get data of a perticular range from a sheet and for this we have to put another parameter on the modified url of our spreadsheet i.e. range={range}
Suppose i want to get first 5 rows from Sheet1
range=A1:G5
https://docs.google.com/spreadsheets/d/1_ztoqpbM16kGVz_F3ojrma7ZmNnso7O0cAdx5exfkMw/gviz/tq?tqx=out:csv&sheet=Sheet1&range=A1:G5
Suppose i want to get data from row 5 to 8 from Sheet1
range=A5:G8
https://docs.google.com/spreadsheets/d/1_ztoqpbM16kGVz_F3ojrma7ZmNnso7O0cAdx5exfkMw/gviz/tq?tqx=out:csv&sheet=Sheet1&range=A5:G8
Let’s get data of a particular row say 3
range=A3:G3
https://docs.google.com/spreadsheets/d/1_ztoqpbM16kGVz_F3ojrma7ZmNnso7O0cAdx5exfkMw/gviz/tq?tqx=out:csv&sheet=Sheet1&range=A3:G3
Let’s get data of a particular column say OrderID as this is the 1st column it’s range id is ‘A’
range=A1:A
https://docs.google.com/spreadsheets/d/1_ztoqpbM16kGVz_F3ojrma7ZmNnso7O0cAdx5exfkMw/gviz/tq?tqx=out:csv&sheet=Sheet1&range=A1:A
Let’s get data of a particular cell i.e. of column Item and row 6.
range=D6
https://docs.google.com/spreadsheets/d/1_ztoqpbM16kGVz_F3ojrma7ZmNnso7O0cAdx5exfkMw/gviz/tq?tqx=out:csv&sheet=Sheet1&range=D6
Note : in above example i have used, G as its the last column in sheet1, if you have more then use that range id.
3. Get filtered data using SQL query
Using this metod you can get filtered data form the sheet using sql queys. And for this we have to use another paremeter tq={query}
Example Url :
https://docs.google.com/spreadsheets/d/1_ztoqpbM16kGVz_F3ojrma7ZmNnso7O0cAdx5exfkMw/gviz/tq?tqx=out:csv&sheet=Sheet1& tq={query}
Here are few examples,
Let’s get data from sheet having Units > 50 ‘Note : Here range id of column unit is E’
tq=SELECT * WHERE E < 50
https://docs.google.com/spreadsheets/d/1_ztoqpbM16kGVz_F3ojrma7ZmNnso7O0cAdx5exfkMw/gviz/tq?tqx=out:csv&sheet=Sheet1&tq=SELECT * WHERE E < 50
Let’s get data from sheet having Item > Pencil ‘Note : Here range id of column unit is D’
tq=SELECT * WHERE D = ‘Pencil’
https://docs.google.com/spreadsheets/d/1_ztoqpbM16kGVz_F3ojrma7ZmNnso7O0cAdx5exfkMw/gviz/tq?tqx=out:csv&sheet=Sheet1&tq=SELECT * WHERE D = 'Pencil'
Note that if the value you are willing to compare is string then put value between single quotations marks. eg ‘Cttricks’
BONUS
Let’s pass above 2 querys togather
tq=SELECT * WHERE D = ‘Pencil’ AND E < 50
https://docs.google.com/spreadsheets/d/1_ztoqpbM16kGVz_F3ojrma7ZmNnso7O0cAdx5exfkMw/gviz/tq?tqx=out:csv&sheet=Sheet1&tq=SELECT * WHERE D = 'Pencil' AND E < 50
Hope you have learned somthing new today… Don’t forget to hit 
