1

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?

Connor
  • 867
  • 7
  • 18
  • See [tag info page](https://stackoverflow.com/tags/google-apps-script/info) for best practices, official documentation, free resources and more details. – TheMaster Aug 03 '22 at 20:21

2 Answers2

2

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 
}
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Why have you made all those things const? Is that standard practice if you can fairly assume they won't be altered? Also, the call `.getRow()` is that a call to the sheet also or not? – Connor Aug 03 '22 at 20:36
  • @Connor See https://stackoverflow.com/questions/40070631/ Why else would you use `const`? Also, If you know things aren't going to change, why use `var` or `let`. I think `.getRow` value is already present in `range` object. The actual IO call checking the ss would be `TextFinder` call, but [it is faster in it's own way](https://stackoverflow.com/a/44563639) – TheMaster Aug 03 '22 at 20:41
  • I realise the call to the sheet is probably the biggest time waster, however, I need to make sure data not linked to a particular ID is left unseen by the user and never loaded into their page. Are the `.gs` scripts of a web app exposed to users or are they considered server side? – Connor Aug 03 '22 at 20:44
  • 1
    @Connor Depends on how it is published. If you publish it to execute as "Me", "anyone" can access, it doesn't need to be shared. If not, you have to share the script. – TheMaster Aug 03 '22 at 20:47
  • 1
    @Connor They are not exposed in the sense, they can access the data, but that they have to have view access to the server side code, but not the values of the code. There's a `google` object loaded client side. It can be used to call all the functions of server side. But you can use private functions to hide implementation details/data objects. But if you enable restrictions on access, you have to share the code, but the data that will be loaded in the code. – TheMaster Aug 03 '22 at 20:56
1

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:

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
9 10 9 4 14 14 8 12 6 17
9 16 2 19 12 10 4 1 10 2
10 14 17 11 7 0 5 19 10 17
14 14 2 7 8 19 12 17 2 0
7 7 10 4 4 19 15 16 14 18
13 11 13 6 3 9 10 15 3 12
17 1 2 13 11 8 18 19 10 9
4 0 12 12 0 10 8 3 19 15
0 5 6 13 7 19 4 2 10 19
Cooper
  • 59,616
  • 6
  • 23
  • 54