How to get a1Notations when FindAll function returning (range, range, range, range)?

Question

I'm trying to search a google sheet for a specific key word with this code:

var completeSearch = dataSheetName.createTextFinder(searchTerm).findAll();

When I log it, the variable returns [Range, Range, Range, Range]. Is there a way to have it return the cell values for each occurrence of this key word?

Edit: I didn't mean I needed it to return the cell VALUES, as in the contents of the cell, but rather the cell name itself, like (A4).

Answer 1

• You want to retrieve values from the following script.
  • var completeSearch = dataSheetName.createTextFinder(searchTerm).findAll();
• dataSheetName is a sheet in a Spreadsheet.
  • About this, I thought from the variable name of dataSheetName.
• You want to use Google Apps Script.

If my understanding is correct, how about this modification?

Modification points:

• The values retrieved by findAll() is [Range, Range,,]. In order to retrieve values from Range, in this case, it uses getValue() and getValues.
• From your situation, you might also need the row value of the searched value. Because the values from each Range are the same with the value of searchTerm. So in this sample script, both the value of the searched range and the values of the row of searched range are retrieved.

Please think of this as just one of several answers.

Modified script:

Before you use this script, please set the variables of searchTerm and sheetName.

function myFunction() {
  var searchTerm = "sample"; // Please set this.
  var sheetName = "Sheet1"; // Please set this.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheetName = ss.getSheetByName(sheetName);
  var completeSearch = dataSheetName.createTextFinder(searchTerm).findAll();
  for (var i = 0; i < completeSearch.length; i++) {
    var range = completeSearch[i];
    var value = range.getValue();
    var rowValue = dataSheetName.getRange(range.getRow(), 1, 1, dataSheetName.getLastColumn()).getValues();
    Logger.log(value) // Value of the searched range
    Logger.log(rowValue) // Values of the row of searched range
  }
}

Note:

• You are showing a part of your script in your question. So I complemented other part for working the script. If this was the different from your script, please modify it.

References:

• Class Range
• getValue()
• getValues()

If I misunderstood your question and this was not the result you want, I apologize.

Edit:

• You want to retrieve the coordinate (a1Notation) of cells retrieved by searching.

From your replying, I could understand like above. If my understanding is correct, how about this sample script?

Modified script:

function myFunction() {
  var searchTerm = "sample"; // Please set this.
  var sheetName = "Sheet1"; // Please set this.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheetName = ss.getSheetByName(sheetName);
  var completeSearch = dataSheetName.createTextFinder(searchTerm).findAll();
  for (var i = 0; i < completeSearch.length; i++) {
    var range = completeSearch[i];
    Logger.log(range.getA1Notation())
  }
}

Reference:

• getA1Notation()