I am using nodeJS for fetching the data from google sheet and the URL looks like :
var url = `https://sheets.googleapis.com/v4/spreadsheets/${sheet_key}/values/Sheet2!A1:J20?key=${google_API_key}`
From this, I am only getting the data between A1 to J20 So now I have two questions :
- How do I get all the rows from the sheet or last 10.
- How do I apply a structured query filter like: where name == "Himanshu"
Edited :
For question 2: what I have done is using Query Language Reference (Version 0.7) / structured queries, which is referenced by @Tanaike. This is how my URL looks
like now and its working as well.
https://docs.google.com/a/google.com/spreadsheets/d/${sheet_key}/gviz/tq?tq=select%20*%20where%20B%20%3D%20'Himanshu'&key=${google_API_key}
But the issue is it's returning me the string something like this which I cant able to parse.
google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"509770406","table":{"cols":[{"id":"A","label":"Response Path","type":"string"},{"id":"B","label":"Name","type":"string"}]}})
How about this answer?
A1
You can retrieve all rows using sheet name as the range as follows.
var url = `https://sheets.googleapis.com/v4/spreadsheets/${sheet_key}/values/Sheet2?key=${google_API_key}`
A2
You can use the query like where name == "Himanshu" using Query Language. In order to use this, please share the Spreadsheet as follows.
- On Google Drive
- On the Spreadsheet file
- right-click -> Share -> Advanced -> Click "change" at "Private - Only you can access"
- Check "On Anyone with the link"
- Click "Save"
- At "Link to share", copy URL.
- Retrieve file ID from
https://docs.google.com/spreadsheets/d/### file ID ###/edit?usp=sharing
About Query Language, you can see the detail information at Query Language Reference.
If I misunderstand your question, I'm sorry.
Edit 1 :
When you want to retrieve the formatted data, please use tqx=out:. In your case, tqx=out:json is used. It seems that it's default. For example, you want csv, it's tqx=out:csv. Also you can use tqx=out:html. I think that tqx=out:csv may be useful for your situation.
https://docs.google.com/a/google.com/spreadsheets/d/${sheet_key}/gviz/tq?tqx=out:csv&tq=select%20*%20where%20B%20%3D%20'Himanshu'&key=${google_API_key}
Edit 2 :
In order to retrieve JSON data of spreadsheet, please do as follows.
- On Spreadsheet
- Click File -> Publish to the web
- Publish as web page.
URL 1
You can retrieve the values of spreadsheet as JSON using the following URL.
https://spreadsheets.google.com/feeds/cells/${sheet_key}/od6/public/values?alt=json
od6 means 1st page of spreadsheet.
URL 2
If you want to retrieve other pages, please confirm using the following URL.
https://spreadsheets.google.com/feeds/worksheets/${sheet_key}/public/basic?alt=json
Note :
- If error occurs when you access the URLs, please confirm whether the spreadsheet is published again.
