Find or replace text within a range, sheet or spreadsheet. Can also specify search options.
Methods
Method | Return type | Brief description |
---|---|---|
findAll() | Range[] | Returns all cells matching the search criteria. |
findNext() | Range | Returns the next cell matching the search criteria. |
findPrevious() | Range | Returns the previous cell matching the search criteria. |
getCurrentMatch() | Range | Returns the current cell matching the search criteria. |
ignoreDiacritics(ignoreDiacritics) | TextFinder | If true , configures the search to ignore diacritics while matching; otherwise the
search matches diacritics. |
matchCase(matchCase) | TextFinder | If true , configures the search to match the search text's case exactly, otherwise the
search defaults to case-insensitive matching. |
matchEntireCell(matchEntireCell) | TextFinder | If true , configures the search to match the entire contents of a cell; otherwise, the
search defaults to partial matching. |
matchFormulaText(matchFormulaText) | TextFinder | If true , configures the search to return matches that appear within formula text;
otherwise cells with formulas are considered based on their displayed value. |
replaceAllWith(replaceText) | Integer | Replaces all matches with the specified text. |
replaceWith(replaceText) | Integer | Replaces the search text in the currently matched cell with the specified text and returns the number of occurrences replaced. |
startFrom(startRange) | TextFinder | Configures the search to start searching immediately after the specified cell range. |
useRegularExpression(useRegEx) | TextFinder | If true , configures the search to interpret the search string as a regular expression;
otherwise the search interprets the search string as normal text. |
Detailed documentation
findAll()
Returns all cells matching the search criteria.
Return
Range[]
— All the matching cells.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
findNext()
Returns the next cell matching the search criteria.
Return
Range
— The next matching cell, or null
if there are no previous matches.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
findPrevious()
Returns the previous cell matching the search criteria.
Return
Range
— The previous matching cell, or null
if there are no previous matches.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
getCurrentMatch()
Returns the current cell matching the search criteria.
Return
Range
— The current matching cell, or null
if there are no further matches.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
ignoreDiacritics(ignoreDiacritics)
If true
, configures the search to ignore diacritics while matching; otherwise the
search matches diacritics. A diacritic is a sign, such as an accent or cedilla, which when
written above or below a letter indicates a difference in pronunciation from the same letter
when unmarked or differently marked.
Parameters
Name | Type | Description |
---|---|---|
ignoreDiacritics | Boolean | Whether the search considers diacritics. |
Return
TextFinder
— This text finder, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
matchCase(matchCase)
If true
, configures the search to match the search text's case exactly, otherwise the
search defaults to case-insensitive matching.
Parameters
Name | Type | Description |
---|---|---|
matchCase | Boolean | Whether the matching is case-sensitive. |
Return
TextFinder
— This text finder, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
matchEntireCell(matchEntireCell)
If true
, configures the search to match the entire contents of a cell; otherwise, the
search defaults to partial matching.
Parameters
Name | Type | Description |
---|---|---|
matchEntireCell | Boolean | Whether the entire cell is matched. |
Return
TextFinder
— This text finder, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
matchFormulaText(matchFormulaText)
If true
, configures the search to return matches that appear within formula text;
otherwise cells with formulas are considered based on their displayed value.
Parameters
Name | Type | Description |
---|---|---|
matchFormulaText | Boolean | Whether the search examines formula text. |
Return
TextFinder
— This text finder, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
replaceAllWith(replaceText)
Replaces all matches with the specified text. Returns the number of occurrences replaced, which may be different from the number of matched cells.
Parameters
Name | Type | Description |
---|---|---|
replaceText | String | The text that replaces the text in the matched cells. |
Return
Integer
— The number of occurrences replaced.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
replaceWith(replaceText)
Replaces the search text in the currently matched cell with the specified text and returns the number of occurrences replaced.
Parameters
Name | Type | Description |
---|---|---|
replaceText | String | The text that replaces the content in the currently matched cell. |
Return
Integer
— The number of occurrences replaced.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
startFrom(startRange)
Configures the search to start searching immediately after the specified cell range.
Parameters
Name | Type | Description |
---|---|---|
startRange | Range | The cell range after which the search should start. |
Return
TextFinder
— This text finder, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
useRegularExpression(useRegEx)
If true
, configures the search to interpret the search string as a regular expression;
otherwise the search interprets the search string as normal text. For more details on how to
use regular expressions, refer to the
Find and replace support page.
Parameters
Name | Type | Description |
---|---|---|
useRegEx | Boolean | Whether to interpret the search string as a regular expression. |
Return
TextFinder
— This text finder, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets