Return Row Data from Spreadsheet having specified string without looping through all Rows

Question

I have a task at hand wherein I need to return the entire Row Data containing a user-defined string.

One way of achieving it is looping through all the rows, but this works only when you know in which column to search, as shown using the code given below.

var sheetData = SpreadsheetApp.getActiveSpreadsheet().getDataRange().getValues();
var searchString = "testSearch";

for(var i=0;i<sheetData.getLastRow();i++)
{
  //assuming we know that the search string is going to be in Column 2
  if(sheetData[i][1].search(searchString)!=-1)
  {
    var rowData = sheetData[i];
    return rowData;
  }
}

So my question is, is there any way in which we can achieve this, without having to loop through all rows one by one?

To make the problem statement more clear, I wish to achieve something like the 'Find' feature as demonstrated in the image below:

This would make it very easy to skim through huge data spread across multiple sheets/spreadsheets.

Note: I am searching for a solution to this using Google Apps Script.

Answer 1

Here is code that gets the data from the row with the match. It can be a partial match. You do not need to know the number of columns or rows. You do not need to know which column to look in.

To make this code work, replace Put Sheet Tab Name Here with the name of the sheet tab to use. The searchString can be passed into the function. If nothing is passed in, then the code uses a hard coded value for the search.

function findRowOfSearchString(searchString) {
  var arrIndexOfAllMatches,dataAsStringWithBrackets,dataRange,i,
      isThereA_Match,j,ll,L,L2,matchOfAllInnerBrackets,numberOfColumns,numberOfRows,
      rowCutOff,rowsOfMatchedData,sh,sheetData,ss,thisIndex,thisMatchIndex,thisRow,thisRowData;

  ll = function(a,b) {
    Logger.log(a + ": " + b)
  }

  if (!searchString) {
    searchString = "testSe";
  }

  ss = SpreadsheetApp.getActiveSpreadsheet();
  sh = ss.getSheetByName('Put Sheet Tab Name Here');//

  dataRange = sh.getDataRange();

  numberOfColumns = dataRange.getNumColumns();
  numberOfRows = dataRange.getNumRows(); //changed 'getNumColumns' to 'getNumRows'

  sheetData = dataRange.getValues();//Get a 2D array of all sheet data

  dataAsStringWithBrackets = JSON.stringify(sheetData);
  //ll('dataAsStringWithBrackets: ',dataAsStringWithBrackets)

  isThereA_Match = dataAsStringWithBrackets.indexOf(searchString);
  //ll('isThereA_Match: ',isThereA_Match)

  if (isThereA_Match === -1) {return;}//There is no match - stop

  arrIndexOfAllMatches = [];

  L = dataAsStringWithBrackets.length;
  //ll('L',L)

  thisMatchIndex = 0;

  for (i=0;i<L;i++) {
    //ll('i',i)
    thisMatchIndex = dataAsStringWithBrackets.indexOf(searchString,thisMatchIndex + 1);

    //ll('thisMatchIndex',thisMatchIndex)

    if (thisMatchIndex === -1) {//No more matches were found
      //ll('No more matches found',thisMatchIndex)
      break;
    }

    arrIndexOfAllMatches.push(thisMatchIndex);
  }

  //ll('arrIndexOfAllMatches',arrIndexOfAllMatches)

  matchOfAllInnerBrackets = [];

  thisMatchIndex = 0;

  for (i=0;i<L;i++){
    thisMatchIndex = dataAsStringWithBrackets.indexOf("],[",thisMatchIndex + 1);

    //ll('thisMatchIndex',thisMatchIndex)

    if (thisMatchIndex === -1) {//No more matches were found
      //ll('No more matches found',thisMatchIndex)
      break;
    }

    matchOfAllInnerBrackets.push(thisMatchIndex);
  }

  ll('matchOfAllInnerBrackets',matchOfAllInnerBrackets)

  rowsOfMatchedData = [];
  L = arrIndexOfAllMatches.length;
  L2 = matchOfAllInnerBrackets.length;

  for (i=0;i<L;i++){
    thisIndex = arrIndexOfAllMatches[i];
    ll('thisIndex: ' ,thisIndex)

    for (j=0;j<L2;j++){
      rowCutOff = matchOfAllInnerBrackets[j];
      ll('rowCutOff: ',rowCutOff)

      if (rowCutOff > thisIndex) {
        ll('greater than: ' ,thisIndex > rowCutOff)
        thisRow = j+1;
        ll('thisRow: ', (thisRow))
        rowsOfMatchedData.push(thisRow)
        break;
      }
    }
  }

  ll('rowsOfMatchedData: ',rowsOfMatchedData)

  L = rowsOfMatchedData.length;

  for (i=0;i<L;i++){
    thisRowData = sh.getRange(rowsOfMatchedData[i], 1, 1, numberOfColumns).getValues();
    ll('thisRowData: ',thisRowData)
  }
}