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:

Or to a range of cells:

All together now

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

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store