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

user_18308583 0
Viewed 1079

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

A part of my code has errors:

function AddNewRows() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var a = spreadsheet.getRange("N1:N" + spreadsheet.getLastRow()).createTextFinder("Double-Click").matchEntireCell(true).findNext().getRow()+1;
  var b = a+6
  ;

Edit 2:
Above issue is resolved. Thank you so much to all those who answered it.

But getting error in this last line = Range not found.

function AddNewRows() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var a = spreadsheet.getRange("N1:N" + spreadsheet.getLastRow()).createTextFinder("Double-Click").matchEntireCell(true).findNext().getRow()+1;
  var b = a+6
  spreadsheet.getRange(a,b).activate();
  spreadsheet.setCurrentCell(spreadsheet.getRange("A" & a &":A" & b));


Please help.
google-apps-script google-sheets
2 Answers

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;
}

findNext() returns a range

enter image description here

user_72150910

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:

Added:

From Above issue is resolved. Thank you so much to all those who answered it., I understood that your question was resolved.

About your new question of But getting error in this last line = Range not found., when I saw your script of your new question, I think that when you want to add the string, "A" & a &":A" & b is required to be modified. It's "A" + a + ":A" + b.

But, when the value of a is different from the value of b, an error occurs. Because setCurrentCell can be used for one cell.

Unfortunately, I'm not sure whether I could correctly understand your new question of Also, can I add "+1" in the last as I want the next row which has the text "Double-Click" in your comment,

If you want to activate the cells of "A" + a + ":A" + b, please modify as follows.

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
var a = spreadsheet.getRange("N1:N" + spreadsheet.getLastRow()).createTextFinder("Double-Click").matchEntireCell(true).findNext().getRow() + 1;
var b = a + 6;
spreadsheet.getRange("A" + a + ":A" + b).activate();

If you want to activate the cell of the next row of the search cell, please modify as follows.

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
spreadsheet.getRange("N1:N" + spreadsheet.getLastRow()).createTextFinder("Double-Click").matchEntireCell(true).findNext().offset(1, 0).activate();
user_71086530
Related