Find difference between two arrays (missing values) in Google Apps Script

Question

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 that works in GAS. Currently attempting with a for() loop and indexOf():

var ss = SpreadsheetApp.getActiveSpreadsheet();  
var sheet = ss.setActiveSheet(ss.getSheetByName('test'));

function TEST(){
  var lastRow = sheet.getLastRow();
  var orders = sheet.getRange(2,1,lastRow,1).getValues(); //[a,b,c,d]
  var products = sheet.getRange(2, 2,lastRow,1).getValues();  //[a, b]
  var missing = [];
  for ( var i = 0 ; i < Object.keys(orders).length; i++){
    if(products.indexOf(orders[i])<0){
      missing.push(orders[i]);};
  };
  Logger.log(missing); //expect [c, d]
   }

The source table has two columns to compare, and a 3rd column where the new 'missing' array should be stored.

orders  products    missing
a       a           c
b       b           d
c       
d

I tried methods from several other posts but everything is using functions that aren't available in Google Apps Scripts.

My answer here might help you, https://stackoverflow.com/questions/52613907/javascript-find-index-of-missing-elements-of-two-arrays/52614169#52614169 . It involves looping using `Object.forEach` and `Array.include` and finding if **array A** contains something **array B** does not have. — ABC, Mar 29 '19 at 19:19
Possible duplicate of [Javascript find index of missing elements of two arrays](https://stackoverflow.com/questions/52613907/javascript-find-index-of-missing-elements-of-two-arrays) — ABC, Mar 29 '19 at 19:27
Possible duplicate of [Javascript algorithm to find elements in array that are not in another array](https://stackoverflow.com/questions/2963281/javascript-algorithm-to-find-elements-in-array-that-are-not-in-another-array) — tehhowch, Mar 29 '19 at 19:44
Note that your comments about the return values of `Range#getValues` are wrong. The return values are `Array`s whose elements are `Array`s, whose elements are the values in a given row. i.e., `Range#getValues` returns a 2D array indexed first by row index, then by column index. So your `orders` is actually `[ [a], [b], [c], [d] ]`. Flatten it, and flatten `products`, and then you should be able to apply the methods discussed in other posts. It's normal to expect you to be able to translate arrow syntax into function syntax. — tehhowch, Mar 29 '19 at 22:41

Cooper · Answer 1 · 2019-03-29T20:42:08.800

Find Missing Orders:

function findMissingOrders() {
  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:

function getColumnHeight(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:

Nils Lockean · Answer 2 · 2019-03-29T20:42:32.677

0

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)

function firstOfArray(array) {
   return array[0]
}
function missing(order) {
    return products.indexOf(order) === -1
}

edited Mar 29 '19 at 20:42

answered Mar 29 '19 at 19:22

Nils Lockean

109
5

The includes() function is not available in GAS. I'm hoping to find a method that works with pure GAS functions. – GreenFlux Mar 29 '19 at 19:45
Tried filter with indexOf() but all values return -1, so the whole list is returned instead of just [c, d]. – GreenFlux Mar 29 '19 at 19:58
Ah, the reason that indexOf returns undefined is probably because the getValues() method called on your ranges return a two-level array ([[a],[b],[c],[d]]). If you map the values like @Cooper says, you should have some more luck. – Nils Lockean Mar 29 '19 at 20:39

Find difference between two arrays (missing values) in Google Apps Script

2 Answers2

Linked