google script How to compare cells in two columns and set value to another cell

Question

I have unique ID ('ID1') in column A in sheet1 and I need to compare it with column B in sheet2 where are ID ('ID2') to.

So I need to compare those of ID and check if some of them match, if they are I want to change value of cell in the same row as 'ID1' in sheet1 but in another column, let say C.

function IDValidation() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('Sheet1');
  var sheet2 = ss.getSheetByName("Sheet2");
  var Sheet1range = sheet1.getRange(2,1,sheet1.getLastRow()); //header is row 1
  var Sheet2range = sheet2.getRange(2, 2);    //header is row 1
  var Sheet1cellValue = Sheet1range.getValues();
  var Sheet2cellValue = Sheet2range.getValues();//Look here

  for(i = 0; i<Sheet1cellValue .length-1; i++){
     if(Sheet1cellValue [i][0] == Sheet2cellValue)
     {
       ss.getSheetByName("Sheet1").getRange(i+2,3).setValue("Close");

}
}
}

Sheet2cellValue is an array. Have you tried comparing to Sheet2cellValue[0][0]? — Karl_S
It didn't work, even if I expanded Sheet2range by var Sheet2range = sheet2.getRange(2, 2,sheet2.getLastRow(). It shows only the first match, not all — vipmaciej
var Sheet2range = sheet2.getRange(2, 2); is only getting one cell. Changing it to var Sheet2range = sheet1.getRange(2,2,sheet2.getLastRow()); will get all the rows starting at 2,2 as an array so you would have to search i the array. The simple method would be a second for loop inside the first, looping on each item in Sheet2cellValue and comparing it to Sheet1cellValue[i][0]. NOTE that using .getLastRow() will get all the empty rows as well, so any blank rows will match. — Karl_S
I know my script get's only cell. I've tried your way but it didn't work to. I think it's because sheet1 don't have range from sheet2 like you wrote. I've try also var Sheet2range = sheet2.getRange(2, 2,sheet2.getLastRow() but it get only first match — vipmaciej

Cooper Cooper · Accepted Answer · 2019-04-20T00:58:59

Not sure what you want but just trying to help

Here are some options.

Try this:

function IDValidation() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sh1=ss.getSheetByName('Sheet1');
  var sh2=ss.getSheetByName("Sheet2");
  var sh1rg=sh1.getRange(2,1,sh1.getLastRow()); //header is row 1
  var sh2rg=sh2.getRange(2,2);//header is row 1
  var sh1cvA=sh1rg.getValues();
  var sh2cV=sh2rg.getValue();//look here
  for(var i=0;i<sh1cvA.length-1; i++){
    if(sh1cvA[i][0]==sh2cV){
      sh1.getRange(i+2,3).setValue("Close");
    }
  }
}

Sheet1

Sheet2

That seems to work. Yes/No?

I don't know, perhaps you're looking for all matches between Sheet1 Column1 and Sheet2 Column2. That's what this function does. I'm using random numbers between 0 and 50 and not unique Ids so there are many matches. I stopped putting close in column3 and started describing where the matches are.

Here's the code:

function IDValidation1() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sh1=ss.getSheetByName('Sheet1');
  var sh2=ss.getSheetByName("Sheet2");
  var sh1rg=sh1.getRange(2,1,sh1.getLastRow(),1); //header is row 1
  var sh2rg=sh2.getRange(2,2,sh2.getLastRow(),1);//header is row 1
  var sh1cvA=sh1rg.getValues();
  var sh2cvA=sh2rg.getValues();
  for(var i=0;i<sh1cvA.length-1; i++){
    for(var j=0;j<sh2cvA.length;j++) {
      if(sh1cvA[i][0]==sh2cvA[j][0]){
        sh1.getRange(i+2,3).setValue(Utilities.formatString('Sheet2 Row:%s == Sheet1 Row:%s = %s',j+2,i+2,sh1cvA[i][0]));
      }
    }
  }
}

Here's Sheet1 After running:

And this is Sheet2: (I picked too many rows and couldn't fit them all on my screen)

google script How to compare cells in two columns and set value to another cell

2 Answers

Not sure what you want but just trying to help