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, stackoverflow.com/questions/52613907/… . It involves looping using Object.forEach and Array.include and finding if array A contains something array B does not have. — ABC
– ABC, Commented Mar 29, 2019 at 19:19
Possible duplicate of Javascript find index of missing elements of two arrays — ABC
– ABC, Commented Mar 29, 2019 at 19:27
Possible duplicate of Javascript algorithm to find elements in array that are not in another array — tehhowch
– tehhowch, Commented Mar 29, 2019 at 19:44
Note that your comments about the return values of Range#getValues are wrong. The return values are Arrays whose elements are Arrays, 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
– tehhowch, Commented Mar 29, 2019 at 22:41

Cooper · Accepted Answer · 2019-03-29 20:42:08Z

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 · Accepted Answer · 2019-03-29 20:42:32Z

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, 2019 at 20:42

answered Mar 29, 2019 at 19:22

Nils Lockean

1095 bronze badges

3 Comments

GreenFlux Over a year ago

The includes() function is not available in GAS. I'm hoping to find a method that works with pure GAS functions.

GreenFlux Over a year ago

Tried filter with indexOf() but all values return -1, so the whole list is returned instead of just [c, d].

Nils Lockean Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related