I am searching for a word in a spreadsheet using TextFinder on Google sheets Script.
I would like to have as an output the name of the sheet where the text was found.
Usually they'll only be 1 of this keyword per spreadsheet and for sure only once per sheet.
If found more than 1 in spreadsheet would be nice to have all sheet names returned.
Code:
function TestCMNDH() {
var source = SpreadsheetApp.openById('SheetID');
var found = source.createTextFinder('Wordtosearch').matchCase(false).findAll();
Logger.log(found)
};
Current output: [Range]
Any help would be greatly appreciated! Thanks!
You could do something like this to get a unique list name of the spreadsheets where the text was found:
function myFunction() {
var source = SpreadsheetApp.openById('SheetID');
var foundRange = source.createTextFinder('Wordtosearch').matchCase(false).findAll();
var sheetNames = foundRange.map(range => range.getSheet().getName());
var uniqSheetNames = [...new Set(sheetNames)];
uniqSheetNames.forEach(sheetName => Logger.log(sheetName));
}
What you get as a result of the findAll function is an array of Range.
You can get the Sheet that sheet belongs to using getSheet(), and you can get the name of that sheet using getName()
So I map that array of Range into an array of sheet names:
var sheetNames = foundRange.map(range => range.getSheet().getName());
The following line remove any possible duplicate, and then just log the names in the last line