Union function - Passing argument in custom function - Google-spreadsheet

Question

While trying to answer this question, I found a strange behavior.

Here's my code :

function remove(val, array){
  var res = new Array();
  for(var i=0; i<array.length; i++){
    if(array[i] != val){
      res.push(array[i]);
    }
  }
  return res;
}

//we assume that there is no duplicates values inside array1 and array2
function my_union(array1, array2){
  var longuer;
  var shorter;
  var arrayRes = new Array();
  if(array1.length < array2.length){
    longuer = array2;
    shorter = array1;
  } else {
    longuer = array1;
    shorter = array2;
  }

  for(var i=0; i<longuer.length; i++){
    arrayRes.push(longuer[i]);
    shorter = remove(longuer[i], shorter);
  }

  for(var i=0; i<shorter.length; i++){
    arrayRes.push(shorter[i]);
  }

  return arrayRes;
}

function test(){
  Browser.msgBox(my_union([1,2,3], [1,2]));
}

The message box clearly says 1,2,3 but when you try to invoke this function inside a spreadsheet with the same values, it fails to delete duplicated values, why ?

**EDIT : ** Thanks to Henrique's answer , here's the code :

function remove(val, array){
  var res = new Array();
  for(var i=0; i<array.length; i++){
    if(array[i] != val){
      res.push(array[i]);
    }
  }
  return res;
}

function matFlattener(matrix){
  var array = new Array();
  for(var i=0; i<matrix.length; i++){
    for(var j=0; j<matrix[i].length; j++){
      array.push(matrix[i][j]);
    }
  }
  return array;
}

function my_union(matrix1, matrix2){
  //assert no duplicate values in matrix1 and matrix2
  var longuer;
  var shorter;
  var array1 = matFlattener(matrix1);
  var array2 = matFlattener(matrix2);
  var arrayRes = new Array();
  if(array1.length < array2.length){
    longuer = array2;
    shorter = array1;
  } else {
    longuer = array1;
    shorter = array2;
  }

  for(var i=0; i<longuer.length; i++){
    arrayRes.push([longuer[i]]);
    shorter = remove(longuer[i], shorter);
  }

  for(var i=0; i<shorter.length; i++){
    arrayRes.push([shorter[i]]);
  }

  return arrayRes;
}

Please consider changing the title of this question to better reflect what you are really asking about. — Eric Koleda, May 30 '12 at 17:21
Please twelve words just to say i've done what you've ask... — Zelwina, May 31 '12 at 11:19

score 1 · Accepted Answer · answered May 29 '12 at 14:18

When you call the custom function from the spreadsheet and pass a multi-range parameter, this parameter will always be a matrix, regardless if you pass a single row or column.

To test the behavior like the spreadsheet does, you should change your test function like this:

function test() {
  //note the "extra" brackets
  Browser.msgBox(my_union([[1,2,3]],[[1,2]]); //passing "single" rows
  Browser.msgBox(my_union([[1],[2],[3]],[[1],[2]]); //passing "single" columns
}

The solution is adjust your my_union formula to account for it.

Union function - Passing argument in custom function - Google-spreadsheet

1 Answers1

Linked