Is there a way to emulate Vlookup in Google Script?

Question

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

Answer 1

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?

Answer 2

I spent a long time using the for loops in the other answers to emulate vlookup. It works wonderfully if you don't have much data....but the more data you have, the longer it takes, which can get quite frustrating! Here is the solution I found which avoids the for loop:

 var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();     

 var data = SpreadsheetApp.openById("ID").getSheetByName("Sheet1").getRange('A2:Z').getValues()

 var searchValue = s.getRange("A2").getValue();

 var dataList = data.map(x => x[0])

 var index = dataList.indexOf(searchValue);

 if (index === -1) {

    throw new Error('Value not found')

 } else {

      var foundValue = data[index][1]

     s.getRange("B2").setValue(foundValue);

 }

It's a pretty short code, so now I'll go through it line by line for you. This is how I did it as part of a larger function, but you could easily turn it into a function that returns, by feeding in some of the data I grab here.

 var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();     

 var data = SpreadsheetApp.openById("ID").getSheetByName("Sheet1").getRange('A2:Z').getValues();

Here, I grab the sheets that I wish to use. For s, this is the page I'm performing the 'vlookup' on. You can also use getSheetByName() here if you prefer to call an individual sheet. For data, that's the search range we want to use. For the data variable, you can grab the ID of the sheet by grabbing the sheet code in the url between /d/ and /edit. For getSheetByName, that's the name of the sheet. You can then

 var searchValue = s.getRange("A2").getValue();

Here, I use getRange() and getValue()/to grab first the value I wish to search for (change A2 to grab the cell of the value you want to search for).

 var dataList = data.map(x => x[0])

Here, I create an array of ONLY the first column of my data. This is what I will be matching on!

 var index = dataList.indexOf(searchValue);

Here, i use indexOf, which is a tool you can use on arrays to get the position within the array. So, for example, if something is the 5th item in your array, it will return 6 (because arrays start the count with 0). This essentially gets what "row" it is in my data.

 if (index === -1) {

    throw new Error('Value not found')

 } else {

If the value is not found within the list, index will return -1. This first portion of the if block covers what to do if the value is not found, which you can customize for your needs.

 var foundValue = data[index][1]

Then, we can use the index item we got to grab the row in the data we want. Then we just have to say which column we want with the second call. If you are looking for the second column (Column B), you would put [1].

 s.getRange("B2").setValue(foundValue);

Finally, I set the value where I want my 'new' value to go to the value I found! You could also instead use it as a variable in whatever you are trying to do.

If you want to find multiple matches, use lastIndexOf() and a while loop. As long as index is not -1, I grab the lastIndexOf that item, and then splice it. If you want to emulate arrayformula(vlookup(, you could also do the original item, but with a for loop going through the different 'search' values.

I know this is a very late answer, but all of my scripts sped up dramatically when I discovered it, so I wished to share :) Hope it helps!

Answer 3

I know I'm late to the party, but I built this script a while back. As expected, it's slow, but it performs vlookup as a script function.

function vlookupscript(search_key, range, index) {
  var returnVal = null;
  for (var i in range) {
    if (range[i][0] == search_key) {
      returnVal = range[i][index - 1];
      break;
    }
  }
  return returnVal;
}

Answer 4

I recommend using the built-in TextFinder functionality. I had a large dataset (17,000+ rows) and my apps script function was timing out trying the other suggestions here, but using the Sheets text finder (similar functionality to hitting Ctrl+F in sheets and searching something) returned a resulting range much faster: https://developers.google.com/apps-script/reference/spreadsheet/sheet#createtextfinderfindtext