Ad

What's The Fastest Way To Create An Array From Another Array In App Scripts?

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?

Ad

Answer

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 
}
Ad
source: stackoverflow.com
Ad