'Most efficient way to build range from range based on content (e.g. formula)

What is the most efficient way with google script to build a range (newRange) from another range (baseRange) based on some criteria (e.g. the formula contains 'factorA') in the cells.

function test() {
  var spreadsheet = SpreadsheetApp.getActive(); 
  var sheet = spreadsheet.getActive();
  const lastRow = sheet.getLastRow();
  const maxColumn = sheet.getMaxColumns();
  baseRange = sheet.getRange(1, 1, maxColumn, lastRow);
  criteria = "factorA";

...

};

The objective is to build newRange from baseRange with only those cells containing 'factorA' in the formula.

What is the efficient way of doing this without looping to death if the sheet is large?

I was unable to figure out better alternatives, but got this working:

function test() {
  var spreadsheet = SpreadsheetApp.getActive(); 
  var sheet = spreadsheet.getActive();
  const lastRow = sheet.getLastRow();
  const maxColumn = sheet.getMaxColumns();
  baseRange = sheet.getRange(1, 1, maxColumn, lastRow);
  criteria = "factorA";

  for( var i = 0; i < baseRange.getLastRow(); i++){
    for( var j = 0; j < baseRange.getLastColumn(); j++){    
      var notation = baseRange.getCell(i+1,j+1).getA1Notation();
      var origFormula = baseRange.getCell(i+1,j+1).getFormula();      
      if(origFormula.toString().indexOf(prefixString) > -1){

        do a bunch of things with criteria to determine modifiedFormula

        var cell = baseRange.getCell(i+1,j+1);
        cell.setFormula(modifiedFormula);
      }
    }
  }
};

but is - as I feared - slow!

Is there a better (=faster) way?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source