I'm using the TextFinder class in Google App Scripts to find cells that have a particular number in them. I believe that leaves me with a RangeList object, which seems to be a kind of Javascript Array, although I'm not sure.

I'd like to perform the getRow() operation on each Range in the list so that I can select the whole row in which that number occurs. Currently, this is the code I'm using to do this:

  var idRowRanges = [];
  for (cell of idCells) {
    idRowRanges.push(cell.getRow());
    var idRange = sheet.getRange(cell.getRow(), 1, 1, sheet.getLastColumn());
    var rowValues = idRange.getValues();
  }

Coming from a Python background this looks very slow to me, is there a faster way of performing an operation like this?

CodePudding user response：

Ideally, you'd want to avoid touching the spreadsheet except twice for I/O(once for each). In this case, if you assume getRow() doesn't check the spreadsheet, the reason why it is slow, is because of your repeated calls to getValues(). Refactoring your code, you'd get:

const fullData = sheet.getDataRange().getValues();//I/O call
const idRowRanges = [];
for (cell of idCells) {
  const thisIdRow = cell.getRow(),
     thisIdRowValues = fullData[thisIdRow];//1D array; No call to ss 
}

CodePudding user response：

Using textfinder

function find(n=5) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const rg = sh.getDataRange();
  let ns = rg.createTextFinder(n).findAll();
  let list = ns.map(n => n.getA1Notation());
  Logger.log(JSON.stringify(list));
}

Execution log
2:41:09 PM  Notice  Execution started
2:41:10 PM  Info    ["E1","G4","G6","H7","J9","B10"]
2:41:11 PM  Notice  Execution completed

Sheet0: