Skip to main content
replaced http://stackoverflow.com/ with https://stackoverflow.com/
Source Link

Whenever an argument is a range containing more than one cell, the custom function receives a 2D array. For example, suppose this is your spreadsheet.

+---+---+---+
|   | A | B |
+---+---+---+
| 1 | 5 | 7 |
| 2 | 6 | 8 |
+---+---+---+

Then:

  • myFunc(A1:B2) receives [[5,7],[6,8]]
  • myFunc(A1:B1) receives [[5,7]]
  • myFunc(A1:A2) receives [[5],[6]] - single-cell rows are still given as arrays.
  • myFunc(A1) receives 5 (a single cell is not given as an array).

So, the first order of business would be to standardize these so that everything is represented by a 1D array. Like this:

function flatten(arg) {
  if (arg.constructor === Array) {
    return arg.reduce(function(a, b) { return a.concat(b); });
  }
  else {
    return [arg];
  }
}

Then your function could use the special argumentsarguments object to handle the unknown number of arguments. In my example, allCells is a 1D array containing the values of all cells involved. For demonstration, I have it returned as a comma-separated string.

function myFunc() {
  var allCells = [];
  for (var i = 0; i < arguments.length; i++) {
    allCells = allCells.concat(flatten(arguments[i]));
  }
  return allCells.join();  // or whatever you want to do with data
}

For example, myFunc(A1:B2, A1:A2, B2) returns the string "5,7,6,8,5,6,8".

Whenever an argument is a range containing more than one cell, the custom function receives a 2D array. For example, suppose this is your spreadsheet.

+---+---+---+
|   | A | B |
+---+---+---+
| 1 | 5 | 7 |
| 2 | 6 | 8 |
+---+---+---+

Then:

  • myFunc(A1:B2) receives [[5,7],[6,8]]
  • myFunc(A1:B1) receives [[5,7]]
  • myFunc(A1:A2) receives [[5],[6]] - single-cell rows are still given as arrays.
  • myFunc(A1) receives 5 (a single cell is not given as an array).

So, the first order of business would be to standardize these so that everything is represented by a 1D array. Like this:

function flatten(arg) {
  if (arg.constructor === Array) {
    return arg.reduce(function(a, b) { return a.concat(b); });
  }
  else {
    return [arg];
  }
}

Then your function could use the special arguments object to handle the unknown number of arguments. In my example, allCells is a 1D array containing the values of all cells involved. For demonstration, I have it returned as a comma-separated string.

function myFunc() {
  var allCells = [];
  for (var i = 0; i < arguments.length; i++) {
    allCells = allCells.concat(flatten(arguments[i]));
  }
  return allCells.join();  // or whatever you want to do with data
}

For example, myFunc(A1:B2, A1:A2, B2) returns the string "5,7,6,8,5,6,8".

Whenever an argument is a range containing more than one cell, the custom function receives a 2D array. For example, suppose this is your spreadsheet.

+---+---+---+
|   | A | B |
+---+---+---+
| 1 | 5 | 7 |
| 2 | 6 | 8 |
+---+---+---+

Then:

  • myFunc(A1:B2) receives [[5,7],[6,8]]
  • myFunc(A1:B1) receives [[5,7]]
  • myFunc(A1:A2) receives [[5],[6]] - single-cell rows are still given as arrays.
  • myFunc(A1) receives 5 (a single cell is not given as an array).

So, the first order of business would be to standardize these so that everything is represented by a 1D array. Like this:

function flatten(arg) {
  if (arg.constructor === Array) {
    return arg.reduce(function(a, b) { return a.concat(b); });
  }
  else {
    return [arg];
  }
}

Then your function could use the special arguments object to handle the unknown number of arguments. In my example, allCells is a 1D array containing the values of all cells involved. For demonstration, I have it returned as a comma-separated string.

function myFunc() {
  var allCells = [];
  for (var i = 0; i < arguments.length; i++) {
    allCells = allCells.concat(flatten(arguments[i]));
  }
  return allCells.join();  // or whatever you want to do with data
}

For example, myFunc(A1:B2, A1:A2, B2) returns the string "5,7,6,8,5,6,8".

Source Link
user79865
user79865

Whenever an argument is a range containing more than one cell, the custom function receives a 2D array. For example, suppose this is your spreadsheet.

+---+---+---+
|   | A | B |
+---+---+---+
| 1 | 5 | 7 |
| 2 | 6 | 8 |
+---+---+---+

Then:

  • myFunc(A1:B2) receives [[5,7],[6,8]]
  • myFunc(A1:B1) receives [[5,7]]
  • myFunc(A1:A2) receives [[5],[6]] - single-cell rows are still given as arrays.
  • myFunc(A1) receives 5 (a single cell is not given as an array).

So, the first order of business would be to standardize these so that everything is represented by a 1D array. Like this:

function flatten(arg) {
  if (arg.constructor === Array) {
    return arg.reduce(function(a, b) { return a.concat(b); });
  }
  else {
    return [arg];
  }
}

Then your function could use the special arguments object to handle the unknown number of arguments. In my example, allCells is a 1D array containing the values of all cells involved. For demonstration, I have it returned as a comma-separated string.

function myFunc() {
  var allCells = [];
  for (var i = 0; i < arguments.length; i++) {
    allCells = allCells.concat(flatten(arguments[i]));
  }
  return allCells.join();  // or whatever you want to do with data
}

For example, myFunc(A1:B2, A1:A2, B2) returns the string "5,7,6,8,5,6,8".