2

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);
  • Use MATCH() instead of a script. Regarding the code you included it has several errors. – Rubén Aug 27 '15 at 20:39
  • That's funny cause its working perfectly. and it has to be embedded in the script. – Michael Aug 27 '15 at 20:43
  • Maybe I'm wrong, maybe something was lost when you pasted the code like the last }. Try using the code format tool to format your code. – Rubén Aug 27 '15 at 20:51
  • I formatted your script code; please check that it has the meaning you intended. If you want to mimic Vlookup in a script, just use getValues and then run through the obtained values, comparing each to the search key. A couple of for loops is all it takes. – user79865 Aug 28 '15 at 3:19
-3

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.

  • 1
    The question is about a script, not a spreadsheet function. – user79865 Sep 29 '15 at 1:03
  • 1
    He mentioned considering using VLOOKUP, so a formula was not off the table necessarily. Thanks for the downvote. – James McCoy Sep 29 '15 at 2:57
  • My pleasure. He mentioned vlookup thinking it would work from a script. – user79865 Sep 29 '15 at 3:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.