1

I used google-apis SDK for my development. I can able to get values from sheet by using sheets.spreadsheets.values.get method. But it returns entire values from that sheet. I need to filter something like querying.

Here what I actually trying,

Sample Sheet Image

Assume the above data as a spreadsheet, Here sheets.spreadsheets.values.get this returns whole values from that sheet. But I try to do fetch first row by Email=surya@aaa.com

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Surya S
  • 53
  • 8

1 Answers1

1

The method spreadsheets.values.get allows (and requires) you to specify a range

A sample value for range - for getting row 5 of Sheet1 would be Sheet1!A5:Z5

UPDATE

If you do not know which row you want to retrieve, but want to incorporate a query request, such as in the Google Sheets UI:

It is not possible with Google Sheets API.

But there is a workaround:

Use a Google Visualization API query.

Workflow:

  • Publish your spreadsheet as "Anyone with the link can view"
  • Create the URL by
    • taking the basic URL of your spreadsheet https://docs.google.com/a/google.com/spreadsheets/d/XXXXXX
    • add to the URL /gviz/tq?tq=
    • create you query such as in Sheets UI and URL encode it
    • Add the URL encoded query to your URL
  • Perform a simple fetch request with the resulting URL

Sample:

If your query is select A,

  • the URL would be https://docs.google.com/spreadsheets/d/XXXXX/gviz/tq?tq=select%20A
  • and the response of a GET request would be:
google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"1023816999","table":{"cols":[{"id":"A","label":"","type":"number","pattern":"General"}],"rows":[{"c":[{"v":1.0,"f":"1"}]},{"c":[{"v":4.0,"f":"4"}]}],"parsedNumHeaders":0}});

You can access the nested JSON structure as desired and of course you can formulate your query as desired.

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • Your correct, but I want to filter it like querying with a sheet value. Assume that column named Email Id, I want to fetch a specific row with some of the email ids. – Surya S Oct 23 '20 at 05:06
  • You mean, you want to retrieve a named range? – ziganotschka Oct 23 '20 at 06:28
  • No, I want a single row with the input of some values in the sheet, Here i using `Sheet1` as range params. – Surya S Oct 27 '20 at 04:32
  • How would you formulate your query in the Sheets UI? – ziganotschka Oct 27 '20 at 11:20
  • Why this is not possible with Google Sheets API? Any documentation? – Surya S Oct 27 '20 at 11:24
  • Have a look at the [reference documentation](https://developers.google.com/sheets/api/reference/rest) and you'll see that there is no method that accepts queries. Hence the workaround with Visulaization API. Alternatively you can retrieve all the data and implement a query function programmatically. – ziganotschka Oct 27 '20 at 11:35
  • Ok, I agree with this. How to fetch multiple sheet data from this way like `sheets.spreadsheets.values.batchGet`. – Surya S Oct 27 '20 at 14:01
  • The documentaiton provides a [sample](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet#examples) of how to implement it in node.js or another language of your choice. – ziganotschka Oct 27 '20 at 14:32
  • Sorry, I know to use that method in Sheet API. We can pass multiple ranges to fetch data. But I want to know that, Is there is the same `batchGet` in Visualization API? – Surya S Oct 27 '20 at 14:55
  • You can make yourself some kind of batch request by including all ranges of interest into a query request. – ziganotschka Oct 27 '20 at 15:11
  • How to set a range in this visualization API? – Surya S Oct 27 '20 at 15:15
  • You do not set a range there, you form a query request in such a way that it returns you your range of interest. If this is not what you want, I am afraid that you won't get around retrieving all data with Sheets API and performing the query subsequently manually programmatically. – ziganotschka Oct 27 '20 at 15:21
  • Here I cannot query `Sheet2`, it always returns values from `Sheet1` – Surya S Oct 27 '20 at 15:59
  • You can query by specifying the sheet in the query URL: `https://docs.google.com/spreadsheets/d/XXXXXXXXX/gviz/tq?sheet=sheet2&tq=select%20A` – ziganotschka Oct 27 '20 at 16:18