Apps Script, Need function to only work on certain cells but can't get code to work-CodePudding

Need help please. Below code works (very slowly) but needs extra function to speed things up.

Purpose of current code: looks at multiple sheets to perform a "find and replace" against a dictionary (the dictionary is pretty big but reduced it for here).

The issue: It runs really slow because it looks through every single cell.

Solution needed please: Need to tell code to only look at L2:P100 on all the sheets (there are about 15 but 2 here for demonstration).

I've spent all day trying to make it work and trying lots of code from here but keep getting dataRange related issues. Any ideas what can be done to have it only look at certain cells i.e. a range of cells? I thought it would be as straightforward as dataRange(L2:P100); but get null errors, undefined etc. I know it's a bit messy and probably has some unneeded variables... have been trying out so many things it's kind of gotten a bit messy as a result. Don't feel bad for pointing these out. I'm sort of new to coding, have some practice but not a lot, so I'm not fully understanding of things just yet. But trying to learn. Thank you!

function FindReplace() {

var r,sheet,sheets,ss;
var data_range = SpreadsheetApp.getActiveSheet().getDataRange();
var num_rows = data_range.getNumRows();
var num_columns = data_range.getNumColumns();
var sheets = ["firstsheet","secondsheet"];
var ss = SpreadsheetApp.getActiveSpreadsheet();
var r = 0;
var startRow = 1;
var startColumn= 1;
var lastRow = 100;
var lastColumn = 15;
var numRows = lastRow-startRow 1;
var numCols = lastColumn-startColumn 1;
var find_replace = {
     /* Dictionary */
    "AA": "Done",
    "BB": "Check",
    "CC": "Complete",
  }

for (r=0;r<5;r  ){
  sheet=ss.getSheetByName(sheets[r]);
  Object.keys(find_replace).map(function(find) {
var replace = find_replace[find];
for (var row=1; row <= num_rows; row  ) {
 for (var col=1; col <= num_columns; col  ) {
    var value = data_range.getCell(row, col).getValue();
    if (data_range.getCell(row, col).getFormula()) {continue;}
    try {
       value = value.replace(find, replace);
       data_range.getCell(row, col).setValue(value);
    }
    catch (err) {continue;}
          }
        }
  });


}

}

CodePudding user response：

I think this maybe what you were trying to accomplish

function FindReplace() {
  const ss = SpreadsheetApp.getActive();
  var shts = ["firstsheet", "secondsheet"];
  var rObj = {pA:["AA","BB","CC"], "AA": "Done", "BB": "Check", "CC": "Complete" };
  sht.forEach(sh => {
    rObj.pA.forEach(p => {
      sh.getDataRange().createTextFinder(p).replaceAllWith(rObj[p])
    });
  });
}

References:

TextFinder

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.
You want to replace the values in the range of "L2:P100".

Modification points:

In your script, it seems that var sheets = ["firstsheet", "secondsheet"]; is not used. Because sheet = ss.getSheetByName(sheets[r]); in the loop is not used. In your script, only the active sheet is used.
When getValue, setValue, getFormula are used in a loop, the process cost will be high.
- I think that the reason for Below code works (very slowly) is due to this.
In your script, it seems that all cells in a sheet are used. In order to use the limited range, it is required to be modified.

When these points are reflected in a Google Apps Script, how about the following modification?

Modified script:

Please copy and paste the following script to the script editor and save the script. And, please enable Sheets API at Advanced Google services. And, please run FindReplace2. By this, the script is run.

function FindReplace2() {
  const sheetNames = ["firstsheet", "secondsheet"]; // Please set the sheet names you want to use.
  const range = "L2:P100"; // Please set the range you want to use.
  const find_replace = { // This is from your script.
    "AA": "Done",
    "BB": "Check",
    "CC": "Complete",
  };

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tempRange = ss.getRange(range);
  const startRowIndex = tempRange.getRow() - 1;
  const endRowIndex = startRowIndex   tempRange.getNumRows();
  const startColumnIndex = tempRange.getColumn() - 1;
  const endColumnIndex = startColumnIndex   tempRange.getNumColumns();
  const ar = Object.entries(find_replace);
  const requests = sheetNames.flatMap(n => {
    const sheetId = ss.getSheetByName(n).getSheetId();
    return ar.map(([find, replacement]) => (
      {
        findReplace: {
          find,
          replacement,
          range: { sheetId, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex },
          matchEntireCell: true,
        },
      })
    );
  });
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

When this script is run, the cell values of range of sheetNames are replaced using find_replace.