What is proper way of searching a row by column value in spreadsheets API

Question

I am trying to search row by column value in spreadsheet (Google Sheets) API Google documentation having API for find & Replace but no API available for find/search.

Some approaches i already tried to achieve searching by value in spreadsheet.

1. Google Visualization API Query Language: this approach is returning rows matching values.

   Some issues in response:

   a) Not returning row number (No indexing)

   b) search is case sensitive

   Is it possible to get row number in result of this method?

2. Set column

    =ROW(INDIRECT(ADDRESS(MATCH("search_string",B:B,0),1)))

Got row number eg : 2

And then Get row by get value by range: https://sheets.googleapis.com/v4/spreadsheets/1Y65ZA5mhyImLL00yGR3lqwtbm6MgGLV0nV014fRXhts/values/A2:Z2

This is not a proper way for this i have to create a cell with Formula and then find the row by value in that cell and also clear sell after search done.

*This approach returns only one row.

3. DeveloperMetadataLookup: i don't know how to use this I created meta data on sheet with metadataKey : key1 metadataValue : value1

   Searching by spreadsheets.values.batchGetByDataFilter

    using dataFilter:DeveloperMetadataLookup{"metadataKey":"key1","metadataValue":"value1"}

This returning all rows in sheet i don't know how to find specific row with this method.

4. Google Apps Text Finder : i don't know if this is free to use with spreadsheet API.

5. Basic Filter: i read about this but don't know how to use it for searching.

6. Some old questions of same topic suggesting to fetch all rows and loop them to get row by value.

   Example: this will hang my server i don't want implement this loop.

    function find(value, range) {
      var data = range.getValues();
      for (var i = 0; i < data.length; i++) {
        for (var j = 0; j < data[i].length; j++) {
          if (data[i][j] == value) {
            return range.getCell(i + 1, j + 1);
          }
        }
      }
      return null;
    }

Conclusion:

1. Can anyone help me to make a proper way to searching a row by column value in sheets ?

2. Is it possible to search row by column value with (DeveloperMetadataLookup or Basic Filter) If yes then please suggest me proper way to use it.

3. Google Visualization API Query Language : is it possible to get row number ?

This is a example sheet

Column-> A, B, C

Row1->B-15, planY, 50
Row2->B-18, planZ, 80
Row3->B-19, planC, 70
Row4->B-21, planZ, 90

My requirement search column:B = value:planZ | (B=planZ)

Result should be similar to this :

Row:{
"2":["B-18","planZ","80"],
"4":["B-21","planZ","90"]
}

Answer 1

Tested This thread function with some modification for searching in spreadsheet and i got row number and row values (all cells of that row).

function doGet(e = {
    "parameter": {
        "value": "planc",
        "spreadsheetId": "1Y65ZA5mhyImLL00yGR3lqwtbm6MgGLV0nV014fRXhts",
        "column": "B",
        "headerStatus": true
    }
}) {
    var findText = e.parameter.value;
    var spreadsheetId = e.parameter.spreadsheetId;
    var column = e.parameter.column;
    var ss = SpreadsheetApp.openById(spreadsheetId);
    var ranges = ss.createTextFinder(findText).findAll();
    var headerStatus = e.parameter.headerStatus;
    if (headerStatus == true || headerStatus == "true") {
        var header = ss.getRange("A1:Z1").getValues()[0];
    }
    var res = ranges.map(r => ({
        row: r.getRow(),
        value: r.getSheet().getRange(`A${r.getRow()}:Z${r.getRow()}`).getValues()[0]
    }));
    var result = {};
    var allColumn = {
        "A": 0,
        "B": 1,
        "C": 2,
        "D": 3,
        "E": 4,
        "F": 5,
        "G": 6,
        "H": 7,
        "I": 8,
        "J": 9,
        "K": 10,
        "L": 11,
        "M": 12,
        "N": 13,
        "O": 14,
        "P": 15,
        "Q": 16,
        "R": 17,
        "S": 18,
        "T": 19,
        "U": 20,
        "V": 21,
        "W": 22,
        "X": 23,
        "Y": 24,
        "Z": 25
    };
    var columnIndex = allColumn[column];
    for (var key in res) {
        var getRow = res[key]["row"];
        var getValue = res[key]["value"];
        if (getValue[columnIndex].toLowerCase() == findText.toLowerCase()) {
            if (headerStatus == true || headerStatus == "true") {
                var headerValueObj = {};
                for (var i = 0; i < header.length; i++) {
                    if (header[i] !== "") {
                        var headerName = header[i];
                        headerValueObj[headerName] = getValue[i];
                    }
                }
                result[getRow] = headerValueObj;
            } else {
                result[getRow] = getValue;
            }
        }
    }
    return result;
}

Output result

"result": {
      "4": {
        "Price": 20,
        "heading4": "",
        "Id": "B-16",
        "Plan": "PlanC"
      },
      "7": {
        "Price": 8,
        "heading4": "",
        "Id": "B-24",
        "Plan": "PlanC"
      }
    }