2

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

  • Are you aware that usually Apps Script is slower than built-in functions? Anyway, Yes, it is possible to emulate many of the built-in functions by using Class Spreadsheet (SpreadsheetApp) and JavaScript Array Object and its methods. – Rubén Jan 4 at 5:43
  • @Rubén Perfect, I think those methods are what I'm looking for thanks so much. I know that Apps Script is often slower. The goal of the project I'm working on is to include it as little as possible, but in essence I'm looking to create an input field that constantly changes, in a system where inputted data is aggregated. – Paul-Michael Escolano Jan 4 at 17:31
0

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?

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.