Skip to content Skip to sidebar Skip to footer

Find Difference Between Two Arrays (missing Values) In Google Apps Script

Trying to find a method that works in Google Apps Scripts, to compare two arrays and find the values missing in the second array. I've tried several approaches but can't find one

Solution 1:

Find Missing Orders:

functionfindMissingOrders() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet110');
  var orderA=sh.getRange(2,1,getColumnHeight(1)-1,1).getValues().map(function(r){return r[0];});
  var prodA=sh.getRange(2,2,getColumnHeight(2)-1,1).getValues().map(function(r){return r[0];});
  var missA=[];
  for(var i=0;i<orderA.length;i++) {
    var order=orderA[i];
    if(prodA.indexOf(orderA[i])==-1) {
      missA.push([orderA[i]]);
    }
  }
  if(missA.length>0) {
    sh.getRange(2,3,missA.length,1).setValues(missA);
  }
}

Here's the getColumnHeight() function:

functiongetColumnHeight(col,sh,ss){
  var ss=ss || SpreadsheetApp.getActive();
  var sh=sh || ss.getActiveSheet();
  var col=col || sh.getActiveCell().getColumn();
  var rg=sh.getRange(1,col,sh.getLastRow(),1);
  var vA=rg.getValues();
  while(vA[vA.length-1][0].length==0){
    vA.splice(vA.length-1,1);
  }
  return vA.length;
}

Spreadsheet Before:

enter image description here

Spreadsheet After:

enter image description here

Solution 2:

Have you tried using .filter() on the orders variable? Something like this should do the trick:

var orders = sheet.getRange(2,1,lastRow,1).getValues().map(firstOfArray)
var products = sheet.getRange(2, 2,lastRow,1).getValues().map(firstOfArray)
var missing = orders.filter(missing)

functionfirstOfArray(array) {
   returnarray[0]
}
functionmissing(order) {
    return products.indexOf(order) === -1
}

Post a Comment for "Find Difference Between Two Arrays (missing Values) In Google Apps Script"