Altering find and replace Google Sheets script

0
votes

I have the following script, though I would like to change it to only find and replace on a specific sheet (Sheet4!A:AB).

How would I go about doing this:

function fandr() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getActiveSheet();
  var r=s.getDataRange();
  var vlst=r.getValues();
  var i,j,a,find,repl;
  find="abc";
  repl="xyz";
  for (i in vlst) {
    for (j in vlst[i]) {
    a=vlst[i][j];
    if (a==find) vlst[i][j]=repl;
  }
 }
  r.setValues(vlst);
}
1
javascriptgoogle-sheets

1 Answers

0
votes

I think that pnuts's comment is helpful for your situation. If you are still looking for your solution, how about this answer? You want to find and replace the values in Sheet4!A:AB. If my understanding is correct, I would like to propose 2 patterns. I think that there are several solution for your situation. So please think of this as one of them.

Pattern 1

This modification is a simple modification. It modified the range for retrieving values to Sheet4!A:AB.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var r = ss.getRange("Sheet4!A:AB");
var vlst = r.getValues();
var i, j, a, find, repl;
find = "abc";
repl = "xyz";
for (var i = 0; i < vlst.length; i++) {
  for (var j = 0; j < vlst[i].length; j++) {
    a = vlst[i][j];
    if (a == find) vlst[i][j] = repl;
  }
}
r.setValues(vlst);

Pattern 2

In the case of pattern 1, the values retrieved by getRange("Sheet4!A:AB") includes the empty rows. By this, the search speed becomes slow. So in this pattern 2, the data range of "A1:AB" is retrieved using getLastRow().

var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("Sheet4");
var r = s.getRange("A1:AB" + s.getLastRow());
var vlst = r.getValues();
var i, j, a, find, repl;
find = "abc";
repl = "xyz";
for (var i = 0; i < vlst.length; i++) {
  for (var j = 0; j < vlst[i].length; j++) {
    a = vlst[i][j];
    if (a == find) vlst[i][j] = repl;
  }
}
r.setValues(vlst);

References :

If I misunderstand what you want, please tell me. I would like to modify it.