Get data from Spreadsheet using CSV + Range & SQL query metod

:wave: 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…

  1. Get data form a sheet by Name
  2. Get data based on Ranges
  3. 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.

img3

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

:warning: 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

Click here to test


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}

:point_right: 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

Click here to test

:point_right: 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

Click here to test

:point_right: 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

Click here to test

:point_right: 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

Click here to test

:point_right: 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

Click here to test

:warning: 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,

:point_right: 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

Click here to test

:point_right: 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'

Click here to test

:warning: Note that if the value you are willing to compare is string then put value between single quotations marks. eg ‘Cttricks’

:partying_face: 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

Click here to test

Hope you have learned somthing new today… Don’t forget to hit :heart:

6 Likes

Mind blowing guide @tanishraj. :grinning:

1 Like