使用TextFinder搜索范围比搜索整个工作表要慢 [英] Searching through a Range with TextFinder is slower than searching the entire Sheet

问题描述

我的工作表上有很多行-成千上万行.我想在特定列中搜索文本.我正在使用Range.createTextFinder，但是超时.如果工作表仅包含4行数据，它甚至会超时.

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?

更新:这是执行记录:

[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]

推荐答案

此处的范围设置为整个A列，包括数千个空行. TextFinder似乎是基于getLastRow()或类似的内容进行搜索的.对于工作表，getLastRow()返回包含内容的最后一行 的值，但是在范围上，它返回

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.

有两种方法可以解决此问题:在创建TextFinder之前指定一个较窄的范围

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.

或者，例如，您可以使用循环而不是TextFinder.

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;
  }
}

这篇关于使用TextFinder搜索范围比搜索整个工作表要慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！