CODEWITHSUNDEEP
node.jsgoogle-apigoogle-sheets-apigoogle-api-nodejs-client
Surya S
Surya S

Reputation: 63

How to fetch Specific row from google-sheet by using Google-sheet-apis(Node.js)?

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 protected]

Upvotes: 2

Views: 2788

Answers (1)

ziganotschka
ziganotschka

Reputation: 26836

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.

Upvotes: 2

Related Questions