Search and match

Question

Sheet Import

 id  | Price | c,e
s123 |  99   |  A@
s124 |  98   |  B@
s125 |  97   |  C@
r78  |  35   |  D@

This is the code:

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

ACTUAL RESULT:
I tried lookupRange.push() inside the if block:
Sheet Pricelist

id   |  Price |  c,e
a453 |  99    |   A@
a111 |  98    |   D@
s123 |  97    |
r78  |  35    |

With the script I do not achieve the match between s123 A @ and r78 D @. I'm not sure what to do next.

DESIRED OUTPUT:

Sheet Pricelist

id   |  Price |  c,e
a453 |   99   |
a111 |   98   |
s123 |   97   |  A@
r78  |   35   |  D@

I'm surprised this gives any output. You have nothing in your `if` statement. So the code is going to run through all of the combinations in the nested `for` statements, and then move on to `s1.getRange`, and most likely spit out whatever happened to be in the array. That line specifically tells it to output to Row 2 Column 3, so that part makes sense. It won't line up with your desired row because you aren't asking it to. I think you need to move the `s1.getRange` line inside the `if` statement, and then change the address: `s1.getRange(2, j+1).setValues(lookupRange);`. — Ron Kloberdanz, Sep 14 '18 at 19:54
What's the desired output? What's wrong with the code so far? — TheMaster, Sep 14 '18 at 21:01
lookupRange is an empty single dimension array which is not appropriate for use in setValues(). Check the documentation — Cooper, Sep 15 '18 at 12:48

TheMaster · Accepted Answer · 2018-09-16T15:55:54.473

To create the empty 2D array lookupRange, You can try:

From:

var lookupRange = [];

To:

var lookupRange = Array.apply(null,Array(range.length)).map(function(e){ return [""];});

or

var lookupRange = s1.getRange(2, 3, range.length).getValues(); //Assuming they're empty.

Array(range.length) creates a sparse array of length equal to length of range
Array.apply() spreads the sparse array and converts it to dense array, which can then be mapped to arrays with empty strings [""]
Alternatively, Easy way is just to getValues() from sheet with empty values

Modified Script:

function buscarCe2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Import")
  var lr = s.getLastRow()
  var lookup = s.getRange(2, 1, lr - 1, 3).getValues();
  var s1 = ss.getSheetByName("Pricelist") 
  var lr1 = s1.getLastRow()
  var range = s1.getRange(2, 1, lr1 - 1, 3).getValues();
  var lookupRange = Array.apply(null,Array(range.length)).map(function(e){ return [""];});
  for (var i = 0; i < lookup.length; i++) {
      for (var j = 0; j < range.length; j++) {
        if (lookup[i][0] == range[j][0]) {
          lookupRange[j][0]=lookup[i][2];
        }    
      }  
    }
  s1.getRange(2, 3, lookupRange.length).setValues(lookupRange);
}

Search and match

1 Answers1

From:

To:

Modified Script:

Further Reading: