Script to act like vlookup, then use found value as variable

Question

I have a value "venue", which I want to match with a unique value in a different sheet ("Options"), column J.

Much like the normal vlookup formula, I want my script to return the value from column K, from the same row where my value matched.

I don't need this all as a standalone function, I need this as part of a bigger script & also define the looked up value as a variable.

I'm a novice with appscripts and grateful for any help.

Here's what I tried:

// Lookup Template
let venue = "Test";
let options = ss.getSheetByName("Options")
let venuelist = options.getRange('J2:J100').getValues();
var template;
     for(nn = 0; nn < venuelist.length; ++nn) {
     if(venuelist[nn][1] == venue) {
         var template = venuelist[nn][0];
         break;
     }
 }

And it's not working.

Answer 1

Try this.

// Lookup Template
let venue = "Test";
let options = ss.getSheetByName("Options")
let venuelist = options.getRange('J2:K100').getValues();
var template;
     for(let nn = 0; nn < venuelist.length; nn++) {
     if(venuelist[nn][0] == venue) {
         template = venuelist[nn][1];
         break;
     }
 }

Assuming your rows will keep on increeasing and it's not a one time activity, you can use the range 'J2:K' instead of 'J2:K100' so that it will check till the last row which has data.