How to use getByDataFilter Sheets API v4 to get specific row data

Question

I need to get specific data row of this table: Google Sheet Data Base , for that I am using the following filter parameter:

var filter = {
  "dataFilters": [
    {
      "developerMetadataLookup": {
        "metadataValue": "[email protected]"
      }
    }
  ]
};

but the current result is as follows:

response: {
  "spreadsheetId": "1chGysP"
}

The result I need is something like the following:

response: {
  "range": "'Hoja 1'!A4:D4",
  "majorDimension": "ROWS",
  "values": [
    [
      "3",
      "domain4",
      "[email protected]",
      "Active"
    ]
  ]
}

I found a similar question to mine, and it seems to work but I think I wouldn't be taking advantage of using the api directly like checking quotas, reporting dashboard and so on. How can I use the Google Sheets (v4) API getByDataFilter to return a specific row of data?

My complete code is as follows:

//function for bot that runs asynchronously from Google Apps Script 

function consultData(){
var url = 'https://sheets.googleapis.com/v4/spreadsheets/1chGysP/values:batchGetByDataFilter?key=KAIzaSy'

  var service = getOAuthService();
  service.reset()

  var filter = {
  "dataFilters": [
    {
      "developerMetadataLookup": {
        "metadataValue": "[email protected]"
      }
    }
  ]
};

  var params = {
      headers: {
      Authorization: 'Bearer ' + service.getAccessToken()
      },
      method: 'get',
      contentType: 'application/json',
      payload: JSON.stringify(filter),
      muteHttpExceptions: true
    };
  var response = UrlFetchApp.fetch(url, params).getContentText();
    Logger.log('response: ' + response);
  return response;
}

API reference: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/getByDataFilter

Although I'm not sure about the situation of your Spreadsheet, when you want to retrieve the values of "getByDataFilter" of Sheets API, the Spreadsheet is required to have the developer metadata. How about this? And also, if you use Google Apps Script for achieving your goal, as a workaround, how about using TextFinder? — Tanaike
Thanks for your reply, I see that TextFinder is a SpreadsheetApp class but I think I could not input a different oauth token to it than user, I need it to run on behalf of a service account. — Julian Camilo Benavides Vanega
Edit your question to clarify whether you actually have developer metadata set in row3. — TheMaster
Thank you for replying. When you want to make users search the value from user's Spreadsheet, using the user's access token, how about using the query language like this thread or using Web Apps with Execute the app as: User accessing the web app and Who has access to the app: Anyone? The latter can use the TextFinder. Ref — Tanaike
Thanks, I understood that for my case I must create a developer metadata, sincerely I did not know, with this model there are some limitations which would not be for scalable projects: 30,000 characters for the spreadsheet developers.google.com/sheets/api/guides/metadata. To publish my project as a web app it would not work since it is a backend and asynchronous bot. At the end of the day my final decision was to store my data in Firebase, with this library consulting and updating specific data is extremely easy github.com/grahamearley/FirestoreGoogleAppsScript — Julian Camilo Benavides Vanega

Julian Camilo Benavides Vanega Julian Camilo Benavides Vanega · Accepted Answer · 2020-06-16T02:59:12

The most practical and scalable solution in my case for having increasing information is not to use sheets as a database, firstly because it has a limit on records https://support.google.com/drive/answer/37603?hl=es

and limit in metadata that is needed for getDataFilter for condition by values

https://developers.google.com/sheets/api/guides/metadata

My quick and effective solution was to have my information repository in Firebase, and with this library it is extremely easy to make the connection, queries, overwrites, creations: https://github.com/grahamearley/FirestoreGoogleAppsScript

How to use getByDataFilter Sheets API v4 to get specific row data

1 Answers