Google Apps Script:
Searching and replacing text

How to:

Google Apps Script offers a straightforward way to search and replace text, especially within Google Docs and Sheets. Below are examples for both.

Google Docs:

To search and replace text in a Google Document, you’ll primarily interact with the DocumentApp class.

function searchReplaceInDoc() {
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();
  
  // To search and replace a specific phrase
  body.replaceText('searchText', 'replacementText');
  
  DocumentApp.getActiveDocument().saveAndClose();
}

// Usage
searchReplaceInDoc();

This code snippet searches for all occurrences of 'searchText' in the active Google Document and replaces them with 'replacementText'.

Google Sheets:

Similarly, in Google Sheets, you can use SpreadsheetApp to perform search and replace operations:

function searchReplaceInSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Search and replace in the currently active sheet
  // replaceText(searchText, replacement)
  sheet.createTextFinder('searchText').replaceAllWith('replacementText');
}

// Usage
searchReplaceInSheet();

In this example, createTextFinder('searchText') searches the active sheet for ‘searchText’, and replaceAllWith('replacementText') replaces all occurrences with ‘replacementText’.

Deep Dive

The search and replace functionality in Google Apps Script is heavily influenced by its web-based nature, allowing scripts to manipulate text across various Google Apps seamlessly. Historically, this capability stems from the broader context of text processing and manipulation in programming, where regular expressions and string functions in languages such as Perl and Python set a high standard for flexibility and power.

While Google Apps Script’s search and replace functionality is powerful for straightforward substitutions, it lacks the full regular expression capabilities found in some other languages. For example, while you can use basic regular expressions in createTextFinder in Google Sheets, the options for complex pattern matching and manipulation are limited compared to Perl or Python.

For more advanced text-processing needs, programmers might resort to exporting the Google Docs or Sheets content to a format that can be processed externally with more powerful languages or employing Google Apps Script to call external APIs or services that offer more sophisticated text manipulation capabilities.

Despite these limitations, for most typical search and replace tasks within the ecosystem of Google Apps, Google Apps Script offers a simple, efficient, and highly integrable solution tailored to the needs of automating and scripting within Google’s suite of productivity tools.