Google App Script: Find & Replace for specific columns

byMR

January 13, 2022

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);
}

>Solution :

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 becomes 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);
  }