Google-sheets – way to emulate Vlookup in Google Script

google sheetsgoogle-apps-script

I did some looking around and I'm struggling quite a bit in finding the answer.

I'm working on a Google Script for Google Sheets that works like a Vlookup, where you define a cell for it to look at, and use the information found within that cell to search ANOTHER range of cells elsewhere.

eg. 1

Use the value from A1 (Sheet 1) to search in the 1st column (Sheet 2) and then return the value from that row in the 3rd column.

eg. 2

=VLOOKUP(A1, 'Sheet 2'!A1:D4, 3)

Bearing in mind that since this is a Script and not a Formula, the fact that A1 is in Sheet 1 and not Sheet 2 would of course need to somehow be defined

Best Answer

Yes, it is possible to emulate many of the built-in functions by using Class Spreadsheet (SpreadsheetApp) and JavaScript Array Object and its methods, but "the emulations" usually will be slower than built-in functions.

Knowing the above I consider that the core of the question is how to use JavaScript to emulate VLOOKUP and other built-in functions.

I order to keep this answer short, here is an example taken from the answer to Writing google Javascript similar to vlookup

function findinB() {
  var sh = SpreadsheetApp.getActiveSheet();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var last=ss.getLastRow();
  var data=sh.getRange(1,1,last,2).getValues();// create an array of data from columns A and B
  var valB=Browser.inputBox('Enter value to search in B')
  for(nn=0;nn<data.length;++nn){
    if (data[nn][1]==valB){break} ;// if a match in column B is found, break the loop
      }
Browser.msgBox(data[nn][0]);// show column A
}

Just replace the Browser.inputBox by the data sources that you want to use.

Note: There are several questions on Stack Overflow about how to implement VLOOKUP on JavaScript and/or Apps Script, like Does JavaScript or jQuery have a function similar to Excel's VLOOKUP?

Related Topic