2

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?

1

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".

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.