Google App Scripts find text in spreadsheet and return location index

Question

I am a novice here to google app scripts and my JavaScript is also not very strong, but neither of these seem to be the problem here as my code works the first time I run it but then when I try to call it again it fails.

Simply I am trying to have a function that will dynamically find a given text in a given range. While it looks like there might be a built in package that does this I cannot figure out how to implement it. And the documentation is not helpful for someone new.

Option 1: was to implement the following: https://developers.google.com/apps-script/reference/spreadsheet/text-finder#findAll()

Since that has not been sucessful in finding out how to do it I moved to creating the following simple two functions, Option 2:

function findIndexRow(range,fText){
  for(var i = 0; i<range.length;i++){
    for(var j = 0; j<range.length;j++){
      if(range[i][j] == fText){
        var fTextRow = i+1;
        var fTextCol = j+1;
      }
    }  
  }
  return fTextRow
}

function findIndexCol(range,fText){
  for(var i = 0; i<range.length;i++){
    for(var j = 0; j<range.length;j++){
      if(range[i][j] == fText){
        var fTextRow = i+1;
        var fTextCol = j+1;
      }
    }  
  }
  return fTextCol
}

It takes in a range that I defined like:

var sheet = SpreadsheetApp.openById('the-gsheet-id');
var CurrSheet = sheet.getSheetByName('Sheet1');
var SHTvalues = CurrSheet.getDataRange().getValues();

So the above works when I call it once in my main code but the second time it returns null, help here as to why re calling the same function does not work.

var text1Row = findIndexRow(SHTvalues,"text1");
var text1Col = findIndexCol(SHTvalues,"text1");
Logger.log(text1Row)
Logger.log(text1Col)
var text2Row = findIndexRow(SHTvalues,"text2");
var text2Col = findIndexCol(SHTvalues,"text2");
Logger.log(text2Col)
Logger.log(text2Row)

I can't understand why my logs return the correct values for text1Row and text1Col but when it is called a second time the text2Row and text2Col both return null

Answer 1

I believe your goal as follows.

• You want to search a text value from a sheet in the Google Spreadsheet, and want to retrieve the row and column numbers of the found values.
• You want to achieve this using TextFinder.

For this, how about this answer?

Sample script:

var findText = "text1";

var sheet = SpreadsheetApp.openById('the-gsheet-id');
var CurrSheet = sheet.getSheetByName('Sheet1');
var SHTvalues = CurrSheet.createTextFinder(findText).findAll();
var result = SHTvalues.map(r => ({row: r.getRow(), col: r.getColumn()}));
console.log(result)

Note:

• About my logs return the correct values for text1Row and text1Col but when it is called a second time the text2Row and text2Col both return null in your script, if there are the values of text1 and text2 in Sheet1, text1Row, text1Col, text2Col and text2Row has the values. If only the value of text1 is put in Sheet1, text1Col and text2Col has the values. But text2Col and text2Row has no values (null). Please be careful this.

  • But in this case, when 2 values of `text1 are put to the cells "A1" and "A2", only "A2" is returned. Also please be careful this.

• In this sample script, please enable V8.

References:

• createTextFinder() in Class Sheet
• Class TextFinder

Answer 2

Here's a script that I used for searching through my spreadsheets when I'm having trouble finding the sheet I want. It does read another sheet to get a list of spreadsheets to search through.

function regexSearch(sObj) {
  var ass=SpreadsheetApp.getActive();
  var startRow=2;
  var msrsh=ass.getSheetByName('MultiSearchResults');
  msrsh.clearContents();
  msrsh.appendRow(['Path','FileName','FileId','SheetName','CellA1Notation','Value','Pattern']);
  msrsh.activate();
  var sh=ass.getSheetByName('SelectedSpreadsheets');
  var hA=sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];
  var getArrayIndex={};
  hA.forEach(function(e,i){getArrayIndex[e]=i;});
  var rg=sh.getRange(startRow,1,sh.getLastRow()-startRow+1,sh.getLastColumn());
  var ssA=rg.getValues();
  var matches='';
  var n=0
  for(var k=0;k<ssA.length;k++) {
    var fileid=ssA[k][getArrayIndex['FileId']];
    var filename=ssA[k][getArrayIndex['FileName']];
    var filepath=getFilePathFromId(ssA[k][getArrayIndex['FileId']]);
    //Logger.log(fileid);
    var ss=SpreadsheetApp.openById(fileid);
    Logger.log(sObj.pattern);
    var tf=ss.createTextFinder(sObj.pattern).useRegularExpression(true);
    var all=tf.findAll();
    for(var i=0;i<all.length;i++) {
      if(i==0)n++;
      matches+=Utilities.formatString('<br /><b>Path:</b> %s <b>Sheet:</b> %s <b>Cell:</b> %s <b>Value:</b> %s<hr width="100%"/>',filepath,all[i].getSheet().getName(),all[i].getA1Notation(),all[i].getValue());
      msrsh.appendRow([filepath,filename,fileid,all[i].getSheet().getName(),all[i].getA1Notation(),all[i].getValue(),sObj.pattern]);
    }
  }
  if(matches) {
    sObj.matches=matches;
    sObj.message=Utilities.formatString('<p>Pattern %s was found in %s spreadsheet out of a total of %s</p>',sObj.pattern,n,ssA.length);
  }else{
    sObj.message=Utilities.formatString('No Matches found for %s',sObj.pattern);
  }
  return sObj;
}