# Best way to iterate through all input arguments?

I am writing a custom function in sheets that will not have a defined number or format of arguments. As an example, the call might be: =myFunc(a1:b3,a2:a4,b1:b9,c1,d2, [might be more, might be less])

I want to step through every value sent in and operate on it but run into all kinds of problems (mostly my own I am sure). I seem to run into problems distiguishing between a 2D and 1D set of inputs. Conceptually, I was thinking of something like this(not my actual code).

``````function myFunc(values) {
var i;
var calcVal = 0;

for (var j= 0; j < values.count; j++) {
if (values[j] > 1) {
calcVal = calcVal + values[j]*j;
}
}
return calcVal;
``````

Is there some straightforward way of walking through each value coming in one at a time?

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 `[,]` - 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".

• Awesome! Thanks. The flatten function was my missing piece. Now I have to figure out how it really works, step by step. – robu Aug 14 '15 at 17:10
• Disclosure: (a) the line with `reduce` is copied from Mozilla documentation on `reduce`. (b) The check for "is this an array" is copied from SO answer... – user79865 Aug 14 '15 at 17:13