Replace text in Google Spreadsheets with Apps Script

Photo by Alex Nicolopoulos on Unsplash

Google Sheets has a powerful find and replace function. But you may want to use Google Apps Script to do the same job.

For example, you get a new spreadsheet every day and have to find “Tom” and replace it with “Scratchy”. A script can get the job done in a click.

StackOverflow has a question on this. The accepted answer would require you to loop through all cells, find the text, replace it and post the new text back to the cell.

Google Apps Script has a more powerful, and simpler, method called TextFinder.

With TextFinder you don’t need to loop through the sheets or cells. And you don’t need to post the edited text back to the cell.

If you want to find and replace text in all the sheets in a spreadsheet, here is how you do it.

Get the sheet:

var sheet = SpreadsheetApp.getActiveSpreadsheet();

Create the text finder:

var textFinder = sheet.createTextFinder(‘Tom’);

And replace:

textFinder.replaceAllWith(‘Scratchy’);

You can limit the search area to a sheet:

var activeSheet = sheet.getActiveSheet();
var textFinder = activeSheet.createTextFinder(‘Tom’);

Or to a range of cells:

var activeSheet = sheet.getActiveSheet();
var range = sheet.getActiveRange();
var textFinder = range.createTextFinder(‘Tom’);

All together now

function replaceEverywhere() {
   var sheet = SpreadsheetApp.getActiveSpreadsheet();
   var textFinder = sheet.createTextFinder(‘Tom’);
   textFinder.replaceAllWith(‘Scratchy’);
}

Make a copy of my Google Sheet. Or get the script from Github.