Filtering a two dimensional array in apps script

Question

In apps script I have the following:

function diff(A, B) {
  return A.filter(function (a) {
    return B.indexOf(a) == -1;
  });
}

When I run:

function testArray(){

  ta = ['a','b','c','d']
  ts = ['a','b','c']

  o =diff(ta,ts)

  Logger.log(o);


}

I get: ['d'] - The correct answer , now I'm trying to apply filter to a 2d array of values - a google sheet.

I tried :

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var target_sheet_range =   ss.getSheets()[1].getDataRange();
  var target_sheet_values =   target_sheet_range.getValues();
        Logger.log('target_sheet_values');
          Logger.log(target_sheet_values);
  var range = ss.getSheets()[0].getDataRange();
  var values = range.getValues();


          Logger.log('values');
          Logger.log(values);


    var diff_values = diff(values, target_sheet_values)

but this is not taking account the 2d nature of the arrays. You can see it at google spreadsheet https://docs.google.com/spreadsheets/d/1fFr9Sz2EAiMYoybg-evV33n_BZjF-1QNnnKqiqSXQ_g/edit?usp=sharing

output:

[18-06-23 14:45:22:129 EDT] target_sheet_values
[18-06-23 14:45:22:130 EDT] [[a, a, a, a, a], [z, x, c, v, b], [m, n, b, v, c]]
[18-06-23 14:45:22:457 EDT] values
[18-06-23 14:45:22:458 EDT] [[a, a, a, a, a], [z, x, c, v, b], [m, n, b, v, c], [a, s, d, f, g], [q, w, e, r, t]]
[18-06-23 14:45:22:459 EDT] hhhhhh
[18-06-23 14:45:22:459 EDT] [[a, a, a, a, a], [z, x, c, v, b], [m, n, b, v, c], [a, s, d, f, g], [q, w, e, r, t]]

How do I modify the filter function 2 filter a 2d array

Related: Equivalent of “=Query(ImportRange(” in Google Apps Script? — Wicket
– Wicket, Commented Jun 23, 2018 at 19:08

Mark · Accepted Answer · 2018-06-23 19:10:24Z

2

You problem is related to the fact that in javascript [1, 2, 3] != [1, 2, 3]. Arrays and object comparisons are not done by the values. This also applies to indexOf(). For example:

var a = [[1, 2, 3], [4, 5, 6]]
var b = [4, 5, 6]

console.log(a.indexOf(b)) // returns -1

To fix this you need to write a function that defines equality the way you want. For example:

// arrays with the same values in the same order will be considered equal
function array_equals(a, b){
    return a.length === b.length && a.every((item,idx) => item === b[idx])
}

console.log(array_equals([1, 2, 3], [1, 2, 3]))
console.log(array_equals([1, 2, 3], [1, 2, 4]))

With that in hand you can now filter with something like:

var a = [[1,2,3], [4, 5, 6], [7, 8, 9]]
var b = [[1, 2, 3], [4, 5, 6]]


function array_equals(a, b){
    return a.length === b.length && a.every((item,idx) => item === b[idx])
}

function diff(A, B) {
    return A.filter(test => {
      return B.findIndex(item => array_equals(item,test)) == -1;
    });
  }
console.log(diff(a,b))

edited Jun 23, 2018 at 19:10

answered Jun 23, 2018 at 19:08

Mark

92.7k8 gold badges116 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Wicket Over a year ago

Google Apps script doesn't support let. Related Declaring variables on Google sheet script editor using let

Mark Over a year ago

Thanks @Rubén, didn't know that. Edited.

Wicket Over a year ago

Arrow functions aren't supported either (I just noticed that you used them too).

Tanaike Over a year ago

Unfortunately, findIndex cannot be used at Google Apps Script yet. Because it was added from ECMAScript 2015 (ES6). developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/…

Owen Blacker · Accepted Answer · 2019-03-02 20:54:44Z

1

You want to retrieve [[a, s, d, f, g], [q, w, e, r, t]] from [[a, a, a, a, a], [z, x, c, v, b], [m, n, b, v, c], [a, s, d, f, g], [q, w, e, r, t]] and [[a, a, a, a, a], [z, x, c, v, b], [m, n, b, v, c]]. If my understanding is correct, how about this sample script? I think that there are several answers for your situation. Please think of this as one of them.

Sample script :

var res = values.filter(
   function(e) {
      return target_sheet_values.filter(
         function(f) {
            return e.toString() == f.toString()
         }).length == 0
   });

var target_sheet_values = [
   ["a","a","a","a","a"],
   ["z","x","c","v","b"],
   ["m","n","b","v","c"]
];
var values = [
   ["a","a","a","a","a"],
   ["z","x","c","v","b"],
   ["m","n","b","v","c"],
   ["a","s","d","f","g"],
   ["q","w","e","r","t"]
];

var res = values.filter(
   function(e) {
      return target_sheet_values.filter(
         function(f) {
            return e.toString() == f.toString()
         }).length == 0
   });
console.log(res);

Result

[["a","s","d","f","g"],["q","w","e","r","t"]]

Note :

values and target_sheet_values are from your script.
When you use this, for your script, please use this instead of var diff_values = diff(values, target_sheet_values).

If I misunderstand what you want, please tell me. I would like to modify it.

edited Mar 2, 2019 at 20:54

Owen Blacker

4,1733 gold badges36 silver badges72 bronze badges

answered Jun 24, 2018 at 0:02

Tanaike

203k12 gold badges126 silver badges222 bronze badges

1 Comment

Tanaike Over a year ago

@user61629 I'm glad your issue was solved. Thank you, too.

Collectives™ on Stack Overflow

Filtering a two dimensional array in apps script

2 Answers 2

4 Comments

Sample script :

Result

Note :

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Sample script :

Result

Note :

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related