Find and replace text in Google Sheets using TextFinder and Apps Script

If you regularly use spreadsheets, you've probably used the "find and replace" feature at some point. This feature lets you find text in your spreadsheet and optionally replace some or all occurrences of that text with some other value. To manually find and replace text, select Edit —> Find and replace.

You can also use Apps Script to programmatically find and replace text. This is especially useful if you're automating some process where you need to "clean the data" in your spreadsheet before it is used for further processing.

Spreadsheets can have all sorts of inconsistencies that make it difficult to analyze the data in them. For example, a spreadsheet containing user entered data might contain "CA", "Ca", "California", and "CALIFORNIA", that all represent the state of California in the United States.

Leaving the data as-is could lead to incorrect inferences since formulas consuming this data might expect states to be represented using the two-letter abbreviations that USPS uses. Therefore, to produce correct results, you might want to replace "Ca", "California" and "Cali" with "CA" before performing the analysis.

In this tutorial, I will show you how to use Apps Script to automate this "find and replace" functionality.

Prerequisites

This tutorial assumes that you're familiar with the basics of Apps Script and Google Sheets. If you're new to Apps Script, I've created a series of introductory tutorials that will help you learn coding using Google Sheets and Apps Script.

Find and replace text in Google Sheets using TextFinder

Google Apps Script provides a TextFinder feature that makes it easy to programmatically find and replace text in your Google Sheets spreadsheet. Here is a simple example that illustrates how it works. The function searchAndReplace() accepts the search term and its replacement as inputs and then replaces all occurrences of the search term with the value that should replace it.

function searchAndReplace(searchTerm, replacement) {
  SpreadsheetApp.getActive()
   .createTextFinder(searchTerm)
   .matchEntireCell(true)
   .matchCase(true)
   .matchFormulaText(false)
   .ignoreDiacritics(false)
   .replaceAllWith(replacement);
}

SpreadsheetApp.getActive()

SpreadsheetApp.getActive()
   .createTextFinder(searchTerm)

SpreadsheetApp.getActive()
   .createTextFinder(searchTerm)
   .matchEntireCell(true)

SpreadsheetApp.getActive()
   .createTextFinder(searchTerm)
   .matchEntireCell(true)
   .matchCase(true)

SpreadsheetApp.getActive()
   .createTextFinder(searchTerm)
   .matchEntireCell(true)
   .matchCase(true)
   .matchFormulaText(false)

SpreadsheetApp.getActive()
   .createTextFinder(searchTerm)
   .matchEntireCell(true)
   .matchCase(true)
   .matchFormulaText(false)
   .ignoreDiacritics(false)

SpreadsheetApp.getActive()
   .createTextFinder(searchTerm)
   .matchEntireCell(true)
   .matchCase(true)
   .matchFormulaText(false)
   .ignoreDiacritics(false)
   .replaceAllWith(replacement);

Find all occurrences of a search term in Google Sheets using the findAll() method

You can use the findAll() method of the text finder to find all occurrences of the search term. The code below finds cells containing "C" in the spreadsheet and sets their background color to yellow.

//Find cells that contain "C"
let ranges = SpreadsheetApp.getActive()
   .createTextFinder("C")
   .matchEntireCell(true)
   .matchCase(true)
   .matchFormulaText(false)
   .ignoreDiacritics(true)
   .findAll();

//Set the background colors of those cells to yellow.
ranges.forEach(function (range) {
    range.setBackground("yellow");
  });

Configure the scope where the search should be performed

You can configure where TextFinder should look for the search term. You can configure the scope of the search to be the entire spreadsheet, a specific sheet within it or even a specific range. In order to configure this, simply create the text finder object from the scope it should use.

Search for text in the entire spreadsheet

Here the createTextFinder() method is invoked on the spreadsheet object so the search will be performed on the entire spreadsheet.

let textFinder = SpreadsheetApp.getActive().createTextFinder("someSearchTerm");

Search for text in a specific sheet

You can restrict the scope of the search to a specific sheet in your Google Sheets spreadsheet. In the code below, the search will be performed only within the sheet named "Sheet1".

let textFinder = SpreadsheetApp.getActive().getSheetByName("Sheet1").createTextFinder("someSearchTerm");

Search for text in a specific range

Finally, you can restrict the scope of the search to a specific range in your Google Sheets spreadsheet. In the code below, the search will be performed only within the range named "Data".

let textFinder = SpreadsheetApp.getActive().getRangeByName("Data").createTextFinder("someSearchTerm");

Conclusion

In this tutorial you learned how to programmatically find and replace text in your Google Sheets spreadsheet using TextFinder in Apps Script. Thanks for reading!