Implement vlookup between two ranges in Google Apps Script

Question

I'm looking to find a way to write a script that behaves similarly to a vLookup in google sheets.

Here is a link to a simplified example document.

What I am trying to do is use "Sheet1" as a form of sorts. When I enter information in "Sheet1" column B I want to be able to hit a custom menu button and have the information in "Sheet1" column B automatically populate correspondingly based of the values in column A into the first empty column on "Sheet 2".

I can write the script to create the custom menu and execute the function but I'm unsure of how to write the function itself.

Unknown Unknown · Accepted Answer · 2016-06-16T07:10:47

Here is how such a function can look like. It gets pointers to each sheet, then the appropriate range from each: columns A and B, ignoring empty rows at the bottom. Then gets the values and begins comparing them: when columns A match, column B is assigned to. The final line puts the modified array values back into Sheet2.

function vl() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('Sheet1');
  var sheet2 = ss.getSheetByName('Sheet2');
  var range1 = sheet1.getRange(1, 1, sheet1.getLastRow(), 2);
  var range2 = sheet2.getRange(1, 1, sheet2.getLastRow(), 2);
  var values1 = range1.getValues();
  var values2 = range2.getValues();
  for (var i = 0; i < values1.length; i++) {
    for (var j = 0; j < values2.length; j++) {
      if (values1[i][0] === values2[i][0]) {
        values2[i][1] = values1[i][0];
      }
    }
  }
  range2.setValues(values2);
}

Implement vlookup between two ranges in Google Apps Script

1 Answers