Google Apps Script – Best Way to Iterate Through All Input Arguments

google sheetsgoogle-apps-script

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?

Best Answer

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

Related Topic