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:
Spreadsheet After:
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"