CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Oli Girling
Oli Girling

Reputation: 763

Compare two columns and if match, paste matching value

This seems like it should be super simple but I really cannot find a solution via Google Script. I want to compare all the cells in SKU (A) in Import to SKU (A) in Pricelist, and if there is a match then paste the matching SKU's into Sheet 3 (A).

Normally I would just do this through index match but I need to do it with Google Script.

I have tried the following and this works for checking a single cell (A81) with the whole range in Pricelist A1:A100 but I cannot make sense of it when checking a range against a range.

function checkProduct() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var lookup = ss.getRange('Import!A81').getValue();
  var range = ss.getRange('Pricelist!A1:A200').getValues();
  var lookupRange = [];

  for (var i = 0; i < range.length; i++) {
    lookupRange.push(range[i][0]);
  }
  var index = lookupRange.indexOf(lookup);
  if (index == -1) {
  }
  else {
    ss.getRange('Sheet3!A1').setValue('its there'); // need to paste in the matching SKU
  }
}

Import Sheet

SKU | Price
s123 | 99
s124 | 98
s125 | 97

Pricelist Sheet

SKU | Price
s123 | 99
a111 | 98
a453 | 97

Upvotes: 1

Views: 4628

Answers (1)

Ed Nelson
Ed Nelson

Reputation: 10259

Try this:

function checkProduct() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getSheetByName("Import")
  var lr=s.getLastRow()
  var lookup = s.getRange(2,1,lr-1,2).getValues();
  var s1=ss.getSheetByName("Pricelist") 
  var lr1=s1.getLastRow()
  var range = s1.getRange(2,1,lr1-1,2).getValues();
  var s3=ss.getSheetByName("Sheet3")
  var lookupRange = [];
  for (var i = 0; i < lookup.length; i++) {
     for (var j = 0; j < range.length; j++) {
     var  test=lookup[i][0]
         if(lookup[i][0]==range[j][0]){
           lookupRange.push([range[j][0],range[j][1],lookup[i][0],lookup[i][1],]);
     }}}
   s3.getRange(2,1,lookupRange.length,4).setValues(lookupRange); 
}

I am pushing all 4 values on a match. Adjust as needed.

Upvotes: 3

Related Questions