replace() method

The replace() method in Apps Script replaces a specified string or regular expression with a new string in a Google Sheet or other G Suite application. It takes two arguments: the string or regular expression to be replaced, and the new string to replace it.

Example 1: Replacing a Substring in a Google Sheet
Suppose we have a Google Sheet with a column containing country names and want to replace the substring “States” with “Kingdom”. We can use the replace() method like so:

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:A100");
var values = range.getValues();
for (var i = 0; i < values.length; i++) {
  values[i][0] = values[i][0].replace("States", "Kingdom");

In this example, we first define a variable sheet to hold the active sheet of the Google Sheet. We then define a variable range to hold a range of cells to be processed (in this case, column A1:A100). Finally, we use a for loop to iterate over each cell in the range, replace the substring “States” with “Kingdom” using the replace() method, and set the new value back to the cell using the setValues() method.

Example 2: Replacing a Regular Expression in a Google Doc
Suppose we have a Google Doc containing city names and want to replace all occurrences of a regular expression that matches a specific pattern with a new string. We can use the replaceText() method like so:

var doc = DocumentApp.getActiveDocument();
var pattern = /New\s\w+/g;
var body = doc.getBody();
body.replaceText(pattern, "Big $&");

In this example, we first define a variable doc to hold the active Google Doc. We then define a regular expression pattern to match all occurrences of a city name starting with “New”, followed by one or more word characters. Finally, we use the replaceText() method on the document body to replace all matches of the pattern with a new string “Big ” followed by the matched text ($& is a special substitution pattern that represents the entire matched text).

By using the replace() method in Apps Script, we can easily replace text patterns within Google Sheets and Docs with new strings or regular expressions, which can be useful for data processing, formatting, and automation.