1

I need to read a Google Sheet using the Sheets API. I can fetch all the rows, however since those rows are in tens of thousands and will continue to grow, I need to find a way to filter them without fetching all the rows.

I just need a SQL WHERE clause with an equality condition for one or more columns. For example, there's a phoneNumber column which is column C in the sheets. I just need to fetch the rows where this column has values equal to some value.

I am currently using the Google Sheets V4 API for Node.js. I was using the get api, but since it just has range as the only filter option available, I thought of trying getByDataFilter api assuming it provides some way to filter the data.

Unfortunately, the only good way in getByDataFilter API is to use GridRange in DataFilter. This still doesn't allow me to provide any condition to filter the data except using rows and/or column ranges.

So is there any way to do it without fetching all the rows? I can't modify that particular sheet. The only way I can find out now is to have a cron job run at midnight to sync the data with a database and then query the database.
Is there any better way to filter the data?

0xC0DED00D
  • 19,522
  • 20
  • 117
  • 184
  • Google Sheets API is not meant to provide database functionality, it is meant to provide UI functionality to programs: read https://stackoverflow.com/a/37868852/9337071 If you know the values to display, you could try programmatically adding a `BasicFilter`, querying row metadata to determine which are hidden, `batchGet` the non-hidden rows, and then clear the `BasicFilter`. – tehhowch Apr 04 '18 at 21:54
  • Related: https://stackoverflow.com/questions/49458708/php-google-sheets-api-v4-filter-by-column-value-and-return-relevant-rows https://stackoverflow.com/questions/44176515/googlesheet-api-php-filter-query https://stackoverflow.com/questions/42463608/how-to-handle-structured-queries-for-retrieving-rows-with-google-sheets-api-v4 https://stackoverflow.com/questions/25732784/official-reference-for-google-spreadsheet-api-structured-query-syntax Website: https://sites.google.com/site/scriptsexamples/learn-by-example/google-sheets-api/filters – tehhowch Apr 04 '18 at 21:59

1 Answers1

0

You can use Google visualization Query directly on the url:

If your url is 

docs.google.com/spreadsheets/d/id

and you want Select A,sum(B) group by A, Use:

docs.google.com/spreadsheets/d/id/gviz/tq?tq="your encoded query here"  
https://docs.google.com/spreadsheets/d/1r8_mfnZAvTFmT02JHi1XgOwn_-sLCR9XgmR8wEQ4uW4/gviz/tq?tq=select%A%2C%20sum(B)%20group%20by%20A
TheMaster
  • 45,448
  • 6
  • 62
  • 85