How to handle structured queries for retrieving rows with Google Sheets API v4

Question

I would like to begin migrating my application that relies on Google Spreadsheet API (v3) to Google Sheets API v4. However, there is one question I have that remains before I begin.

In Google Sheets API v3 I can get a rows based feed with a structured query using the sq parameter in the request URL. This is useful if the sheet has 10000 rows and I need to get only rows with a certain value in a specific column(s). It's also very efficient in terms of speed.

In looking at Google's migration guide to v4, I found this statement under "Retrieve Row Data":

The Sheets API v4 does not currently have a direct equivalent for the Sheets
API v3 structured queries. However, you can retrieve the relevant data and
sort through it as needed in your application.

How do I go about retrieving "relevant data" from a sheet of 10000 rows efficiently given that there is no structured query in v4 API. I would rather not have to retrieve all 10000 rows only to sift through that response. Especially since it will slow down my app and probably increase the amount of data being sent over the wire.

With the v4 API, how can I retrieve rows that have specific values in a specific column (or set of columns)?

noogui noogui · Accepted Answer · 2017-02-26T16:42:56

I don't need to reference the docs because you are already aware of it. There is no such feature in Sheetsv4. You have to manually perform the Basic Reading methods and parse the response, loop over it and sift for the value you're looking for.

This is related to your SO question here. I've already written some code there so I'll just post the results here so you have an idea how I searched for values.

To fetch for phone, I looped through the JSON response:

arrayBuffer = xhr.response;
             console.log("arrayBuffer "+ arrayBuffer);
             myArr = JSON.parse(arrayBuffer);

              for(var i = 0; i < myArr.values.length; i++){

                 if(myArr.values[0][i] === "phone"){
                     console.log("column position is " + (i+1)); // +1 because counting starts at 0
                 }
              }

You can also file a Feature Request here.

How to handle structured queries for retrieving rows with Google Sheets API v4

1 Answers