Home > database >  How to get row number of a cell with a specific text in Google Scripts
How to get row number of a cell with a specific text in Google Scripts

Time:03-01

In my column N, there is only one cell with text - "Double-Clic: How to get the row number of that cell.

My code has errors:

function FindRow() {
  var spreadsheet = SpreadsheetApp.getActive();
  var a = spreadsheet.getRange.createTextFinder("Double-Click").matchEntireCell(true).findNext().getRow();
return a
}

Error is TypeError: spreadsheet.getRange.createTextFinder is not a function


Please help.

CodePudding user response:

Using Textfinder

find first occurrence

function FindRow() {
  const ss = SpreadsheetApp.getActive();
  const r = ss.createTextFinder("Double-Click").matchEntireCell(true).findNext();
  const location = r.getA1Notation();
  const sheet = r.getSheet();
  const row = r.getRow();
  Logger.log('%s!%s row:%s',sheet.getName(),location,row);
  return row;
}

enter image description here

CodePudding user response:

I think that the reason for your issue is due to getRange of spreadsheet.getRange.createTextFinder.

From In my column N, there is only one cell with text - "Double-Clic: How to get the row number of that cell., when you want to search the value of Double-Click from the column "N", please modify as follows.

Modified script:

function FindRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var a = sheet.getRange("N1:N"   sheet.getLastRow()).createTextFinder("Double-Click").matchEntireCell(true).findNext().getRow();
  return a
}
  • When you want to search from the row 2, please modify "N1:N" sheet.getLastRow() to "N2:N" sheet.getLastRow().

Note:

  • createTextFinder method is also existing in Class Range, Class Sheet, Class Spreadsheet. For example, when you want to search the value from the sheet, you can also modify as follows.

      function FindRow() {
        var sheet = SpreadsheetApp.getActiveSheet();
        var a = sheet.createTextFinder("Double-Click").matchEntireCell(true).findNext().getRow();
        return a
      }
    

Reference:

  • Related