Here is the problem & logic for the find & replace script I am using.

• Search Sheet for to_replace string.
  • If found, replace to_replace with replace_with.
  • If not found, replace to_replace with to_replace // This is not needed, and causes problems (it replaces all formulas, and replaces it with a string).

My Objective:

I would like the script to only replace cells that match to_replace, and ignore every other cell.

My Rookie Solution:

Exclude specific columns in the foruma by eliminating column C from array using script from here. (only find & replace within Column B & D).

Here is the modified code I added in My Current Script...

const range = sheet.getRange('B2:D'+lastRow).getValues();
range.forEach(a => a.splice(1, 1)); //removes column C.

But I get the error: "TypeError: var data = range.getValues(); is not a function"

Question

Can you help me troubleshoot my rookie solution, or teach me a better way to solve this problem?

---

My current script

function findAndReplace(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var lastRow = sheet.getLastRow()
  var lastColumn = sheet.getLastColumn()
  // var range = sheet.getRange(1, 1, lastRow, lastColumn) //REMOVED - Searches all columns.

  const range = sheet.getRange('B2:D'+lastRow).getValues(); //ADDED - Searches only B & D
  range.forEach(a => a.splice(1, 1)); //ADDED - Searches only B & D
     
  var to_replace = "TextToFind";
  var replace_with = ""; //Leave blank to delete Text, or enter text in quotes to add string.
  var data  = range.getValues();
 
    var oldValue="";
    var newValue="";
    var cellsChanged = 0;
 
    for (var r=0; r<data.length; r++) {
      for (var i=0; i<data[r].length; i++) {
        oldValue = data[r][i];
        newValue = data[r][i].toString().replace(to_replace, replace_with);
        if (oldValue!=newValue)
        {
          cellsChanged++;
          data[r][i] = newValue;
        }
      }
    }
    range.setValues(data);
}

Here is the problem & logic for the find & replace script I am using.

• Search Sheet for to_replace string.
  • If found, replace to_replace with replace_with.
  • If not found, replace to_replace with to_replace // This is not needed, and causes problems (it replaces all formulas, and replaces it with a string).

My Objective:

I would like the script to only replace cells that match to_replace, and ignore every other cell.

My Rookie Solution:

Exclude specific columns in the foruma by eliminating column C from array using script from here. (only find & replace within Column B & D).

Here is the modified code I added in My Current Script...

const range = sheet.getRange('B2:D'+lastRow).getValues();
range.forEach(a => a.splice(1, 1)); //removes column C.

But I get the error: "TypeError: var data = range.getValues(); is not a function"

Question

Can you help me troubleshoot my rookie solution, or teach me a better way to solve this problem?

---

My current script

function findAndReplace(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var lastRow = sheet.getLastRow()
  var lastColumn = sheet.getLastColumn()
  // var range = sheet.getRange(1, 1, lastRow, lastColumn) //REMOVED - Searches all columns.

  const range = sheet.getRange('B2:D'+lastRow).getValues(); //ADDED - Searches only B & D
  range.forEach(a => a.splice(1, 1)); //ADDED - Searches only B & D
     
  var to_replace = "TextToFind";
  var replace_with = ""; //Leave blank to delete Text, or enter text in quotes to add string.
  var data  = range.getValues();
 
    var oldValue="";
    var newValue="";
    var cellsChanged = 0;
 
    for (var r=0; r<data.length; r++) {
      for (var i=0; i<data[r].length; i++) {
        oldValue = data[r][i];
        newValue = data[r][i].toString().replace(to_replace, replace_with);
        if (oldValue!=newValue)
        {
          cellsChanged++;
          data[r][i] = newValue;
        }
      }
    }
    range.setValues(data);
}

• javascript
• arrays
• google-apps-script
• google-sheets

Share Improve this question Follow asked Jan 13, 2022 at 8:21 JamesReed68JamesReed68 41944 silver badges2020 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 5

From teach me a better way to solve this problem, in your situation, I thought that when TextFinder is used, the process cost might be able to be reduced. When TextFinder is used for achieving your goal, it bees as follows.

Sample script:

function myFunction() {
  var to_replace = "TextToFind";
  var replace_with = ""; //Leave blank to delete Text, or enter text in quotes to add string.

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var lastRow = sheet.getLastRow();
  var ranges = ['B2:B' + lastRow, 'D2:D' + lastRow];
  sheet.getRangeList(ranges).getRanges().forEach(r => 
    r.createTextFinder(to_replace).matchEntireCell(true).replaceAllWith(replace_with)
  );
}

Note:

• If you want to replace the part of cell value, please modify r.createTextFinder(to_replace).matchEntireCell(true).replaceAllWith(replace_with) to r.createTextFinder(to_replace).replaceAllWith(replace_with).

• As an additional modification, if your script is modified, how about the following modification?

    function findAndReplace() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
      var lastRow = sheet.getLastRow()
      var range = sheet.getRange('B2:D' + lastRow);
      var data = range.getValues();
      var to_replace = "TextToFind";
      var replace_with = ""; //Leave blank to delete Text, or enter text in quotes to add string.
      for (var r = 0; r < data.length; r++) {
        for (var i = 0; i < data[r].length; i++) {
          var value = data[r][i].toString();
          if (i != 1 && value.includes(to_replace)) {
            data[r][i] = data[r][i].replace(to_replace, replace_with);
          }
        }
      }
      range.setValues(data);
    }
  

References:

• createTextFinder(findText)
• Class TextFinder