Find and replace in a Google Sheet - whole data search

The following Google Apps Script is designed to search Google Sheet data for a list of terms that need correcting and the cell colour changing on ones that have been modified. So here is a list of the 'incorrect' words to find and what it should be replaced by, as an example:

• xray change to X-Ray
• 1st change to First
• 2nd change to Second
  

This blog post is a slight adaptation of the one here for Find and replace in a single column. This time we are expanding our scope to the whole of the Google Sheet data where our list of terms may be present in multiple columns.

Use the 'Welcome' sheet for input to run the tool

I also wanted to learn about using the 'TextFinder' methods rather than just using 'For Loops'. This will allow us to give it a Google Sheet and in return get an array of all Ranges of cells matching our find word:

var textFinder = rawDataSheet.createTextFinder(findWord).findAll();

Next we use a JavaScript 'forEach' method as we need to cycle through each of the returned Ranges to pinpoint the exact cell location (via its Row and Column):

textFinder.forEach(function getCell(word) {
      var row = word.getRow();
      var col = word.getColumn();

From here we can get the cell, set its new value and change the background colour.

Download

Download the Find and replace in a Google Sheet - whole data search here (please use 'File' > 'Make a copy' for your own version).