Google-sheets – Reference a Google Spreadsheet Row containing specific Text within Google App Script for spreadsheets

google sheetsgoogle-apps-script

I am having trouble finding a function that can reference a row (the number of that row) containing specific text as criteria.

I thought that VLookUp would work, but it's not really. Ideally it's something like this:

var Row = Find("This Text", in this range, in this column)

And the output would be the rows#.

"Find" is a substitute for the ACTUAL function I'm looking for. If you know of a function in app scripts that can accomplish this, please let me know.

To give you an idea of the code I'm making, here is a section (Vlookup isn't working).

// Process the user's response. 

var Row = Vlookup("Cancelled",A7:B,2,False); 

var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var range = ss.getSheets()[0].getRange("A14:K14"); 
SpreadsheetApp.setActiveRange(range); 
var sheet = ss.getSheets()[0]; 
var cell = sheet.getRange("B15"); 
var button = Result.getSelectedButton(); 
var text = Result.getResponseText(); 
if (button == Ui.Button.OK) { 
    var sh = ss.getActiveSheet(); 
    sh.insertRowsAfter(Row-1,1); 
}
cell.setValue(text);

Best Answer

You can use MATCH to return the row number. For example if you're looking for the word "Help" in Column H, and it's on row 14:

=MATCH("Help",H:H,0)

will return 14.