Match Entire Cell for Multiple Find and Replace in Google Sheets via Google Apps Script

Question

First question here - I'm trying to use the Multiple Find and Replace in Google App Scripts for Google Sheets from this thread, however, I need to do an exact match on the cell. I did some research and see mentions of the class TextFinder and method matchExactCell, but I am stumped on where to add it.

When the script is run multiple times, then the first name in the replace is appended multiple times so the replaced cell reads: "John John John Smith" if script is run 3 times.

Any recommendations are appreciated! Thanks!

function runReplaceInSheet(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  //  get the current data range values as an array
  //  Fewer calls to access the sheet -> lower overhead 
  var values = sheet.getDataRange().getValues();  

  // Replace Names
  replaceInSheet(values, 'Smith', 'John Smith');
  replaceInSheet(values, 'Doe', 'Jane Doe');

  // Write all updated values to the sheet, at once
  sheet.getDataRange().setValues(values);
}

function replaceInSheet(values, to_replace, replace_with) {
  //loop over the rows in the array
  for(var row in values){
    //use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = values[row].map(function(original_value) {
      return original_value.toString().replace(to_replace,replace_with);
    });

    //replace the original row values with the replaced values
    values[row] = replaced_values;
  }
}

Martín · Accepted Answer · 2022-12-10T11:36:05.460

You can try with this little modification that does not use the function "replace" but compares the whole value with "replace_to" and returns "replace_with" if it's equal or "original_values" if it's not:

function replaceInSheet(values, to_replace, replace_with) {
  //loop over the rows in the array
  for(var row in values){
    //use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = values[row].map(function(original_value) {
      if(original_value == to_replace) {return replace_with}
      else {return original_value};
    });

    //replace the original row values with the replaced values
    values[row] = replaced_values;
  }
}

score 0 · Answer 2 · answered Dec 10 '22 at 09:06

As another approach, from I did some research and see mentions of the class TextFinder and method matchExactCell, but I am stumped on where to add it., if you want to use TextFinder, how about the following sample script?

Sample script:

function sample1() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var obj = [
    { find: "Smith", replacement: "John Smith" },
    { find: "Doe", replacement: "Jane Doe" }
  ];
  var range = sheet.getDataRange();
  obj.forEach(({ find, replacement }) => range.createTextFinder(find).matchEntireCell(true).replaceAllWith(replacement));
}

Although the process cost of TextFinder is low, in this case, the replacement is run in a loop. If you want to reduce the process cost more, how about the following sample script? In this sample, Sheets API is used. By this, the process cost is lower a little than that of the above. Before you use this script, please enable Sheets API at Advanced Google services.

function sample2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetId = ss.getSheetByName("Sheet1").getSheetId();
  var obj = [
    { find: "Smith", replacement: "John Smith" },
    { find: "Doe", replacement: "Jane Doe" }
  ];
  var requests = obj.map(({ find, replacement }) => ({ findReplace: { find, replacement, range: { sheetId }, matchEntireCell: true } }));
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

Match Entire Cell for Multiple Find and Replace in Google Sheets via Google Apps Script

2 Answers2

Sample script:

References: