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);
4
  • Use MATCH() instead of a script. Regarding the code you included it has several errors. Aug 27, 2015 at 20:39
  • That's funny cause its working perfectly. and it has to be embedded in the script.
    – Michael
    Aug 27, 2015 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. Aug 27, 2015 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, 2015 at 3:19

1 Answer 1

-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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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