Google Apps Script: using createTextFinder() and textFinder() function to search for a string
Searching for a string in a large spreadsheet is done almost as often as using formulas and performing various calculations. In order to search for a string in a spreadsheet you will need to learn to use the two functions together. First you will create a text finder object by calling createTextFinder(). Function createTextFinder will return a TextFinder object, which you will then use findNext() to search for the first occurrence of the string.
The findRowsByString() function accepts two parameters: first the string to search for and second the sheet to search in. What we want to do is search for every occurrence of the string, gathering each found row and save the rows in an array. The return array will be used for later processing after all the rows that contain the string have been found.
We first create a textFinder object by calling
var textFinder = searchSheet.createTextFinder(searchStr);We then use the textFind to actually find the first occurrence of the string, which will return a Range object.
var foundRange = textFinder.findNext();If this Range object is not null we can continue searching for the next occurrence of the string again. We will continue searching until no strings are found and the Range object is null.
Putting it all together:
function findRowsByString(searchStr, searchSheet) {
var foundRow = [];
var textFinder = searchSheet.createTextFinder(searchStr);
var foundRange = textFinder.findNext();
while ( null != foundRange ) {
foundRow.push(foundRange.getRow());
foundRange = textFinder.findNext();
};
return(foundRow);
};
function mainSearch() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var searchStr = "dog";
var foundRows = findRowsByString(searchStr, sheet);
Logger.log(foundRows);
};