Searching through a Range with TextFinder is slower than searching the entire Sheet

user_3383907 0
Viewed 10858

I have a sheet with a lot of rows -- thousands. I want to search a specific column for text. I am using Range.createTextFinder but it times out. It even times out if the Sheet only has 4 rows of data.

// This is very slow
var found = sheet.getRange("A:A").createTextFinder("dog").matchCase(false).findNext();

If I search the entire sheet it is pretty fast but I want to only check a specific column.

// This works faster but it searches the entire sheet
var found = sheet.createTextFinder("dog").matchCase(false).findNext();

Is there a faster way to find the first row that has a string in a specific column?

Update: Here is the execution transcript:

[19-06-09 14:16:48:430 EDT] Starting execution
[19-06-09 14:16:48:513 EDT] SpreadsheetApp.openById([redacted]) [0.071 seconds]
[19-06-09 14:16:48:513 EDT] Spreadsheet.getSheetByName([Sheet2]) [0 seconds]
[19-06-09 14:16:48:514 EDT] Sheet.createTextFinder([b]) [0 seconds]
[19-06-09 14:16:48:514 EDT] TextFinder.matchCase([false]) [0 seconds]
[19-06-09 14:16:48:595 EDT] TextFinder.findNext() [0.08 seconds]
[19-06-09 14:16:48:596 EDT] Sheet.getRange([A:A]) [0 seconds]
[19-06-09 14:16:48:596 EDT] Range.createTextFinder([b]) [0 seconds]
[19-06-09 14:16:48:597 EDT] TextFinder.matchCase([false]) [0 seconds]
[19-06-09 14:17:21:064 EDT] TextFinder.findNext() [32.467 seconds]
[19-06-09 14:17:21:072 EDT] Execution failed: Service error: Spreadsheets (line 103, file "main") [32.626 seconds total runtime]
google-apps-script google-sheets
1 Answers

The Range here is set to be the entire A column, including thousands of empty rows. TextFinder appears to search based on getLastRow() or something very much like it. For the sheet, getLastRow() returns the value of the last row with content, but on a range, it returns the last row within that range.

There are two ways to get around this: Specify a narrower range before creating your TextFinder

var found = sheet.getRange(1,1,sheet.getLastRow()).createTextFinder("dog").matchCase(false).findNext();

Which will search only the first column, starting from the first row, and concluding at the last row with content.

Alternatively, you could use a loop rather than a TextFinder, e.g.

var column = sheet.getRange("A:A").getValues();
var foundIndex;
for (var i = 0; i < column.length; i++){
  if (column[i][0] === "dog") {
    foundIndex = i;
    break;
  }
}
user_109966250
Related