What's the best way to compare two columns of data in Google Sheets and output ONLY the non-matching using Google Scripts?

Question

I have two columns of similar unique data (Column A and Column B) and on the third (Column C), I would like to output data, where it is in Column B but not in Column A.

I would like to do this in Google Scripts and have tried writing my own if statements, looping it but without luck.

Can someone please point me to the right direction?

Answer 1

I actually do the same thing on a sheet I use at work. It looks at Col A, and Col C, and the ones from col A that are not in Col C will show up in Col E.

function onOpen() {
  SpreadsheetApp.getUi().createMenu("CD Report")
  .addItem("Agreement Report", "agreementReport")
  .addToUi();
}

function agreementReport(){
  var as = SpreadsheetApp.getActive();
  var sheet = as.getSheetByName("15-16 1:1 agreement");

  var handedIn = sheet.getSheetValues(2, 1, sheet.getLastRow(), 1);
  var stuNames = sheet.getSheetValues(2, 3, sheet.getLastRow(), 1);
  var list = []; 

  for (i in stuNames){
    var curName = stuNames[i][0];
    var exists = false;

    for (j in handedIn){
      var curCheck = handedIn[j][0];
      if (curCheck == curName){
        exists = true;
        break;
      }
    } // end for j
    if (exists == false){
      list.push([curName]);
    } 
  } // end for i

  sheet.getRange(2, 5, list.length, 1).setValues(list);

} // end agreementReport

Answer 2

complementing the above answer, I think a highlighted code helps people new to programming to better understand what the friend's code does. Because I found a simple side effect that causes this check to fail. It is necessary to check the list with the highest number of occurrences and, in the inner loop, make the comparison with the smaller list. Just supplemented and making the code a little more generic.

/** @OnlyCurrentDoc */
function onOpen() {
  SpreadsheetApp.getUi().createMenu("CD Report")
  .addItem("Agreement Report", "agreementReport")
  .addToUi();
}

function agreementReport(){
  var plan = SpreadsheetApp.getActive();
  var sheet = plan.getSheetByName("contatos1");
      ///GREB ALL VALUES FROM THE SMALLER COLUMN
  var columnB = sheet.getSheetValues(2, 2, sheet.getLastRow(), 1);
  ///GREB ALL VALUES FROM THE GREATER COLUMN
  var columnA = sheet.getSheetValues(2, 1, sheet.getLastRow(), 1);
  
  var list = []; 

  for (i in columnA){/*START BUFF THE GREATER COLUMN*/
    var curName = columnA[i][0];
    var exists = false;

    /*START BUFF THE smaller COLUMN AND chack if all occurency in the greater column*/
    for (j in columnB){
      var curCheck = columnB[j][0];
      if (curCheck == curName){
        exists = true;
        break;
      }
    } // end for j
    if (exists == false){
      list.push([curName]);
    } 
  } // end for i
  //Logger.log(list.length);
  sheet.getRange(2, 3, list.length, 1).setValues(list);
  //Logger.log(list);
} // end agreementReport