How can I use the Google Sheets (v4) API getByDataFilter to return a specific row of data?

Question

I want to find a way of using the Google API to retrieve a specific row of data based on criteria I supply. I am thinking "getByDataFilter" might do this?

I am writing an app for personal use. I want to use Google Sheets as my backend so that I can mail merge from it. At this stage I am using the API explorer to see what data I can get. I have figured out how to get data from a range of cells using

    GET https://sheets.googleapis.com/v4/spreadsheets/1keCaROqv4ytDaf5AhcMV13Jj3N_eZCpLfRAGt2ycwA8/values/A%3AL?valueRenderOption=UNFORMATTED_VALUE&fields=values&key={YOUR_API_KEY}

I have tried playing around with getByDataFilter but cannot seems to return any filtered values.

I have the following which basically returns everything.

    POST https://sheets.googleapis.com/v4/spreadsheets/1keCaROqv4ytDaf5AhcMV13Jj3N_eZCpLfRAGt2ycwA8:getByDataFilter?fields=sheets(data(columnMetadata%2FdeveloperMetadata%2Flocation%2FdimensionRange%2FstartIndex%2CrowData%2Fvalues%2FeffectiveValue%2CstartColumn%2CstartRow))&key={YOUR_API_KEY}

    {
     "dataFilters": [
      {
       "developerMetadataLookup": {
        "metadataLocation": {
        }
       }
      }
     ]
    }

I expect to be able to filter the data and only return the columns that match the criteria I specify. i.e. a simple example would be if I had a sheet like this

----------------------------------
| Name  | Age   | Job            |
----------------------------------
| Craig | 42    | Teacher        |
----------------------------------
| Tim   | 23    | Student        |
----------------------------------
| Jess  | 45    | Accountant     |
----------------------------------

I want to be able to somehow filter for Tim's row and return ['Tim',23,'Student']

Thanks,

Craig

Answer 1

• You want to retrieve the filtered values from your Spreadsheet.
• You want to retrieve the values from outside.
• You have already been able to use Sheets API and the access token for requesting the API.

If my understanding is correct, how about this answer? Please think of this as just one of several answers.

Issue:

Unfortunately, the filtered values cannot be directly retrieved using the method of spreadsheets.getByDataFilter. When you want to use this, at first, please put the developer metadata. Then, you can retrieve the value using the method of spreadsheets.getByDataFilter. For example, as a test case, it thinks of the following situation.

• Your sample values in your question are used for this situation. So Tim is put in a cell of "A3".
• Developer metadata is created to the cell "A3" with "createDeveloperMetadata" using the batchUpdate method. metadataKey and metadataValue are key1 and value1, respectively.
• Under the above situation, when the following endpoint is requested, the values can be retrieved.
  • Endpoint: POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:getByDataFilter?fields=sheets%2Fdata%2FrowData%2Fvalues%2FuserEnteredValue
  • Request body: {"dataFilters":[{"developerMetadataLookup":{"metadataLocation":{"sheetId":{sheetId}}}}]}
  • Of course, the values can be also retrieved with metadataKey and metadataValue.
  • Result: {"sheets":[{"data":[{"rowData":[{"values":[{"userEnteredValue":{"stringValue":"Tim"}},{"userEnteredValue":{"numberValue":23}},{"userEnteredValue":{"stringValue":"Student"}}]}]}]}]}

In this case, it is required to set the developer metadata to the cells. But from your question and replying, I thought that this method might be different from what you want. So I would like to propose the workaround for directly retrieving the filtered values from the Spreadsheet.

Workaround:

In this workaround, I would like to propose to retrieve the filtered values using the query language. The sample query is as follows.

Query:

select * where C='Student'

or

select * where B<40

In above case, when job of the column "C" is Student, the rows are retrieved. In below case, when Age of the column "B" is less than 40, the rows are retrieved. In your sample values, "Tim","23","Student" is retrieved from both query.

When above query is run by the curl command, it becomes as follows.

curl sample:

curl "https://docs.google.com/spreadsheets/d/{spreadsheetId}/gviz/tq?gid={sheetId}&tqx=out:csv&range=A2:C&tq={query}&access_token={accessToken}"

• In this sample, CSV data is returned as the result value.
• When you use this script, please set {spreadsheetId}, {sheetId}, {query} and {accessToken}. If you want to use other situations, also please modify range=A2:C.
  • Each value of the query parameters of URL might be required to be URL encoded. Please be careful this.
• When you access to above URL using your browser, the CSV data is retrieved.

Result:

When above curl sample is run for your sample values in your question, the following values are retrieved.

"Tim","23","Student"

Reference:

• Query Language Reference (Version 0.7)