How to perform bulk find and replace operations in Google Sheets using multiple search terms

Other Questions Google Sheets
1

I’m currently working on a task in Google Sheets where I aim to replace several specific words with a single new word. To clarify what I’m trying to do:

I would like to go through my spreadsheet and identify cells that include “apple” or “orange” and change both of these terms into the word “fruit”. At present, I can only replace one word at a time, so I need a way to efficiently manage multiple keywords.

Here is a basic example of the kind of functionality I’m seeking:

function replaceMultipleTerms() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  
  var searchTerms = ["apple", "orange"];
  var replacement = "fruit";
  
  // Need logic to check for multiple terms here
}

Is there a method to tweak a script such that it checks for several keywords concurrently and replaces them all with the same word? I would greatly appreciate any advice!

2

yep, regex is key! just use new RegExp('apple|orange', 'gi') to match em. loop through your cells and swap those words for ‘fruit’. it’s quick and saves so much hassle.

3

You can also combine all your search terms into one regex pattern upfront - it’s way more efficient than running multiple loops. Here’s what I’ve been using for bulk replacements:

function replaceMultipleTerms() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  
  var searchTerms = ["apple", "orange"];
  var replacement = "fruit";
  
  var pattern = new RegExp(searchTerms.join('|'), 'gi');
  
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (typeof values[i][j] === 'string') {
        values[i][j] = values[i][j].replace(pattern, replacement);
      }
    }
  }
  
  range.setValues(values);
}

The trick is creating one regex pattern that matches all terms at once instead of running separate replacements. Cuts down processing time big time with larger datasets. Just escape any special regex characters in your search terms if needed.

4

You can extend your function by looping through each cell and checking against your search terms array. Here’s what worked for me with a similar problem:

function replaceMultipleTerms() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  
  var searchTerms = ["apple", "orange"];
  var replacement = "fruit";
  
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      var cellValue = values[i][j].toString();
      for (var k = 0; k < searchTerms.length; k++) {
        cellValue = cellValue.replace(new RegExp(searchTerms[k], 'gi'), replacement);
      }
      values[i][j] = cellValue;
    }
  }
  
  range.setValues(values);
}

This hits each cell individually and applies all replacements one by one. Performance is fine for moderate datasets, but you might need batch processing for huge sheets.