CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets-api
Julian Camilo Benavides Vanega
Julian Camilo Benavides Vanega

Reputation: 67

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

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": "julian@domain3.com"
      }
    }
  ]
};

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",
      "julian@domain3.com",
      "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": "julian@domain3.com"
      }
    }
  ]
};

  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

Upvotes: 4

Views: 3048

Answers (1)

Julian Camilo Benavides Vanega
Julian Camilo Benavides Vanega

Reputation: 67

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

Upvotes: 1

Related Questions